|
|
|
 |
 |
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 | ||||||||||||||||||||||||||||||||||||||||||||||||
