OTF statistics are stored in DBC.StatsTbl, with each entry represented by a single row. The table’s structure remains unchanged:
- ObjectId is the non-unique primary index.
- The combination of ObjectId and StatsId must be unique, though this is not enforced by constraints.
Here are the specific rules for field values in a DBC.StatsTbl row corresponding to an OTF stats entry:
- StatsType: The value of the StatsType field is 'O'.
- DatabaseId: The value of the databaseId field is 0x00.
- RefObjects: A string in the format "datalake"."database"."table"
- ObjectId: A 6-byte VARBYTE; first 4 bytes are a hash of RefObjects, last 2 bytes are hex 992 (indicating OTF data rows)
- ParentId: A 6-byte VARBYTE representing the datalake’s ServerId (must match a value in DBC.ServerInfo.ServerID)
- FieldIdList: A colon-separated string of field IDs; OTF field IDs can start from 1 (unlike BFS/OFS which start from 1025)
- StatsId: A unique integer per ObjectId, starting from 1 (no 0, which is reserved for table-level. Note that there is no StatsId of 0, which represents the table-level stats entry.
Following is an example of an OTF stats entry in DBC.StatsTbl:
A COLLECT STATS is first executed on column a1 of table T1 in Teradata database MyDB. Then, the same command is run on column a1 of table T1 in the MyDB database within datalake Lake1.
- The first two rows in DBC.StatsTbl represent Teradata BFS stats:
- Row 1: Table-level stats for MyDB.T1
- Row 2: Column-level stats for MyDB.T1.a1
- OTF tables do not support table-level stats, so there is no row for Lake1.MyDB.T1 at the table level.
- The third row is the OTF column-level stats for Lake1.MyDB.T1.a1, with:
- StatsType: 'O'
- StatsId: 1
- ObjectId: 4C01BB02E003
- ParentId: 00002C110000
- RefObjects: "Lake1"."MyDB"."T1"
Many existing views reference DBC.StatsTbl, assuming that:
- StatsTbl.DatabaseId maps to DBASE.DatabaseId
- StatsTbl.ObjectId maps to TVM.TvmId
However, for OTF stats entries, these mappings do not apply. The values inStatsTbl.DatabaseId and StatsTbl.ObjectId are unrelated to DBASE and TVM, so OTF stats are excluded from view outputs that join these tables using those fields.
To support OTF statistics, Teradata introduces two new views: DBC.OtfStatsV and DBC.AllStatsV. These views reference the same DBC.StatsTblentries shown in the earlier example.
- DBC.OtfStatsV: Displays only OTF stats entries from DBC.StatsTbl. It includes a CatalogName column (representing the datalake name), along with DatabaseName and TableName, all derived from RefObjects—not from joins with DBC.DBASE or DBC.TVM.
- DBC.AllStatsV: Displays all stats entries, including OTF. Like OtfStatsV, it includes CatalogName. For regular (non-OTF) entries, CatalogName is set to TD_Local.