|
Archives of the TeradataForumMessage Posted: Tue, 14 Mar 2006 @ 11:04:39 GMT
Hi Subrahmanian, I'm suspecting that what you want to hear is thats there is a table out there called something like dbc.rowCount and that all you would have to do is submit the following SQL Select * from dbc.rowCount where databaseName = 'xxxxxxx' Unfortunately maintaining a table like this would be very resource intensive for some thing that would only be used the odd time by DBA's and Developers. After every single successful SQL request this table would have to be updated with the current count(*) for that table (or tables). I personally don't think that is really be the best thing for a database to be doing, and I'm sure that there could be all sorts of problems with Dead Locks and Roll Backs. I've seen this question asked a few times and the best solution I've seen is Affan's SELECT 'SELECT ' || '''' || TRIM(BOTH FROM DATABASENAME) || '.' || TRIM(BOTH FROM TABLENAME) || '''' || ' AS col1,' || ' COUNT(*) AS ROW_COUNT FROM ' || TRIM(BOTH FROM DATABASENAME) || '.' || TRIM(BOTH FROM TABLENAME) || ';' FROM DBC.TABLES WHERE DATABASENAME = 'DT_TAB' AND TABLEKIND ='t' ORDER BY 1 or one which I think was submitted by Dieter about 3 years which may help if you collect stats on unique primary indexes. My apologies if I'm not giving the right person credit for this code, they deserve it!!! Hope this helps, Fachtna O'Donovan SELECT DatabaseName, TableName, ColumnName, /** stats collected on: 'C' --> Column 'I' --> Index 'M' --> Multiple columns (V2R5) **/ StatsType, /** collect stats date **/ CollectDate, /** collect stats time **/ CollectTime, /** V2R5: sample size used for collect stats**/ CASE SampleSize WHEN 0 THEN 100 ELSE SampleSize END AS SampleSize, /** Row Count Estimated when SampleSize < 100 **/ (-1**(NumRowsw1 / 32768)) * (2**((NumRowsw1 / 16 mod 2048) - 1023)) * (1 + ((NumRowsw1 mod 16) * 2**-4) + (NumRowsw2 * 2**-20) + (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52)) (decimal(18,0)) AS NumRows, /** Distinct Values Estimated when SampleSize < 100 **/ (-1**(NumValuesw1 / 32768)) * (2**((NumValuesw1 / 16 mod 2048) - 1023)) * (1 + ((NumValuesw1 mod 16) * 2**-4) + (NumValuesw2 * 2**-20) + (NumValuesw3 * 2**-36) + (NumValuesw4 * 2**-52)) (decimal(18,0)) AS NumValues, /** Number of NULLs Estimated when SampleSize < 100 **/ (-1**(NumNullsw1 / 32768)) * (2**((NumNullsw1 / 16 mod 2048) - 1023)) * (1 + ((NumNullsw1 mod 16) * 2**-4) + (NumNullsw2 * 2**-20) + (NumNullsw3 * 2**-36) + (NumNullsw4 * 2**-52)) (decimal(18,0)) AS NumNulls, /** Maximum number of rows / value, Estimated when SampleSize < 100 **/ (-1**(ModeFreqw1 / 32768)) * (2**((ModeFreqw1 / 16 mod 2048) - 1023)) * (1 + ((ModeFreqw1 mod 16) * 2**-4) + (ModeFreqw2 * 2**-20) + (ModeFreqw3 * 2**-36) + (ModeFreqw4 * 2**-52)) (decimal(18,0)) AS ModeFreq FROM ( SELECT DatabaseName, TableName, ColumnName, Stats, StatsType, ( (HASHBUCKET (SUBSTR(Stats, 2, 1) || SUBSTR(Stats, 1, 1) (BYTE(4)) ) - 1900 ) * 10000 + (HASHBUCKET ('00'xb || SUBSTR(Stats, 3, 1) (BYTE(4)) ) ) * 100 + (HASHBUCKET ( '00'xb || SUBSTR(Stats, 4, 1) (BYTE(4)) ) ) ) (DATE) AS CollectDate, (CAST( (HASHBUCKET (CAST('00'xb || SUBSTR(Stats, 5, 1) AS BYTE(4)) ) (FORMAT '99:') ) || (HASHBUCKET (CAST('00'xb || SUBSTR(Stats, 6, 1) AS BYTE(4)) ) (FORMAT '99:') ) || (HASHBUCKET (CAST('00'xb || SUBSTR(Stats, 7, 1) AS BYTE(4)) ) (FORMAT '99.') ) || (HASHBUCKET (CAST('00'xb || SUBSTR(Stats, 8, 1) AS BYTE(4)) ) (FORMAT '99') ) AS TIME(2)) ) AS CollectTime, HASHBUCKET ('00'xb || SUBSTR(Stats, 12, 1) (BYTE(4))) AS SampleSize, HASHBUCKET(SUBSTR(Stats, 12 + 8, 1) || SUBSTR(Stats, 12 + 7, 1) (BYTE(4))) AS NumNullsw1, HASHBUCKET(SUBSTR(Stats, 12 + 6, 1) || SUBSTR(Stats, 12 + 5, 1) (BYTE(4))) AS NumNullsw2, HASHBUCKET(SUBSTR(Stats, 12 + 4, 1) || SUBSTR(Stats, 12 + 3, 1) (BYTE(4))) AS NumNullsw3, HASHBUCKET(SUBSTR(Stats, 12 + 2, 1) || SUBSTR(Stats, 12 + 1, 1) (BYTE(4))) AS NumNullsw4, HASHBUCKET(SUBSTR(Stats, 40 + Offset + 8, 1) || SUBSTR(Stats, 40 + Offset + 7, 1) (BYTE(4))) AS ModeFreqw1, HASHBUCKET(SUBSTR(Stats, 40 + Offset + 6, 1) || SUBSTR(Stats, 40 + Offset + 5, 1) (BYTE(4))) AS ModeFreqw2, HASHBUCKET(SUBSTR(Stats, 40 + Offset + 4, 1) || SUBSTR(Stats, 40 + Offset + 3, 1) (BYTE(4))) AS ModeFreqw3, HASHBUCKET(SUBSTR(Stats, 40 + Offset + 2, 1) || SUBSTR(Stats, 40 + Offset + 1, 1) (BYTE(4))) AS ModeFreqw4, HASHBUCKET(SUBSTR(Stats, 48 + Offset + 8, 1) || SUBSTR(Stats, 48 + Offset + 7, 1) (BYTE(4))) AS NumValuesw1, HASHBUCKET(SUBSTR(Stats, 48 + Offset + 6, 1) || SUBSTR(Stats, 48 + Offset + 5, 1) (BYTE(4))) AS NumValuesw2, HASHBUCKET(SUBSTR(Stats, 48 + Offset + 4, 1) || SUBSTR(Stats, 48 + Offset + 3, 1) (BYTE(4))) AS NumValuesw3, HASHBUCKET(SUBSTR(Stats, 48 + Offset + 2, 1) || SUBSTR(Stats, 48 + Offset + 1, 1) (BYTE(4))) AS NumValuesw4, HASHBUCKET(SUBSTR(Stats, 56 + Offset + 8, 1) || SUBSTR(Stats, 56 + Offset + 7, 1) (BYTE(4))) AS NumRowsw1, HASHBUCKET(SUBSTR(Stats, 56 + Offset + 6, 1) || SUBSTR(Stats, 56 + Offset + 5, 1) (BYTE(4))) AS NumRowsw2, HASHBUCKET(SUBSTR(Stats, 56 + Offset + 4, 1) || SUBSTR(Stats, 56 + Offset + 3, 1) (BYTE(4))) AS NumRowsw3, HASHBUCKET(SUBSTR(Stats, 56 + Offset + 2, 1) || SUBSTR(Stats, 56 + Offset + 1, 1) (BYTE(4))) AS NumRowsw4 FROM ( SELECT d.databasename AS DatabaseName, t.tvmname AS TableName, /** Concat up to 16 columnnames into a comma delimited list **/ MAX(CASE WHEN i.FieldPosition = 1 THEN TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 2 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 3 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 4 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 5 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 6 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 7 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 8 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 9 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 10 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 11 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 12 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 13 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 14 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 15 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition = 16 THEN ',' || TRIM(c.FieldName) ELSE '' END) || MAX(CASE WHEN i.FieldPosition > 16 THEN ',...' ELSE '' END) AS ColumnName, CASE WHEN IndexType = 'M' THEN 'M' ELSE 'I' END AS StatsType, /** Floats are stored after the data and data may be 16 or 32 BYTEs **/ /** depending on BYTE[23]**/ MAX(CASE WHEN SUBSTR(i.indexStatistics, 23, 1) = '00'XB THEN 16 ELSE 0 END) AS Offset, MAX(SUBSTR(i.indexstatistics, 1, 80)) AS Stats FROM dbc.dbase d JOIN dbc.tvm t ON d.databaseid = t.databaseid JOIN dbc.tvfields c ON t.tvmid = c.tableid JOIN dbc.Indexes i ON c.tableid = i.tableid AND c.fieldid = i.fieldid GROUP BY DatabaseName, TableName, StatsType, i.IndexNumber UNION ALL SELECT d.databasename AS DatabaseName, t.tvmname AS TableName, c.fieldname AS ColumnName, 'C' AS StatsType, /** Floats are stored after the data and data may be 16 or 32 BYTEs **/ /** depending on BYTE[23]**/ CASE WHEN SUBSTR(c.fieldStatistics, 23, 1) = '00'XB THEN 16 ELSE 0 END AS Offset, SUBSTR(c.fieldstatistics, 1, 80) AS Stats FROM dbc.dbase d JOIN dbc.tvm t ON d.databaseid = t.databaseid JOIN dbc.tvfields c ON t.tvmid = c.tableid ) dt ) dt WHERE Stats IS NOT NULL ORDER BY DatabaseName, TableName, ColumnName ;
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||