Teradata Inline Stats - How to over-ride the optimiser

Inline Stats are statistics collected in between a script, usually before a SELECT or a JOIN.

It is mostly used to over-ride the optimiser to pick a different PLAN.

For example:

We have a table FACT_SALES with PI on CUS_ID.

But, the Sales_Dimension has to be joined on PROD_ID, a primary key in the Dimension. There is no point joining based on CUS_ID, as it will lead to a spool-join rather than amp-local join.

So, we collect an Inline Stats by using:

Collects stats on Sales_Dim column PROD_ID;

This will help the optimizer to join the 2 tables based on PROD_ID.

What happens to statistics if a table is renamed in Teradata?

What happens to statistics if a table is renamed in Teradata? Should we recollect the statistics or should the statistics exist already?

The statistics in the table are retained because Teradata internally stores the Table with TableID.

So, statistics are retained because there has been no demographic changes.

Let us consider the example below:

collect statistics on ta_62917_qrmdb.dummy1 column version_no;

à 1 row processed

help stats ta_62917_qrmdb.dummy1;

Date	Time	Unique Values	Column Names
12/10/2025	10:53:08	3	VERSION_NO

rename table ta_62917_qrmdb.dummy1 to ta_62917_qrmdb.dummy2;

à 2 rows processed

help stats ta_62917_qrmdb.dummy2;

Date	Time	Unique Values	Column Names
12/10/2025	10:53:08	3	VERSION_NO

Teradata internally stores only the Table-ID.

Why should we Collect Statistics on Partition

Why should we Collect Statistics on Partition?

 These are a few details on the importance of collecting stats on PARTITION keyword. Please make sure to add this for all your target tables.

1. This is more quick method for getting information like select count(*) statements.
2. This does not take time to collect stats and goes into cylinder headers and collects data.

For accurate information, collect statistics every time any data modification happens to that table.

COLLECT STATISTICS TA_EDW_SCDB.TXN_MASTER_RETAIL COLUMN PARTITION;

*** Update completed. One row changed.

*** Total elapsed time was 44 seconds.

HELP STATS TA_EDW_SCDB.TXN_MASTER_RETAIL COLUMN PARTITION;

Date	Time	Number of Rows	Number of Nulls	Number of All Nulls	Number of AMPs	Average AMP RPV	OneAMPSampleEst	AllAMPSampleEst
11/7/2019	5:10:19	270,155,352	0	0	25	0	270320650	270127450
11/7/2019	5:10:19	270,155,352	0	0	25	0	270320650	270127450