Size of Index for given table
The poster's sysmaster queries to find total index size for a table returned no rows. Art Kagel supplied a corrected query joining systabnames (twice) to sysptnhdr via sph.lockid/partnum, using dbinfo('dbspace',...) and npused-npdata to show index pages per partition, with a filter on the index name if wanted. He explained nptotal/npused/npdata, noted bitmap pages (1 per 4096 pages) must also be subtracted, and advised converting pages to KB using sysptnhdr.pagesize (or sysshmvals.sh_pagesize) rather than hardcoding 2K. He also explained why index size doesn't scale linearly with key size (rowids, duplicate-key compression, partly filled leaf pages). A follow-up confirmed the 4096 bitmap-page limit per partition.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I tried running following query to get the total size of index for a
given table but I don't get any results. My indexes are in separate
dbspace as of row data:
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 = sp.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 = sp.lockid
AND st.partnum = sp.partnum
AND sd.dbsnum = (sp.partnum /1048576)
AND st.tabname = st2.tabname
I also tried below query to get the total size of index but I am not
sure if this is correct:
select sysptprof.dbsname database,
systabnames.tabname indexname,
keylen,
round((ti_nptotal* sysshmvals.sh_pagesize)/1024,0) totalsize,
sum(lockreqs +
lockwts +
deadlks +
lktouts +
isreads +
iswrites +
isrewrites +
isdeletes +
bufreads +
bufwrites +
seqscans +
pagreads +
pagwrites) totalops
from sysptprof, sysptnkey, systabnames, sysshmvals, systabinfo
where
sysptnkey.partnum = sysptprof.partnum
and
sysptnkey.partnum = systabnames.partnum
and
systabnames.partnum = systabinfo.ti_partnum
and
systabnames.tabname not matches "*sys*"
group by 1,2,3,4
On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote: > I tried running following query to get the total size of index for a > given table but I don't get any results. My indexes are in separate > dbspace as of row data: > <SNIP> Try this one Mohitan: select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname database, st2.tabname partition, nptotal, npused, npdata, (npused - npdata) npindex from systabnames st1, systabnames st2, sysptnhdr sph where st1.partnum = sp.lockid and st2.partnum = sph.partnum and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable' order by 2, 3, 1; To filter for a particular index add a filter on: " and st2.tabname = 'myindexname' " Art S. Kagel
On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a > > given table but I don't get any results. My indexes are in separate > > dbspace as of row data: > > <SNIP> > > Try this one Mohitan: > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > database, > st2.tabname partition, nptotal, npused, > npdata, (npused - npdata) npindex > from systabnames st1, systabnames st2, sysptnhdr sph > where st1.partnum = sp.lockid and st2.partnum = sph.partnum > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable' > order by 2, 3, 1; > > To filter for a particular index add a filter on: " and st2.tabname = > 'myindexname' " > > Art S. Kagel the " sp.lockid " should be " sp2.lockid " Art S. Kagel
On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a > > > given table but I don't get any results. My indexes are in separate > > > dbspace as of row data: > > > <SNIP> > > > Try this one Mohitan: > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > > database, > > st2.tabname partition, nptotal, npused, > > npdata, (npused - npdata) npindex > > from systabnames st1, systabnames st2, sysptnhdr sph > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable' > > order by 2, 3, 1; > > > To filter for a particular index add a filter on: " and st2.tabname = > > 'myindexname' " > > > Art S. Kagel > > the " sp.lockid " should be " sp2.lockid " > > Art S. Kagel AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA Trying again: the " sp.lockid " should be " sph.lockid " Art S. Kagel
On Sep 4, 12:22 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a > > > > given table but I don't get any results. My indexes are in separate > > > > dbspace as of row data: > > > > <SNIP> > > > > Try this one Mohitan: > > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > > > database, > > > st2.tabname partition, nptotal, npused, > > > npdata, (npused - npdata) npindex > > > from systabnames st1, systabnames st2, sysptnhdr sph > > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum > > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable' > > > order by 2, 3, 1; > > > > To filter for a particular index add a filter on: " and st2.tabname = > > > 'myindexname' " > > > > Art S. Kagel > > > the " sp.lockid " should be " sp2.lockid " > > > Art S. Kagel > > AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA > > Trying again: > > the " sp.lockid " should be " sph.lockid " > > Art S. Kagel Few more questions: 1. What does npdata stand for. I didn't understand npused - npdata in the above query. I was earlier thinking of something like npused * 2k page size. 2. One more thing I don't understand is this: For eg: Table A has index I1 on 1 key of 4 bytes and index I2 on 2 keys of 8bytes, so logically total index size of I1 should be half of the size of I2. But, when I see the statistics of my database I don't see that relation. Is there something else because of which it doesn't compute the way I think it should.
On Sep 5, 12:59 am, mohitanch...@gmail.com wrote: > On Sep 4, 12:22 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a > > > > > given table but I don't get any results. My indexes are in separate > > > > > dbspace as of row data: > > > > > <SNIP> > > > > > Try this one Mohitan: > > > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > > > > database, > > > > st2.tabname partition, nptotal, npused, > > > > npdata, (npused - npdata) npindex > > > > from systabnames st1, systabnames st2, sysptnhdr sph > > > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum > > > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable' > > > > order by 2, 3, 1; > > > > > To filter for a particular index add a filter on: " and st2.tabname = > > > > 'myindexname' " > > > > > Art S. Kagel > > > > the " sp.lockid " should be " sp2.lockid " > > > > Art S. Kagel > > > AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA > > > Trying again: > > > the " sp.lockid " should be " sph.lockid " > > > Art S. Kagel > > Few more questions: > > 1. What does npdata stand for. I didn't understand npused - npdata in > the above query. I was earlier thinking of something like npused * 2k > page size. NPTOTAL - Number of Pages Total - The total number of pages allocated to the partition. NPUSED - Number of Pages Used - The number of allocated pages that actually have something written on them. NPDATA - Number of Pages of Data - The number of dbspace pages marked as data pages (originating, extension, or remainder) NPUSED-NPDATA - The number of used pages which are NOT data pages - this includes bitmap pages (1 per 4096 total pages) and index pages The number of Index pages in a partition is actually: (NPUSED - (NPDATA + ((NPTOTAL + 4095)/4096))) Your NPUSED * 2 will convert pages to KB IFF pagesize is 2K for the dbspace. In IDS 10.00+ it's better to use the pagesize column from sysptnhdr to convert to KB. For earlier releases you should still use sysshmvals.sh_pagesize rather than hard code '2'. > 2. One more thing I don't understand is this: > For eg: Table A has index I1 on 1 key of 4 bytes and index I2 on > 2 keys of 8bytes, so logically total index size of I1 should be half > of the size of I2. But, when I see the statistics of my database I > don't see that relation. Is there something else because of which it > doesn't compute the way I think it should. Yeah, that's not going to work the way you think it will. The 8 byte index will take up more space than the 4byte index but not twice as much. That's partly because each index entry contains the rowid of each row in addition to the key values. Also non-unique indexes compress multiple rowids with the same non-unique key into a smaller number of leave pages. Finally the leaf pages are not completely full in any index and the slack is not directly related to the number or size of the individual keys. Art S. Kagel
On Sep 5, 7:21 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Sep 5, 12:59 am, mohitanch...@gmail.com wrote: > > > > > > > On Sep 4, 12:22 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a > > > > > > given table but I don't get any results. My indexes are in separate > > > > > > dbspace as of row data: > > > > > > <SNIP> > > > > > > Try this one Mohitan: > > > > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > > > > > database, > > > > > st2.tabname partition, nptotal, npused, > > > > > npdata, (npused - npdata) npindex > > > > > from systabnames st1, systabnames st2, sysptnhdr sph > > > > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum > > > > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable' > > > > > order by 2, 3, 1; > > > > > > To filter for a particular index add a filter on: " and st2.tabname = > > > > > 'myindexname' " > > > > > > Art S. Kagel > > > > > the " sp.lockid " should be " sp2.lockid " > > > > > Art S. Kagel > > > > AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA > > > > Trying again: > > > > the " sp.lockid " should be " sph.lockid " > > > > Art S. Kagel > > > Few more questions: > > > 1. What does npdata stand for. I didn't understand npused - npdata in > > the above query. I was earlier thinking of something like npused * 2k > > page size. > > NPTOTAL - Number of Pages Total - The total number of pages allocated > to the partition. > NPUSED - Number of Pages Used - The number of allocated pages that > actually have something written on them. > NPDATA - Number of Pages of Data - The number of dbspace pages marked > as data pages (originating, extension, or remainder) > NPUSED-NPDATA - The number of used pages which are NOT data pages - > this includes bitmap pages (1 per 4096 total pages) and index pages > > The number of Index pages in a partition is actually: (NPUSED - > (NPDATA + ((NPTOTAL + 4095)/4096))) > > Your NPUSED * 2 will convert pages to KB IFF pagesize is 2K for the > dbspace. In IDS 10.00+ it's better to use the pagesize column from > sysptnhdr to convert to KB. For earlier releases you should still use > sysshmvals.sh_pagesize rather than hard code '2'. > > > 2. One more thing I don't understand is this: > > For eg: Table A has index I1 on 1 key of 4 bytes and index I2 on > > 2 keys of 8bytes, so logically total index size of I1 should be half > > of the size of I2. But, when I see the statistics of my database I > > don't see that relation. Is there something else because of which it > > doesn't compute the way I think it should. > > Yeah, that's not going to work the way you think it will. The 8 byte > index will take up more space than the 4byte index but not twice as > much. That's partly because each index entry contains the rowid of > each row in addition to the key values. Also non-unique indexes > compress multiple rowids with the same non-unique key into a smaller > number of leave pages. Finally the leaf pages are not completely full > in any index and the slack is not directly related to the number or > size of the individual keys. > > Art S. Kagel- Hide quoted text - > > - Show quoted text - Is 4096 the limit on number of bitmap pages a partition can have
mohitanchlia@gmail.com wrote: > > Is 4096 the limit on number of bitmap pages a partition can have > Yes, the maximum number of pages per fragment (partition) is 16,775,134 pages. Since one bit map page can map 4096 pages you need 4096 pages to map 16,775,134 pages.