|
今天写了一个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
====
请大家看看,是不是还有什么地方需要修改(性能等)?
另外,如何估算一张表的空间使用率,除了用根据记录平均长度,还有npage等,还有什么其它更好一点的方法?
先谢谢大家了! |
|