Teradata Tidbits: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
(2 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
== Record Count for all Tables in a Database == | == Record Count for all Tables in a Database == | ||
< | <pre>select 'select count (1), ''' || trim(tablename) || ''' (varchar(30)) from ' || trim(databasename) || '.' || trim (tablename) || ' union ' | ||
from dbc.tables | from dbc.tables | ||
where databasename = 'my_database';</ | where databasename = 'my_database';</pre> | ||
== Recreate Collect Statistics Script (requires privs) == | |||
<pre>SELECT 'collect statistics on ' || TRIM ( databasename ) || '.' || TRIM ( tablename ) | |||
|| | |||
Case | |||
When indextype = 'M' Then ' column (' | |||
Else ' index (' | |||
End | |||
|| indexcols || ');' | |||
FROM ( | |||
SELECT databasename , tablename , indexnumber , indextype, MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 1 THEN TRIM ( columnname ) | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 2 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 3 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 4 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 5 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 6 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 7 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) || MAXIMUM ( | |||
CASE | |||
WHEN columnposition = 8 THEN ',' || TRIM ( columnname ) | |||
ELSE '' | |||
END ) AS indexcols | |||
FROM ( | |||
SELECT c.databasenamei AS databasename | |||
, b.tvmnamei AS tablename | |||
, d.indexnumber AS indexnumber | |||
, d.indextype AS indextype | |||
, d.fieldposition AS columnposition | |||
, a.fieldname AS columnname | |||
FROM dbc.tvfields a | |||
, dbc.tvm b | |||
, dbc.dbase c | |||
, dbc.indexes d | |||
WHERE a.tableid = b.tvmid | |||
AND b.databaseid = c.databaseid | |||
AND d.fieldid = a.fieldid | |||
AND d.tableid = b.tvmid | |||
AND d.databaseid = c.databaseid | |||
AND ( d.databaseid , d.tableid , d.indexnumber ) IN ( | |||
SELECT databaseid , tableid , indexnumber | |||
FROM dbc.indexes | |||
WHERE fieldposition = 1 | |||
AND indexstatistics IS NOT NULL ) | |||
AND databasename = 'MY_DBNAME' ) a | |||
GROUP BY 1 , 2 , 3, 4 ) b | |||
UNION | |||
SELECT 'collect statistics on ' || TRIM ( databasename ) || '.' || TRIM ( tablename ) || ' column (' || indexcols || ');' | |||
FROM ( | |||
SELECT databasename , tablename , TRIM ( columnname ) AS indexcols | |||
FROM ( | |||
SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename , a.fieldname AS columnname | |||
FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c | |||
WHERE a.tableid = b.tvmid | |||
AND b.databaseid = c.databaseid | |||
AND a.fieldstatistics IS NOT NULL | |||
AND databasename = 'MY_DBNAME' ) a | |||
GROUP BY 1 , 2 , 3 ) b ; </pre> |
Latest revision as of 07:14, 12 September 2007
Record Count for all Tables in a Database
select 'select count (1), ''' || trim(tablename) || ''' (varchar(30)) from ' || trim(databasename) || '.' || trim (tablename) || ' union ' from dbc.tables where databasename = 'my_database';
Recreate Collect Statistics Script (requires privs)
SELECT 'collect statistics on ' || TRIM ( databasename ) || '.' || TRIM ( tablename ) || Case When indextype = 'M' Then ' column (' Else ' index (' End || indexcols || ');' FROM ( SELECT databasename , tablename , indexnumber , indextype, MAXIMUM ( CASE WHEN columnposition = 1 THEN TRIM ( columnname ) END ) || MAXIMUM ( CASE WHEN columnposition = 2 THEN ',' || TRIM ( columnname ) ELSE '' END ) || MAXIMUM ( CASE WHEN columnposition = 3 THEN ',' || TRIM ( columnname ) ELSE '' END ) || MAXIMUM ( CASE WHEN columnposition = 4 THEN ',' || TRIM ( columnname ) ELSE '' END ) || MAXIMUM ( CASE WHEN columnposition = 5 THEN ',' || TRIM ( columnname ) ELSE '' END ) || MAXIMUM ( CASE WHEN columnposition = 6 THEN ',' || TRIM ( columnname ) ELSE '' END ) || MAXIMUM ( CASE WHEN columnposition = 7 THEN ',' || TRIM ( columnname ) ELSE '' END ) || MAXIMUM ( CASE WHEN columnposition = 8 THEN ',' || TRIM ( columnname ) ELSE '' END ) AS indexcols FROM ( SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename , d.indexnumber AS indexnumber , d.indextype AS indextype , d.fieldposition AS columnposition , a.fieldname AS columnname FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c , dbc.indexes d WHERE a.tableid = b.tvmid AND b.databaseid = c.databaseid AND d.fieldid = a.fieldid AND d.tableid = b.tvmid AND d.databaseid = c.databaseid AND ( d.databaseid , d.tableid , d.indexnumber ) IN ( SELECT databaseid , tableid , indexnumber FROM dbc.indexes WHERE fieldposition = 1 AND indexstatistics IS NOT NULL ) AND databasename = 'MY_DBNAME' ) a GROUP BY 1 , 2 , 3, 4 ) b UNION SELECT 'collect statistics on ' || TRIM ( databasename ) || '.' || TRIM ( tablename ) || ' column (' || indexcols || ');' FROM ( SELECT databasename , tablename , TRIM ( columnname ) AS indexcols FROM ( SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename , a.fieldname AS columnname FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c WHERE a.tableid = b.tvmid AND b.databaseid = c.databaseid AND a.fieldstatistics IS NOT NULL AND databasename = 'MY_DBNAME' ) a GROUP BY 1 , 2 , 3 ) b ;