Significance of COLLECT STATS and Easy way to do it

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Significance of COLLECT STATS and Easy way to do it

Hello There,

We are doing a collect stats every time the daily data load is complete.

We run the following command in the ETL process as an after sql for each table load, it looks very primitive and the ETL has been around for 8 year now (guessing)

 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

INSERT INTO tableA SELECT * FROM tableB ..

 

COLLECT STATS

INDEX(COL_ID_1,COL_ID_3,COL_ID_1,COL_ID_5,COL_ID_6,COL_ID_7)

COLUMN(COL_ID_1),

COLUMN(COL_ID_3),

COLUMN(COL_ID_5),

COLUMN(COL_ID_6),

COLUMN(COL_ID_7)

ON tableB

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Wondering if there is any efficient way i can automate the process w/o having to write these bunch of lines.

 

Like a collect stats procedure that will just do the stats by just passing the table name

 

For example.. some_(default)tera_collectstats_procedure.tableB  = Done !!

 

One line and peace of mind....and input will he helpful

 

Thanks,

 

 

 

 

 

 

2 REPLIES 2
Teradata Employee

Re: Significance of COLLECT STATS and Easy way to do it

As long as the table is persistent you can simply use collect statistics on tableb;

Teradata Employee

Re: Significance of COLLECT STATS and Easy way to do it

You can consider implementing Stats Manager (controlled via VP) in order to automate the process of collecting and refreshing stats.

We are currently using it successfully at our customer site.