Dictionary Statistics for 16.10 - Recommendations

Blog
The best minds from Teradata, our partners, and customers blog about relevant topics and features.
Teradata Employee

 

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

 

Collecting statistics on data dictionary tables is an option you have 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.

 

New statistics included in this list come from these tables:

  • DBC.Maps
  • DBC.MapGrants
  • DBC.DatabaseSpace
  • DBC.GlobalDBSpace

 

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 (ProxyUser)
 , COLUMN (TrustUserID)
 , COLUMN (TrustUserID,ProxyUser) 
 , COLUMN (ProxyUserID,GrantStatus) 
 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 ;

COLLECT STATISTICS
 COLUMN (DatabaseId)
 , COLUMN (SpoolOnly)
 , INDEX (DatabaseId, TableId)
ON DBC.DatabaseSpace;

COLLECT STATISTICS
 COLUMN (DatabaseId)
 , COLUMN (SpoolOnly)
 , COLUMN (SoftLimitPercent)
ON DBC.GlobalDBSpace;

COLLECT STATISTICS
 COLUMN (MapNameI)
 , COLUMN (MapNo)
 , COLUMN (ParentSystemMapNo)
 , COLUMN (CreatorId)
ON DBC.Maps;

COLLECT STATISTICS
 COLUMN (GranteeId)
 , COLUMN (GrantorId)
 , COLUMN (MapNo)
ON DBC.Mapgrants;