Query to generate collect stats statement automatically

Database
Junior Supporter

Query to generate collect stats statement automatically

Hi,

say i ahve a tablename and databasename. I have to collect stats on the primay and secondaty indexes of that table(single or multi column).

Is there a query with someone which can help me create that the collect stats statement. The number of tables runs in hundreds, therefore need to automate it, manually its a diffucult task.

Need t it urgently, any help would be appreciated.

3 REPLIES
mjj
Teradata Employee

Re: Query to generate collect stats statement automatically

Hi,

Below statement will generate the collect stats statements for a given table in given database. You can create a macro for this and run it for all tables in your DB and save the collect stats statements in file which can be executed using bteq.

select distinct 'collect stats on '|| trim(databasename) || '.' || trim(tablename) ||' on index( ' || trim(columnname) || ');'

from dbc.indices

where trim(databasename) = 'db1'

and trim(tablename)= 'test1'

and Indextype = 'P'

Junior Supporter

Re: Query to generate collect stats statement automatically

Senior Supporter

Re: Query to generate collect stats statement automatically

Check also dieters blog post

http://developer.teradata.com/blog/dnoeth/2011/12/how-to-decode-the-binary-statistics-stored-in-dbc-...

the code can be downloaded in the attachement section.

The statsinfo view contains also collect stats statements