Recommended Data Dictionary Statistics for 15.10

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

Collecting statistics on data dictionary tables is an option that can be useful if your site supports queries that access multi-table dictionary views. Third party tools often access the data dictionary several times for meta-data purposes, primarily using X-views. Without statistics, the optimizer may not be doing an optimal job in building query plans for such queries.

 

This is an update to an earlier posting in which data dictionary statistics collection recommendation were made for Teradata Database 14.0. This posting makes recommendations for dictionary statistics from the 15.10 perspective. You may not require all of these statistics, but they cover the tables and columns most likely to be accessed in the data dictionary views in 15.10.

 

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)
 , COLUMN (PARTITION)
ON DBC.AccessRights;

COLLECT STATISTICS
 COLUMN (ProfileName) 
 , COLUMN (ProxyUserID,GrantStatus) 
 , COLUMN (TrustUserID)
 , COLUMN (TrustUserID,ProxyUser) 
ON DBC.ConnectRulesTbl ;

COLLECT STATISTICS
 COLUMN DatabaseId
 , COLUMN DatabaseName
 , COLUMN DatabaseNameI
 , COLUMN OwnerName
 ,  COLUMN LastAlterUID
 , COLUMN JournalId
 , COLUMN ZoneID
 , 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 (ProfileId) 
 , COLUMN (ProfileName) 
 , COLUMN (ProfileNameI) 
ON DBC.Profiles ;

COLLECT STATISTICS 
COLUMN (TableId)
, COLUMN (FieldId)
, COLUMN (FieldName)
, COLUMN (FieldType)
, COLUMN (DatabaseId)
, COLUMN (CreateUID)
, COLUMN (LastAlterUID)
, COLUMN (UDTName)
, COLUMN (TableId, FieldName)
, COLUMN (TableId, FieldID)
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 (DatabaseID)
 , COLUMN (UsageType)
 , COLUMN (IndexNumber)
 , COLUMN (DatabaseId, ObjectId)
 , 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;

COLLECT STATISTICS
 COLUMN (ConstraintType) 
 , COLUMN (DBaseId) 
 , COLUMN (TVMId) 
ON DBC.TableConstraints ;

COLLECT STATISTICS
 COLUMN (BaseTableId)
ON DBC.TempTables ;