15.10 - Statistics from Dropped and Recreated Objects - Teradata Database

Teradata Database Application Programming Reference

Teradata Database
Programming Reference

A table is said to be Reincarnated after being temporarily dropped and subsequently recreated with the same column definitions, but potentially different data. Reincarnated tables are common to the ETL process where a "query" table is briefly dropped and then immediately recreated by renaming a "shadow" table that contains data from the original query table plus some recently loaded data. All reincarnated tables share the same underlying condition of having a newly assigned internal table id stored in their definition in system table DBC.TVM. Rather than leaving them orphaned and eventually removed, users have the option of repairing the TDStats stored definitions by marking them for preservation. For more information, see Open API “PreserveAfterRecreate” on page 511.

Example of Preserving TDStats Data During Table Recreation

A typical ETL flow involving a previously automated query table that is reincarnated by renaming a shadow table requires the following SQL statements and API calls.

Initial setup steps (performed once):

CREATE TABLE QueryTable (….); Load or INSERT data into QueryTable COLLECT STATISTICS …. ON QueryTable; /* user defined stats on columns and indexes */ CALL AutomateStats('Db1','QueryTable',…); CALL PreserveAfterRecreate('Db1','QueryTable',NULL,:NumStatsToPreserve);

Note: Step number 5 is required to ensure that TDStats metadata for QueryTable will survive the table recreation.

Table recreation steps (periodically repeated):

CREATE TABLE ShadowTable AS Db1.QueryTable WITH DATA AND STATS; Load additional data into ShadowTable using bulk load methods COLLECT SUMMARY STATISTICS ON ShadowTable; /* refresh summary-only stats */ DROP TABLE QueryTable; RENAME TABLE ShadowTable AS QueryTable; CALL ResyncStats('Db1','QueryTable',:ResyncId,:NumRepaired);

Note: Step number 6 is required to ensure that TDStats metadata for QueryTable will survive the table recreation.