 |
 |
Archives of the TeradataForum
Message Posted: Mon, 29 Jan 2007 @ 18:14:21 GMT
| Subj: | | Multi-column index COLLECT STATISTICS script |
| |
| From: | | Mark Jahnke |
Can anyone provide a script that does the same thing as this script below -- except using LEFT OUTER JOIN?
SELECT 'collect stats on ' || TRIM(databasename) || '.'
||TRIM(tablename) || ' index ('
|| MAX(CASE WHEN columnposition = 1 THEN TRIM(columnname)
ELSE NULL END)
|| MAX(CASE WHEN columnposition = 2 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 3 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 4 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 5 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 6 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 7 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 8 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 9 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 10 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 11 THEN ',' || TRIM(columnname)
ELSE '' END)
|| MAX(CASE WHEN columnposition = 12 THEN ',' || TRIM(columnname)
ELSE '' END)
|| ');' (title'') from dbc.indices
group by databasename, tablename
order by databasename , tablename;
Mark Jahnke
Guardian, New York
| |
