Teradata Collect Stats

1.     From Teradata 14, it is better to combine multiple collect stats on same table so that table scans occur just once.

Example-

Collect stats Column col1 on table1;

Collect stats Column col2 on table1;

Collect stats Column (col1,col3) on table1;

Instead of above one, replace it with

Collect stats

Column col1

,Column col2

,Column (col1,col3)

On table1;

2.       When a table has billions of rows and we don’t have much time window left then we can go for collecting sample statistics whose syntax given below

“Collect Statistics Using <sampling level > <column name> on <table name>”

3.       Instead of recollecting full stats on table, summary stats can be opted utilizing less resources and time consumed.

“Collect Summary Stats on <table name>”

4.       Use Help Statistics on table to see the summary of stats values collected and Show Statistics for detailed description on column stats.

show stats on edw_db.notebase column(NoteContactAuthorEpicUserID )

help Stats on edw_db.notebase

Thanks to Gokul from my team for this useful piece of information.