sql - Using COLLECT STATISTICS in Teradata -

in teradata can use statement ...

collect statistics on my_table column(col1) 

this gather stats on table , store them in dbc views columnstats, indexstats , multicolumnstats. i'm under impression optimizer (parsing engine) find statistics when available , use them instead of estimated table cardinality/index value counts make better decisions on how execute query.

this sounds great, have questions.

• are there disadvantages using collect stats?
• when appropriate/inappropriate use collect statistics in sql scripting?
• what's performance benefit collect statistics on field that's indexed?
• how long statistics stored (table, volatile tables)?
• any other comments concerning collect statistics appreciated.

1>are there disadvantages using collect stats?

yes, collect stats time consuming, locate data amps , insert stats in dictionary tables.

suppose have table definition like:

ct t1(x1 int,y1 int, z1 int);

the table contains millions of rows , z1 never used in st/join conditions, not worth collect stats on z1.

2>when appropriate/inappropriate use collect statistics in sql scripting?

already answered above. if column going used st/join condition .i.e in or on clause, must collect stats, otherwise not needed.

3>what's performance benefit collect statistics on field that's indexed?

ct t1(x1 int,y1 int) primary index(x1);

for simple query sel * t1 x1 = 5;

will demonstrate usefulness of collect stats.

how?

the optimizer can correctly estimate how many rows query select , if t1 going joined t2, efficient join chosen optimizer.

4>how long statistics stored (table, volatile tables)?

table : permanently.

volatile tables: till session expires.

5>any other comments concerning collect statistics appreciated.

nothing has been discussed multicolumn stats.

say, query like:

sel * t1 join t2 on y1=y2 , x1=2;

then collecting multi-column stats on (x1,y1) quite helpful in optimization.

also, if table demography has been changed (increased number of rows) must consider re-collecting stats