COLLECT STATS, DROP STATS, HELP STATS, SHOW STATS | Teradata OTF - Supported SQL (COLLECT STATS, DROP STATS, HELP STATS, SHOW STATS) - Teradata Vantage

Teradata® Open Table Format for Apache Iceberg and Delta Lake User Guide
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Lake
Product
Teradata Vantage
Release Number
20.00
Published
October 2025
ft:locale
en-US
ft:lastEdition
2025-10-25
dita:mapPath
qrj1749167830193.ditamap
dita:ditavalPath
lli1749584660955.ditaval
dita:id
bsr1702324250454

OTF statistics can be stored in DBC.StatsTbl and managed using standard Teradata operations such as COLLECT STATS, DROP STATS, HELP STATS, and SHOW STATS.

The following table outlines supported functionalities using the standard syntax, where <<table_name>> refers to an OTF table in three-level dot notation or a Datalake Table Alias.

Category Functionality Syntax
COLLECT STATS COLLECT STATS with one single-column stats entry. A COLLECT STATS statement has one COLUMN keyword whose parenthesis encloses one column name. COLLECT STATISTICS COLUMN(<<column_name>>) ON <<table_name>>;

Example: COLLECT STATISTICS COLUMN(make) ON tdlake.products.cars;
COLLECT STATS with one multi-column stats entry: A COLLECT STATS statement has one COLUMN keyword whoes parenthesis encloses multiple column names separated by a comma (','). COLLECT STATISTICS COLUMN(<<column_name>>,<<column_name>>) ON <<table_name>>;

Example: COLLECT STATISTICS COLUMN (price, weight) ON tdlake.products.cars;
COLLECT STATS with two stats entry: A COLLECT STATS statement has two COLUMN keywords, each of whose parenthesis encloses one column name. COLLECT STATISTICS COLUMN(<<column_name>>), COLUMN(<<column_name>>) ON <<table_name>>;

Example: COLLECT STATISTICS COLUMN(make), COLUMN(price) ON tdlake.products.cars;
COLLECT STATS VALUES of one stats entry of a relation: A COLLECT STATS statement has the VALUES clause, so that statistics for a stats entry are collected from the given values in the parenthesis of the VALUES keyword, instead of from the data rows by a physical scan. Those values are seprated by commas. A value in each position has its own meaning.
The set of values in the VALUES clause are not data values but statistics which correspond to the internal data structure of DBC.StatsTbl.Histogram.
 COLLECT STATISTICS COLUMN(<<column_name>>) ON <<table_name>> VALUES (<<set_of_values>>);

Example:

COLLECT STATISTICS COLUMN ( city ) ON datalake_iceberg_glue .vim.t1_part VALUES

(TIMESTAMP '2024-12-31 21:31:18-00:00', 6, 6,

'20.00.24.10MYOTFSTATS2_SKD1', 'D', 'ComplexStatInfo',

5,0,0,0.00,3,3,0,0,0.000000,'chennai','San Diego','portland',

6,6,24,0.000000,0.000000,0,0,0.000000,0.000000,

'T0000D00000S00000',0,0,0,0,

TIMESTAMP '9999-12-31 23:59:59-00:00', TIMESTAMP '9999-12-31 23:59:59-00:00',

0,'chennai', 3,'Delhi', 4,'Paris', 4,'portland', 6,'San Diego', 4);
DROP STATS DROP STATS of one stats entry of a relation: A DROP STATS statement removes a given stats entry of a table. DROP STATISTICS COLUMN(<<column_name>>) ON <<table_name>>;

DROP STATISTICS COLUMN(<<column_name>>,<<column_name>>) ON <<table_name>>;

DROP STATISTICS COLUMN(<<column_name>>),COLUMN(<<column_name>>) ON <<table_name>>;

Example:

  • DROP STATISTICS COLUMN (temperature) ON tdlake.nations.korea;
  • DROP STATISTICS COLUMN (temperature, humidity) ON tdlake.nations.korea;
  • DROP STATISTICS COLUMN (temperature), COLUMN(humidity) ON tdlake.nations.korea;
DROP STATS of all entry from a table. A DROP STATS statement removes all stats entries of a table. DROP STATISTICS ON <<table_name>>;

Example: DROP STATISTICS ON tdlake.nations.korea;
HELP STATS HELP STATS on a relation. A HELP STATS statement displays the number of unique values for each stats entry. HELP STATISTICS ON <<table_name>>;

Example: HELP STATISTICS ON tdlake.nations.korea;
SHOW STATS SHOW STATS of one stats entry of a table. A SHOW STATS statement displays a COLLECT STATS statement that can be executed to collect statistics for the corresponding stats entry. SHOW STATISTICS COLUMN(<<column_name>>) ON <<table_name>>;

SHOW STATISTICS COLUMN(<<column_name>>,<<column_name>>) ON <<table_name>>;

Example:

  • SHOW STATISTICS COLUMN (temperature) ON tdlake.nations.korea;
  • SHOW STATISTICS COLUMN (temperature, humidity) ON tdlake.nations.korea;
SHOW STATS of all stats entries of a table. A SHOW STATS statement displays a COLLECT STATS statement that can be executed to collect statistics for all the stats entries of the table. SHOW STATISTICS ON <<table_name>>;

Example: SHOW STATISTICS ON tdlake.nations.korea;

SHOW STATS VALUES of one stats entry of a relation. A SHOW STATS statement displays a COLLECT STATS statement, for the stats entry, with the VALUES clause. SHOW STATISTICS VALUES COLLUMN(<<column_name>>) ON <<table_name>>;

Example: SHOW STATISTICS VALUES COLUMN (temperature) ON tdlake.nations.korea;
SHOW STATS VALUES of all stats entries of a table. A SHOW STATS statement displays multiple COLLECT STATS statements for all the statistics entries of a table, respectively. Each statement has the VALUES clause. SHOW STATISTICS VALUES ON <<table_name>>;

Example: SHOW STATISTICS VALUES ON tdlake.nations.korea;

COPY

STATS

 COLLECT STATS on <<OTF table>> FROM <<TD table>>.
  • COLLECT STATS OtfTbl FROM TdTbl;
  • COLLECT STATS OtfTbl (target column) FROM TdTbl
  • COLLECT STATS OtfTbl (target column) FROM TdTbl(source column)
  • TdTbl represents BFS or OFS tables.
  • OtfTbl represents an OTF table which is represented as the three-level dot notation.
  • Data types of all targeted columns between TdTbl and OtfTbl must match.
  • Due to the difference of string representation between OtfTbl and TdTbl, COPY STATS does not work when there is a target column whose data type is string (or the source table whose data type is CHAR/VARCHAR).
Example
COLLECT Stats bn_otf_storage.bn_otf_db.TargetOTFTbl from SrcTDTbl1;
/* TD Column is defined as VARCHAR(25) but OTF Column is defined as VARCHAR(4000) */
 *** Failure 9216 Source and target table definitions did not match.
                Statement# 1, Info =0

COLLECT Stats column(OrderDate), column(StoreNo) ON bn_otf_storage.bn_otf_db.TargetOTFTbl FROM SrcTDTbl;
 *** Insert completed. 2 rows added.

COLLECT  STATS column(OrderDate), column(StoreNo) ON bn_otf_storage.bn_otf_db.TargetOTFTbl
 FROM SrcTDTbl COLUMN(StoreNo), column(OrderDate);
 *** Failure 9251 Source table field list did not match with Target table field list.

COLLECT  Stats column(StoreNo), column(OrderDate) ON bn_otf_storage.bn_otf_db.TargetOTFTbl
 FROM SrcTDTbl COLUMN(StoreNo), COLUMN(OrderDate);
 *** Insert completed. 2 rows added.
COLLECT STATS on <<TD table>> FROM <<OTF table>> (not supported)

→ syntax error

 Example:
COLLECT Stats column(OrderDate), column(StoreNo) ON TargetTDTbl 
FROM bn_otf_storage.bn_otf_db.SrcOTFTbl COLUMN(StoreNo), column(OrderDate);
                               $
 *** Failure 3706 Syntax error: expected something between the word 'bn_otf_db' and '.'.
                Statement# 1, Info =97

COLLECT STATS on <<OTF table>> FROM <<OTF table>> (not supported)

→ syntax error

Example:

COLLECT Stats column(OrderDate), column(StoreNo) ON bn_otf_storage.bn_otf_db.TargetOTFTbl 
FROM bn_otf_storage.bn_otf_db.SrcOTFTbl;
                               $
 *** Failure 3706 Syntax error: expected something between the word 'bn_otf_db' and '.'.
                Statement# 1, Info =123
CT AS WITH STATS (it is NOT supported): Statistics of a target table to be created can be copied from the stats of a source table when WITH STATS is specified.
  • CT TargetBfsTbl as SrcOtfTbl with DATA and STATS;

    CT TargetBfsTbl as SrcOtfTbl with NO DATA and STATS;

    → A warning 6972 message is raised, that says, stats cannot be copied.

  • CT TargetOtfTbl as SrcBfsTbl with Data and Stats;

    CT TargetOtfTbl as SrcBfsTbl with Data and Stats;

    → A Syntax error is raised.

  • CT TargetOtfTbl as SrcOtfTbl With Data and Stats;

    CT TargetOtfTbl as SrcOtfTbl With Data and Stats;

    → Syntax error is raised.

 CREATE TABLE <<…>> AS <<source table>> with STATS; or

CREATE TABLE <<…>> AS (SELECT * FROM <<source table>>) with STATS;

Example #1:

CT TargetTDTbl AS bn_otf_storage.bn_otf_db.SrcOTFTbl with data and stats;
 *** Table has been created.
 *** Warning: 6962 Statistics cannot be copied.

CT TargetTDTbl AS bn_otf_storage.bn_otf_db.SrcOTFTbl with no data and stats;
 *** Table has been created.
 *** Warning: 6962 Statistics cannot be copied.

CT TargetTDTbl AS
(select orderdate, storename, ordertotal, storeno from bn_otf_storage.bn_otf_db.SrcOTFTbl1)
with data and stats;
 *** Table has been created.
 *** Warning: 6962 Statistics cannot be copied.

 CT TargetTDTbl AS
(select orderdate, storename, ordertotal, storeno from bn_otf_storage.bn_otf_db.SrcOTFTbl1)
with no data and stats;
 *** Table has been created.
 *** Warning: 6962 Statistics cannot be copied.

Example #2:

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as SrcTDTbl with data;
 *** Table has been created.

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as SrcTDTbl with no data;
 *** Table has been created.

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as SrcTDTbl with data and stats;
                                                                             $
 *** Failure 3706 Syntax error: Copying STATS is not supported for Open Format table.
                Statement# 1, Info =78

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as SrcTDTbl with no data and stats;
                                                                                $
 *** Failure 3706 Syntax error: Copying STATS is not supported for Open Format table.
                Statement# 1, Info =81

Example #3:

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as bn_otf_storage.bn_otf_db.SrcOTFTbl with data;
 *** Table has been created.

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as bn_otf_storage.bn_otf_db.SrcOTFTbl with no data;
 *** Table has been created.

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as bn_otf_storage.bn_otf_db.SrcOTFTbl with data and stats;
                                                                                                       $
 *** Failure 3706 Syntax error: Copying STATS is not supported for Open Format table.
                Statement# 1, Info =104

create table bn_otf_storage.bn_otf_db.TargetOTFTbl as bn_otf_storage.bn_otf_db.SrcOTFTbl with no data and stats;
                                                                                                          $
 *** Failure 3706 Syntax error: Copying STATS is not supported for Open Format table.
                Statement# 1, Info =107
Stats Recommend Statistics recommendation kicks in for an Explain query when the diagnostic of HELPSTATS is turned on.

Recommended statistics collection can be found in the Explain output text in the form of complete COLLECT STATISTICS statement which is executable as it is.

Diagnostic HELPSTATS on for session;

Explain <<query>>;

Example: please find the recommendation at the end of Explain output

Diagnostic HELPSTATS on for session;
Explain select  *  from  lake1.db1.tbl1  where Col3 = 4;

Explanation
--------------------------------------------------------------------------------
  1) First, we lock manifest list of bn_otf_storage
     .bn_otf_db.otf_stats_rec for read.
  2) Next, we do a single-AMP RETRIEVE step from manifest list of
     ...
  3) We do an all-AMPs RETRIEVE step in TD_MAP1 from data manifest file
     ...
  4) We do a single-AMP RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 4 (all_amps), which is binpacked and
     ...
  5) We do an all-AMPs RETRIEVE step in TD_MAP1 from 3 column
     partitions of bn_otf_storage .bn_otf_db.otf_stats_rec
     by way of external metadata in Spool 4 (Last Use) with a condition
     of ("lake1 .db1.tbl1.Col3 = 4") into Spool
     1 (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with no confidence to be 1 row (2,043 bytes).
     The estimated time for this step is 0.51 seconds.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1, are sent back to the user as the result
     of statement 1.  The total estimated time is 0.78 seconds.
     BEGIN RECOMMENDED STATS FOR FINAL PLAN->
     -- "COLLECT STATISTICS COLUMN (Col3) ON lake1.db1.tbl1" (High Confidence)
     <- END RECOMMENDED STATS FOR FINAL PLAN