Dedicated to design and performance of databases and audio systems.
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
Statistics are collected as either
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.
-- 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.