b¬äd†ødd

Dedicated to design and performance of databases and audio systems.

Got Stats?

Recently I was asked if there was a way to query the DBC tables and determine tables that lacked statistics. The simple answer is yes with a small exception--the pseudo-column PARTITION.

Statistics are collected as either COLUMN or INDEX. To illustrate

COLLECT STATISTICS ON the_db.table_1 COLUMN (field_2);
COLLECT STATISTICS ON the_db.table_1 INDEX  (field_2);

You can only collect the latter on a Primary or Secondary Index, but that is another topic. However, the point is that Teradata stores the statistics in different DBC tables depending on the type of statistic collected--either DBC.columnstats or DBC.indexstats, respectively. One restriction here is that you cannot actually read the statistics collected (i.e. the internal histogram) until Teradata 14, but you will know if the field contains a statistic or not. All fields except the pseudo-column PARTITION because it is not actually a field on the table, rather it is part of the row header, or logically inferred in the case of a NPPI table.

The query:

-- note: the query does not recognize the pseudo-column PARTITION

-- INDEX stats query
select databasename, tablename
from dbc.indexstats
where databasename = 'the_db' -- replace with database to be queried
group by 1,2
having sum(case when indexstatistics is null then 1 else 0 end) = count(*)

intersect -- neither Index Stats nor Column Stats

-- COLUMN stats query
select databasename, tablename
from dbc.columnstats
where databasename = 'the_db' -- replace with database to be queried
group by 1,2
having sum(case when fieldstatistics is null then 1 else 0 end) = count(*)

order by 1,2
;

I hope this helps with your future statistics research.

Cheers!
-Brad