|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/26/2008 9:43:02 AM
Posts: 7,
Visits: 28
|
|
Hi
On 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 columns
of each index in dbc.Indices.
Instructions:
1. Replace @YOURDATABASENAME w/ the correct DatabaseName
2. Modify the "where" clause as necessary to target specific tables
3. Execute this SQL and capture the resulting "Collect Stats" scripts
4. Execute the resulting Collect Stats script as necessary
Requirements:
- Select access to DBC database
----------------------------------------------------------------------------*/
select (case Min_Pos
when 1 then 'Collect Statistics on ' ||
trim(upper(DatabaseName)) || '.' ||
trim(upper(TableName)) || ' INDEX ('
else ' ,' end) ||
trim(upper(ColumnName)) ||
(case Max_Pos
when 1 then ');'
else '' end)
from
(
select
DatabaseName,
TableName,
ColumnName,
IndexNumber,
ColumnPosition,
rank() over (partition by DatabaseName, TableName, IndexNumber order by ColumnPosition)
Min_Pos,
rank() over (partition by DatabaseName, TableName, IndexNumber order by ColumnPosition
desc) Max_Pos
from dbc.Indices
where DatabaseName in
('@YOURDATABASENAME')
and TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')
) xxx
order by
databasename, tablename, indexnumber, columnposition;
/*----------------------------------------------------------------------------
Description:
This SQL will generate a Collect Statistics script for individual columns
of index fields.
Instructions:
1. Replace @YOURDATABASENAME w/ the correct DatabaseName
2. Modify the "where" clause as necessary to target specific tables
3. Execute this SQL and capture the resulting "Collect Stats" scripts
4. Execute the resulting Collect Stats script as necessary
Requirements:
- Select access to DBC database
----------------------------------------------------------------------------*/
select
'Collect Statistics on ' ||
trim(upper(a.TableName)) ||
' Column ' ||
trim(upper(ColumnName)) ||
';' ans
from
dbc.indices a
where
12.3 Periodic Maintenance Tasks
12-63
a.DatabaseName = '@YOURDATABASENAME' and
-- Modify the TableName where clause to target specific tables
a.TableName like any ('IOS%','WCM%') and
a.TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')
order by
TableName, ColumnPosition;**/
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|