学步园

数据库版本是V9.7，要统计数据库中表及其上的索引占用的空间大小。

初看这个问题，感觉很简单，马上写出了如下的查询语句：

select sum(data_object_l_size + index_object_l_size + dictionary_size/1024.0)/1024.0
from table(sysproc.admin_get_tab_info_v97('${schema}','${name}'));

注：库中没有 long, lob, xml 数据类型，所以忽略了相关字段。另外统计的是逻辑大小，也可以统计物理大小。

然而这样查出所有表的占有空间后，发现与表空间的已使用量小很多，查阅文档后发现 sysproc.admin_get_tab_info_v97 中汇报的索引大小不包含分区表上的非分区索引，于是按文档中的指示，通过 sysproc.admin_get_index_info 来查索引的大小。我想当然的写出了如下语句：

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;

起初看似简单的问题，却让我两次犯错。更甚的是，文档上对这几个字段的特性有明确说明，我居然视而未见。