Re: Index Size

Art S. Kagel wrote: > <SNIP> Typo in both queries. st.lockid should be sp.lockid. Art S. Kagel > Should mention that the '* 2' below in each query should be changed to > '* 4' on Windows and AIX where the pagesize is 4K rather than 2K. This > works for 9.xx also and the 10/11 example in my original post doesn't > need the pagesize adjustment by platform, it's built in. > > Art S. Kagel > >>> Unfortunately this won't show the space taken up by attached indexes >>> (which are default on 7.31, the original poster's platform). >> >> >> >> Arg! Missed the version info in the original post. For attached >> indexes, and to adjust the above for 7.31: >> >> SELECT st.tabname, sp.npused as indexpages, >> (sp.npused * 2) as indexKB >> FROM sysmaster:systabnames st, >> sysmaster:systabnames st2, >> sysmaster:sysptnhdr sp, >> sysmaster:sysdbspaces sd >> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable' >> AND st2.partnum = st.lockid >> AND st.partnum = sp.partnum >> AND sd.dbsnum = (sp.partnum /1048576) >> AND st.tabname != st2.tabname >> UNION ALL >> SELECT st.tabname, sp.npused as indexpages, >> ((sp.npused - npdata) * 2) as indexKB >> FROM sysmaster:systabnames st, >> sysmaster:systabnames st2, >> sysmaster:sysptnhdr sp, >> sysmaster:sysdbspaces sd >> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable' >> AND st2.partnum = st.lockid >> AND st.partnum = sp.partnum >> AND sd.dbsnum = (sp.partnum /1048576) >> AND st.tabname = st2.tabname>> ; >> >> Art S. Kagel