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;