|
|
|
 |
 |
Archives of the TeradataForumMessage Posted: Fri, 04 Dec 2003 @ 22:23:51 GMT
Dear All, The FORUM is still quiet active, I came to check it after a pretty long time. I got the things working!!! :-) . Thanks to you all esp. Rudel, Vishal, Vivek and all others. I am posting the macro just for any future reference. It returns the following three answer sets: 1. Single column and single column indexes. 2. Multi column stats 3. Multi column index stats
/*********************************************************************/
REPLACE MACRO GetStatsInfo
( DBName Varchar(30)
)
AS (
/* Generate script for single column stats and single column index stats */
SELECT 'COLLECT STATISTICS ON
'||TRIM(dbase.DatabaseName)||'.'||TRIM(tvm.TVMName)||
(
CASE
WHEN TD.databasename IS NULL
THEN ' COLUMN '||TRIM(tvfields.FieldName)||';'
WHEN td.indexname is Null
THEN ' INDEX ('||TRIM(tvfields.FieldName)||');'
ELSE ' INDEX '||TRIM(td.indexname)||';'
END) (TITLE '')
FROM DBC.Dbase
INNER JOIN DBC.TVM
ON tvm.DatabaseId = dbase.DatabaseId
INNER JOIN DBC.tvfields
ON tvm.tvmid = tvfields.tableid
LEFT JOIN (
SELECT databasename,tablename,IndexNumber,indexname,MIN(Columnname) AS COL
FROM DBC.Indices
GROUP BY 1,2,3,4
HAVING MIN(Columnname) = MAX(Columnname)
OR IndexName IS NOT NULL) AS TD
ON TD.databasename = dbase.DatabaseName
AND
TD.tablename = TVM.tvmname
AND
TD.COL = tvfields.FieldName
WHERE tvfields.FieldStatistics IS NOT NULL
AND Dbase.OwnerName IN ( Sel child
From dbc.children
Where parent = :DBName)
ORDER BY dbase.DatabaseName, tvm.TVMName;
/* Generate script for multi column stats
It has a max of 17 columns used for collecting stats */
sel 'Collect stats on ' || trim(ms1.databasename) || '.' || =
trim(ms1.tablename) || ' column ( ' ,
Case
When ms1.ColumnName IS NOT NULL THEN ms1.ColumnName
ELSE ''
END,
Case
When ms2.ColumnName IS NOT NULL THEN ', ' || ms2.ColumnName
ELSE ''
END,
Case
When ms3.ColumnName IS NOT NULL THEN ', ' || ms3.ColumnName
ELSE ''
END,
Case
When ms4.ColumnName IS NOT NULL THEN ', ' || ms4.ColumnName
ELSE ''
END,
Case
When ms5.ColumnName IS NOT NULL THEN ', ' || ms5.ColumnName
ELSE ''
END,
Case
When ms6.ColumnName IS NOT NULL THEN ', ' || ms6.ColumnName
ELSE ''
END,
Case
When ms7.ColumnName IS NOT NULL THEN ', ' || ms7.ColumnName
ELSE ''
END,
Case
When ms8.ColumnName IS NOT NULL THEN ', ' || ms8.ColumnName
ELSE ''
END,
Case
When ms9.ColumnName IS NOT NULL THEN ', ' || ms9.ColumnName
ELSE ''
END,
Case
When ms10.ColumnName IS NOT NULL THEN ', ' || ms10.ColumnName
ELSE ''
END,
Case
When ms11.ColumnName IS NOT NULL THEN ', ' || ms11.ColumnName
ELSE ''
END,
Case
When ms12.ColumnName IS NOT NULL THEN ', ' || ms12.ColumnName
ELSE ''
END,
Case
When ms13.ColumnName IS NOT NULL THEN ', ' || ms13.ColumnName
ELSE ''
END,
Case
When ms14.ColumnName IS NOT NULL THEN ', ' || ms14.ColumnName
ELSE ''
END,
Case
When ms15.ColumnName IS NOT NULL THEN ', ' || ms15.ColumnName
ELSE ''
END,
Case
When ms16.ColumnName IS NOT NULL THEN ', ' || ms16.ColumnName
ELSE ''
END,
');'
From
dbc.multicolumnstats ms1
Left Outer Join
dbc.multicolumnstats ms2
On (ms1.Databasename = ms2.Databasename
And
ms1.Tablename = ms2.Tablename
And
ms1.StatisticsId = ms2.StatisticsId
And
ms2.ColumnPosition = 2)
Left Outer Join
dbc.multicolumnstats ms3
On (ms1.Databasename = ms3.Databasename
And
ms1.Tablename = ms3.Tablename
And
ms1.StatisticsId = ms3.StatisticsId
And
ms3.ColumnPosition = 3)
Left Outer Join
dbc.multicolumnstats ms4
On (ms1.Databasename = ms4.Databasename
And
ms1.Tablename = ms4.Tablename
And
ms1.StatisticsId = ms4.StatisticsId
And
ms4.ColumnPosition = 4)
Left Outer Join
dbc.multicolumnstats ms5
On (ms1.Databasename = ms5.Databasename
And
ms1.Tablename = ms5.Tablename
And
ms1.StatisticsId = ms5.StatisticsId
And
ms4.ColumnPosition = 5)
Left Outer Join
dbc.multicolumnstats ms6
On (ms1.Databasename = ms6.Databasename
And
ms1.Tablename = ms6.Tablename
And
ms1.StatisticsId = ms6.StatisticsId
And
ms4.ColumnPosition = 6)
Left Outer Join
dbc.multicolumnstats ms7
On (ms1.Databasename = ms7.Databasename
And
ms1.Tablename = ms7.Tablename
And
ms1.StatisticsId = ms7.StatisticsId
And
ms4.ColumnPosition = 7)
Left Outer Join
dbc.multicolumnstats ms8
On (ms1.Databasename = ms8.Databasename
And
ms1.Tablename = ms8.Tablename
And
ms1.StatisticsId = ms8.StatisticsId
And
ms4.ColumnPosition = 8)
Left Outer Join
dbc.multicolumnstats ms9
On (ms1.Databasename = ms9.Databasename
And
ms1.Tablename = ms9.Tablename
And
ms1.StatisticsId = ms9.StatisticsId
And
ms4.ColumnPosition = 9)
Left Outer Join
dbc.multicolumnstats ms10
On (ms1.Databasename = ms10.Databasename
And
ms1.Tablename = ms10.Tablename
And
ms1.StatisticsId = ms10.StatisticsId
And
ms4.ColumnPosition = 10)
Left Outer Join
dbc.multicolumnstats ms11
On (ms1.Databasename = ms11.Databasename
And
ms1.Tablename = ms11.Tablename
And
ms1.StatisticsId = ms11.StatisticsId
And
ms4.ColumnPosition = 11)
Left Outer Join
dbc.multicolumnstats ms12
On (ms1.Databasename = ms12.Databasename
And
ms1.Tablename = ms12.Tablename
And
ms1.StatisticsId = ms12.StatisticsId
And
ms4.ColumnPosition = 12)
Left Outer Join
dbc.multicolumnstats ms13
On (ms1.Databasename = ms13.Databasename
And
ms1.Tablename = ms13.Tablename
And
ms1.StatisticsId = ms13.StatisticsId
And
ms4.ColumnPosition = 13)
Left Outer Join
dbc.multicolumnstats ms14
On (ms1.Databasename = ms14.Databasename
And
ms1.Tablename = ms14.Tablename
And
ms1.StatisticsId = ms14.StatisticsId
And
ms4.ColumnPosition = 14)
Left Outer Join
dbc.multicolumnstats ms15
On (ms1.Databasename = ms15.Databasename
And
ms1.Tablename = ms15.Tablename
And
ms1.StatisticsId = ms15.StatisticsId
And
ms4.ColumnPosition = 15)
Left Outer Join
dbc.multicolumnstats ms16
On (ms1.Databasename = ms16.Databasename
And
ms1.Tablename = ms16.Tablename
And
ms1.StatisticsId = ms16.StatisticsId
And
ms4.ColumnPosition = 16)
Where ms1.databasename IN ( Sel child
From dbc.children
Where parent = :DBName)
And ms1.ColumnPosition = 1;
/* Generate script for multi column index stats */
SELECT 'COLLECT STATISTICS ON = '||TRIM(TD.DatabaseName)||'.'||TRIM(TD.tableName) (TITLE ''),
' INDEX('||
TRIM(TD.col1)||TRIM(TD.col2)||TRIM(TD.col3) (TITLE ''),
' =
'||TRIM(TD.col4)||TRIM(TD.col5)||TRIM(TD.col6)||TRIM(TD.col7)||');' (TITLE '')
FROM (
SELECT Indices.databasename,Indices.tablename,Indices.IndexNumber,
MAX(
CASE
WHEN Columnposition = 1 THEN columnname
Else '' END) AS COL1,
MAX(
CASE
WHEN Columnposition = 2 THEN ','||TRIM(columnname)
Else '' END) AS COL2,
MAX(
CASE
WHEN Columnposition = 3 THEN ','||TRIM(columnname)
Else '' END) AS COL3,
MAX(
CASE
WHEN Columnposition = 4 THEN ','||TRIM(columnname)
Else '' END) AS COL4,
MAX(
CASE
WHEN Columnposition = 5 THEN ','||TRIM(columnname)
Else '' END) AS COL5,
MAX(
CASE
WHEN Columnposition = 6 THEN ','||TRIM(columnname)
Else '' END) AS COL6,
MAX(
CASE
WHEN Columnposition = 7 THEN ','||TRIM(columnname)
Else '' END) AS COL7
FROM DBC.Indices
,(
SELECT databasename,tablename,IndexNumber
FROM DBC.Indexstats
WHERE IndexName IS NULL
AND IndexStatistics IS NOT NULL) AS TD1
WHERE Indices.IndexName IS NULL
AND td1.databasename = Indices.databasename
AND td1.tablename = Indices.tablename
AND td1.IndexNumber = Indices.IndexNumber
GROUP BY 1,2,3
Having MIN(ColumnPOSITION) < MAX(Columnposition))
AS TD
,DBC.Dbase
WHERE TD.databasename = dbase.DatabaseName
AND Dbase.OwnerName IN ( Sel child
From dbc.children
Where parent = :DBName)
ORDER BY 1
;
);
/*****************************************************************/
The second query currently looks a bit crude, but didnot have enough time to fix it. May be some day later!!! My 2 AUDs for all those who responded!!! Cheers, Prabhjot.
| ||||||||||||||||||||||||||||||||||||||||||||||
| | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
| Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||
