查询表及索引所占空间

库中所有表、索引分别所占空间统计(按分片统计)

dbaccess sysmaster -

select
st.dbsname databasename,st.tabname,sd.name dbspacename,f.PARTITION partitionname,
ti_nextns extents,
sin.ti_nrows,sin.ti_pagesize,  sin.ti_rowsize,
sin.ti_nptotal nptotal, round(sin.ti_nptotal*sd.pagesize/1024/1024,2)||'MB' total_size,
sin.ti_npused npused, round(sin.ti_npused*sd.pagesize/1024/1024,2)||'MB' used_size,
sin.ti_nextsiz nextsize
from
sysmaster:systabnames st JOIN sysmaster:systabinfo sin ON  st.partnum=sin.ti_partnum
JOIN sysmaster:sysdbspaces sd ON sd.dbsnum = trunc(st.partnum/1048576)
LEFT JOIN testdb:sysfragments f --testdb更换为需要统计的库名
ON st.partnum=f.partn
WHERE
st.dbsname='testdb' --testdb更换为需要统计的库名
AND st.tabname[1,3]!='sys';

输出示例：

databasename   testdb
tabname        t1
dbspacename    datadbs01
partitionname  jtk_trade_p0
extents        1
ti_nrows       1
ti_pagesize    16384
ti_rowsize     4
nptotal        4
total_size     0.06MB
npused         2
used_size      0.03MB
nextsize       32

databasename   testdb
tabname        index_t1
dbspacename    datadbs01
partitionname  jtk_trade_p0
extents        1
ti_nrows       0
ti_pagesize    16384
ti_rowsize     4
nptotal        4
total_size     0.06MB
npused         3
used_size      0.05MB
nextsize       80

databasename   testdb
tabname        t1
dbspacename    datadbs02
partitionname  sys_p2
extents        1
ti_nrows       1
ti_pagesize    16384
ti_rowsize     4
nptotal        4
total_size     0.06MB
npused         2
used_size      0.03MB
nextsize       32

databasename   testdb
tabname        t1
dbspacename    datadbs02
partitionname  sys_p3
extents        1
ti_nrows       1
ti_pagesize    16384
ti_rowsize     4
nptotal        4
total_size     0.06MB
npused         2
used_size      0.03MB
nextsize       32

databasename   testdb
tabname        index_t1
dbspacename    datadbs02
partitionname  sys_p2
extents        1
ti_nrows       0
ti_pagesize    16384
ti_rowsize     4
nptotal        4
total_size     0.06MB
npused         3
used_size      0.05MB
nextsize       80

databasename   testdb
tabname        index_t1
dbspacename    datadbs02
partitionname  sys_p3
extents        1
ti_nrows       0
ti_pagesize    16384
ti_rowsize     4
nptotal        4
total_size     0.06MB
npused         3
used_size      0.05MB
nextsize       80

库中所有表、索引分别所占空间统计(按表、索引统计)

dbaccess sysmaster -

select
st.dbsname databasename,st.tabname,
sum(ti_nextns) extents,
sum(sin.ti_nrows) nrows,
max(sin.ti_pagesize) pagesize,
max(sin.ti_rowsize) rowsize,
sum(sin.ti_nptotal) nptotal,
round(sum(sin.ti_nptotal*sd.pagesize)/1024/1024,2)||'MB' total_size,
sum(sin.ti_npused) npused,
round(sum(sin.ti_npused*sd.pagesize)/1024/1024,2)||'MB' used_size
from
sysmaster:systabnames st JOIN sysmaster:systabinfo sin ON  st.partnum=sin.ti_partnum
JOIN sysmaster:sysdbspaces sd ON sd.dbsnum = trunc(st.partnum/1048576)
WHERE
st.dbsname='testdb' --testdb更换为需要统计的库名
AND st.tabname[1,3]!='sys'
GROUP BY 1,2
ORDER BY 7 DESC;

输出示例：

databasename  testdb
tabname       index_t1
extents       3
nrows         0
pagesize      16384
rowsize       4
nptotal       12
total_size    0.19MB
npused        9
used_size     0.14MB