Teradata Tidbits: Difference between revisions

From ChipWiki
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 ==


<nowiki>select 'select count (1), ''' || trim(tablename) || ''' (varchar(30)) from ' || trim(databasename) || '.' || trim (tablename) || ' union '
<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';</nowiki>
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 ;