select a.size_in_mb + coalesce(b.size_in_mb,0) from (select sum(data_object_l_size + dictionary_size/1024.0)/1024.0 size_in_mb from table(sysproc.admin_get_tab_info_v97('${schema}','${name}'))) a left join (select sum(index_object_l_size)/1024.0 size_in_mb from table(sysproc.admin_get_index_info('T','${schema}','${name}'))) b on 1=1; 问题还没结束,新查法得到的所有表和索引总大小又比表空间的已使用量高很多。于是再仔细看文档,这个 sysproc.admin_get_index_info 返回的结果比较特别,根据表和索引的范围分区有以下三种情况: 1.如果是非分区表上的索引,那么汇报的是表上所有索引的总大小,而不是那个索引的单独大小; 2.如果是分区表的分区索引,那么汇报的是那个数据分区上的所有索引的总大小,即多个索引的部分大小之和; 3.如果是分区表上的非分区索引,汇报的才是这个索引在每个数据库节点的单独大小。
弄清了上面的三点,再把查询语句改成下面这样的,就得到了与表空间已使用量一致的统计结果了。
select a.size_in_mb + coalesce(b.size_in_mb,0) from (select sum(data_object_l_size + dictionary_size/1024.0)/1024.0 size_in_mb from table(sysproc.admin_get_tab_info_v97('${schema}','${name}'))) a left join (select sum(index_object_l_size)/1024.0 size_in_mb from (select * from (select index_object_l_size,row_number() over(partition by dbpartitionnum) sn from table(sysproc.admin_get_index_info('T','${schema}','${name}')) where index_partitioning='') where sn=1 union all select * from (select index_object_l_size,row_number() over(partition by dbpartitionnum,datapartitionid) sn from table(sysproc.admin_get_index_info('T','${schema}','${name}')) where index_partitioning='P') where sn=1 union all select index_object_l_size,1 sn from table(sysproc.admin_get_index_info('T','${schema}','${name}')) where index_partitioning='N') ) b on 1=1; 也可以写成下面这样的:
select a.size_in_mb + coalesce(b.size_in_mb,0) from (select sum(data_object_l_size + dictionary_size/1024.0)/1024.0 size_in_mb from table(sysproc.admin_get_tab_info_v97('${schema}','${name}'))) a left join (select sum(index_object_l_size)/1024.0 size_in_mb from (select t1.index_object_l_size,row_number() over(partition by t1.dbpartitionnum,t2.index_objectid) sn from table(sysproc.admin_get_index_info('T','${schema}','${name}')) t1, (select iid,index_objectid,0 datapartitionid from syscat.indexes where tabschema='${schema}' and tabname='${name}' and iid not in (select iid from syscat.indexpartitions where tabschema='${schema}' and tabname='${name}') union all select iid,indpartitionobjectid index_objectid,datapartitionid from syscat.indexpartitions where tabschema='${schema}' and tabname='${name}') t2 where t1.iid=t2.iid and t1.datapartitionid=t2.datapartitionid ) where sn=1 ) b on 1=1; 起初看似简单的问题,却让我两次犯错。更甚的是,文档上对这几个字段的特性有明确说明,我居然视而未见。