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