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.
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) || ');'
where trim(databasename) = 'db1'
and trim(tablename)= 'test1'
and Indextype = 'P'
I use this query:
Check also dieters blog post
the code can be downloaded in the attachement section.
The statsinfo view contains also collect stats statements