All Forums Marketing & CRM
Ince 31 posts Joined 05/08
09 May 2008
Collect Statistic on IOS tables

HiOn the WHERE CLAUSE, do I mention all the tables that I need to collect statistics on?Refer to the script provided on the TCRM maintenance guideline below/**/*-------------------------------------------- --------------------------------Description:This SQL will generate a Collect Statistics script for all of the columnsof each index in dbc.Indices.Instructions:1. Replace @YOURDATABASENAME w/ the correct DatabaseName2. Modify the "where" clause as necessary to target specific tables3. Execute this SQL and capture the resulting "Collect Stats" scripts4. Execute the resulting Collect Stats script as necessaryRequirements:- Select access to DBC database---------------------------------------------- ------------------------------*/select (case Min_Poswhen 1 then 'Collect Statistics on ' ||trim(upper(DatabaseName)) || '.' ||trim(upper(TableName)) || ' INDEX ('else ' ,' end) ||trim(upper(ColumnName)) ||(case Max_Poswhen 1 then ');'else '' end) from(selectDatabaseName,TableName,ColumnName,IndexNumb er,ColumnPosition,rank() over (partition by DatabaseName, TableName, IndexNumber order by ColumnPosition)Min_Pos,rank() over (partition by DatabaseName, TableName, IndexNumber order by ColumnPositiondesc) Max_Posfrom dbc.Indiceswhere DatabaseName in('@YOURDATABASENAME')and TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')) xxxorder bydatabasename, tablename, indexnumber, columnposition;/*------------------------------------- ---------------------------------------Description: This SQL will generate a Collect Statistics script for individual columnsof index fields.Instructions:1. Replace @YOURDATABASENAME w/ the correct DatabaseName2. Modify the "where" clause as necessary to target specific tables3. Execute this SQL and capture the resulting "Collect Stats" scripts4. Execute the resulting Collect Stats script as necessaryRequirements:- Select access to DBC database---------------------------------------------- ------------------------------*/select'Collect Statistics on ' ||trim(upper(a.TableName)) ||' Column ' ||trim(upper(ColumnName)) ||';' ansfromdbc.indices awhere12.3 Periodic Maintenance Tasks12-63a.DatabaseName = '@YOURDATABASENAME' and-- Modify the TableName where clause to target specific tablesa.TableName like any ('IOS%','WCM%') anda.TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')order byTableName, ColumnPosition;**/

You must sign in to leave a comment.