 |
 |
Archives of the TeradataForum
Message Posted: Tue, 07 Apr 2009 @ 16:42:07 GMT
| Subj: | | Re: Dieter's collect stats query on v12 |
| |
| From: | | Kevin.Leach |
Below is a modified query which accounts for TDv12(also works on TDv13) as well as the bitness(32bit or 64bit) of the OS. This query does have
a prerequisite of a simple function compiled to gives us the bitness. Maybe there's a more graceful way to do this?
Here's a simple test table create to test your environment.
drop table vers_stats;
create multiset table vers_stats (col1 char(5));
insert into vers_stats values ('hello');
insert into vers_stats values ('hello');
insert into vers_stats values (NULL);
insert into vers_stats values ('there');
The below sql should return this row, and of course should avoid the
numeric overflow.
TableName ColumnName SampleSize
NumRows NumValues NumNulls ModeFreq
------------------------------ ------------------------------
-------------------- -------------------- --------------------
-------------------- -----------------
VERS_STATS col1
100 4. 3.
1. 2.
Kevin
-- http://www.teradataforum.com/teradata/20080528_141149.htm
-- requires precompiled function 'floor'
-- returns table name, column name and stats for all tables in logged
in database
-- original code modified to work on 64-bit and/or TDv12+ dbms
-- tested on v2r6 Windows 32-bit, MP-RAS 32-bit, Windows 64-bit
-- v12 Windows 32-bit, Linux 64-bit
-- v13 Linux 64-bit
SELECT
trim(TableName) TableName,
trim(ColumnName) ColumnName,
SampleSize,
NumRows,
NumValues,
NumNulls,
ModeFreq
FROM
( SELECT
TableName,
ColumnName,
CASE
WHEN HASHBUCKET ('00'xb || SUBSTR(Statss, 11, 1) (BYTE(4))) = 1
THEN HASHBUCKET ('00'xb || SUBSTR(Statss, 12, 1) (BYTE(4)))
ELSE 100
END AS SampleSize,
HASHBUCKET(SUBSTR(Statss, 13+offset2+7, 1)
|| SUBSTR(Statss, 13+offset2+6, 1) (BYTE(4)))/d AS NumNullsw1,
HASHBUCKET(SUBSTR(Statss, 13+offset2+5, 1)
|| SUBSTR(Statss, 13+offset2+4, 1) (BYTE(4)))/d AS NumNullsw2,
HASHBUCKET(SUBSTR(Statss, 13+offset2+3, 1)
|| SUBSTR(Statss, 13+offset2+2, 1) (BYTE(4)))/d AS NumNullsw3,
HASHBUCKET(SUBSTR(Statss, 13+offset2+1, 1)
|| SUBSTR(Statss, 13+offset2+0, 1) (BYTE(4)))/d AS NumNullsw4,
(-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,
HASHBUCKET(SUBSTR(Statss, 41+Offset+7, 1)
|| SUBSTR(Statss, 41+Offset+6, 1) (BYTE(4)))/d AS ModeFreqw1,
HASHBUCKET(SUBSTR(Statss, 41+Offset+5, 1)
|| SUBSTR(Statss, 41+Offset+4, 1) (BYTE(4)))/d AS ModeFreqw2,
HASHBUCKET(SUBSTR(Statss, 41+Offset+3, 1)
|| SUBSTR(Statss, 41+Offset+2, 1) (BYTE(4)))/d AS ModeFreqw3,
HASHBUCKET(SUBSTR(Statss, 41+Offset+1, 1)
|| SUBSTR(Statss, 41+Offset+0, 1) (BYTE(4)))/d AS ModeFreqw4,
(-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,
HASHBUCKET(SUBSTR(Statss, 49+Offset+7, 1)
|| SUBSTR(Statss, 49+Offset+6, 1) (BYTE(4)))/d AS NumValuesw1,
HASHBUCKET(SUBSTR(Statss, 49+Offset+5, 1)
|| SUBSTR(Statss, 49+Offset+4, 1) (BYTE(4)))/d AS NumValuesw2,
HASHBUCKET(SUBSTR(Statss, 49+Offset+3, 1)
|| SUBSTR(Statss, 49+Offset+2, 1) (BYTE(4)))/d AS NumValuesw3,
HASHBUCKET(SUBSTR(Statss, 49+Offset+1, 1)
|| SUBSTR(Statss, 49+Offset+0, 1) (BYTE(4)))/d AS NumValuesw4,
(-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,
HASHBUCKET(SUBSTR(Statss, 57+Offset+7, 1)
|| SUBSTR(Statss, 57+Offset+6, 1) (BYTE(4)))/d AS NumRowsw1,
HASHBUCKET(SUBSTR(Statss, 57+Offset+5, 1)
|| SUBSTR(Statss, 57+Offset+4, 1) (BYTE(4)))/d AS NumRowsw2,
HASHBUCKET(SUBSTR(Statss, 57+Offset+3, 1)
|| SUBSTR(Statss, 57+Offset+2, 1) (BYTE(4)))/d AS NumRowsw3,
HASHBUCKET(SUBSTR(Statss, 57+Offset+1, 1)
|| SUBSTR(Statss, 57+Offset+0, 1) (BYTE(4)))/d AS NumRowsw4,
(-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
FROM
( SELECT
TableName,
ColumnName,
CASE
WHEN trim(version)||trim(bitness) = '632'
then case
when SUBSTR(FieldStatistics, 23, 1) = '00'XB then 16
else 0
end
WHEN trim(version)||trim(bitness) = '664'
then case
when SUBSTR(FieldStatistics, 27, 1) = '00'XB then 24
else 8
end
WHEN trim(version)||trim(bitness) = '1232'
then case
when SUBSTR(FieldStatistics, 23, 1) = '00'XB then 50
else 34
end
WHEN trim(version)||trim(bitness) = '1264'
then case
when SUBSTR(FieldStatistics, 27, 1) = '00'XB then 64
else 48
end
END AS offset,
CASE
WHEN bitness = 64 THEN 4
ELSE 0
END AS offset2,
CASE
WHEN version = 12 THEN 16
ELSE 1
END AS d,
FieldStatistics AS Statss
FROM
(select
CASE
WHEN v.cnt = 0 THEN 6
ELSE 12
END AS version,
CASE
WHEN b.cnt = 0 then 32
else 64
end as bitness
from
(select count(*) as cnt
from dbc.columnstats
where databasename=database and
SUBSTR(FieldStatistics, 9, 1) = '03'XB) as v,
(select count(*) as cnt
from dbc.functions
where databasename=database and functionname='FLOOR'
and platform like '%64%') b) as vb,
dbc.ColumnStats
WHERE
Statss IS NOT NULL
and DatabaseName = database
and tablename='vers_stats'
) dt
) dtt
ORDER BY TableName, ColumnName;
| |
