如何估算一张表的CARDINALITY 和它的使用率
今天写了一个SQL，根据记录的平均长度来估算数据库中某张表的CARDINALITY 和它的使用率.
SQL
====
select a.tabschema , a.tabname ,a.card ,
case b.pagesize
when 4096
Then
card*count_avg*100/64000000000
when 8192
Then
card*count_avg*100/128000000000
when 16384
Then
card*count_avg*100/256000000000
when 32768
Then
card*count_avg*100/512000000000
else null
end as used
from syscat.tables a , syscat.tablespaces b , (
select tabname,tabschema, SYSIBM . sum ( AVGCOLLEN ) count_avg
from syscat.columns
group by tabname,tabschema ) c
where a.tbspaceid = b.tbspaceid
and a.tabname = c.tabname
and a.tabschema = c.tabschema
and card > 600000000
====
收起