Blog

The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

06-11-2013
07:03 AM

- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

06-11-2013
07:03 AM

Collecting statistics on data dictionary tables is an excellent way to tune long-running queries that access multi-table dictionary views. Third party tools often access the data dictionary several times, primarily using the X views. SAS, for example, accesses DBC views including IndicesX and TablesX for metadata discovery. Without statistics, the optimizer may do a poor job in building plans for these complex views, some of which are composed of over 200 lines of code.

In an earlier blog posting I discussed the value of collecting statistics against data dictionary tables, and provided some suggestions about how you can use DBQL to determine which tables and which columns to include. Go back and review that posting. This posting is a more comprehensive list of DBC statistics that is updated to include those recommended with JDBC.

Note that the syntax I am using is the new create-index-like syntax available in Teradata 14.0. If you are on a release prior to 14.0 you will need to rewrite the following statements so they are in the traditional collect statistics SQL.

Here are the recommendations for DBC statistics collection. Please add a comment if I have overlooked any other useful ones.

COLLECT STATISTICS

COLUMN TvmId

, COLUMN UserId

, COLUMN DatabaseId

, COLUMN FieldId

, COLUMN AccessRight

, COLUMN GrantorID

, COLUMN CreateUID

, COLUMN (UserId ,DatabaseId)

, COLUMN (TVMId ,DatabaseId)

, COLUMN (TVMId ,UserId)

, COLUMN (DatabaseId,AccessRight)

, COLUMN (TVMId,AccessRight)

, COLUMN (FieldId,AccessRight)

, COLUMN (AccessRight,CreateUID)

, COLUMN (AccessRight,GrantorID)

, COLUMN (TVMId ,DatabaseId,UserId)

ON DBC.AccessRights;

COLLECT STATISTICS

COLUMN DatabaseId

, COLUMN DatabaseName

, COLUMN DatabaseNameI

, COLUMN OwnerName

, COLUMN LastAlterUID

, COLUMN JournalId

, COLUMN (DatabaseName,LastAlterUID)

ON DBC.Dbase;

COLLECT STATISTICS

COLUMN LogicalHostId

, INDEX ( HostName )

ON DBC.Hosts;

COLLECT STATISTICS

COLUMN OWNERID

, COLUMN OWNEEID

, COLUMN (OWNEEID ,OWNERID)

ON DBC.Owners;

COLLECT STATISTICS

COLUMN ROLEID

, COLUMN ROLENAMEI

ON DBC.Roles;

COLLECT STATISTICS

INDEX (GranteeId)

ON DBC.RoleGrants;

COLLECT STATISTICS

COLUMN (TableId)

, COLUMN (FieldId)

, COLUMN (FieldName)

, COLUMN (FieldType)

, COLUMN (DatabaseId)

, COLUMN (CreateUID)

, COLUMN (LastAlterUID)

, COLUMN (UDTName)

, COLUMN (TableId, FieldName)

ON DBC.TVFields;

COLLECT STATISTICS

COLUMN TVMID

, COLUMN TVMNAME

, COLUMN TVMNameI

, COLUMN DATABASEID

, COLUMN TABLEKIND

, COLUMN CREATEUID

, COLUMN CreatorName

, COLUMN LASTALTERUID

, COLUMN CommitOpt

, COLUMN (DatabaseId, TVMName)

, COLUMN (DATABASEID ,TVMNAMEI)

ON DBC.TVM;

COLLECT STATISTICS

INDEX (TableId)

, COLUMN (FieldId)

, COLUMN (IndexNumber)

, COLUMN (IndexType)

, COLUMN (UniqueFlag)

, COLUMN (CreateUID)

, COLUMN (LastAlterUID)

, COLUMN (TableId, DatabaseId)

, COLUMN (TableId, FieldId)

, COLUMN (UniqueFlag, FieldId)

, COLUMN (UniqueFlag, CreateUID)

, COLUMN (UniqueFlag, LastAlterUID)

, COLUMN (TableId, IndexNumber, DatabaseId)

ON DBC.Indexes;

COLLECT STATISTICS

COLUMN (IndexNumber)

, COLUMN (StatsType)

ON DBC.StatsTbl;

COLLECT STATISTICS

COLUMN (ObjectId)

, COLUMN (FieldId)

, COLUMN (IndexNumber)

, COLUMN (DatabaseId, ObjectId, IndexNumber)

ON DBC.ObjectUsage;

COLLECT STATISTICS

INDEX (FunctionID )

, COLUMN DatabaseId

, COLUMN ( DatabaseId ,FunctionName )

ON DBC.UDFInfo;

COLLECT STATISTICS

COLUMN (TypeName)

, COLUMN (TypeKind)

ON DBC.UDTInfo;

32 Comments

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.