Space Allocation for Indexes

Version IDS 10: I run below query to get free pages in index tblspace for individual indexes: select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname database, st2.tabname partition, nptotal, npused, npdata, (npused - npdata) npindex , nptotal-npused freepages from systabnames st1, systabnames st2, sysptnhdr sph where st1.partnum = sph.lockid and st2.partnum = sph.partnum and st1.dbsname = 'dbname' and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' group by 1,2,3,4,5,6,7,8 order by 2, 3, 1; But what I am seeing is that nptotal and npused pages are same. What I don't understand is that this index that I am looking at grows everyday. Question is that if free pages is 0 and index grows everyday then how and where it's getting the space from. I consistently see same number of npused and nptotal pages. For example if npused is 100 and nptotal is 100, I see the same number even if I run this query after a week. But I know for sure that 1000s of rows have been added in between. This index is a serial value and is unique.

On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: > Version IDS 10: > > I run below query to get free pages in index tblspace for individual > indexes: > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > database, st2.tabname partition, nptotal, npused, > npdata, (npused - npdata) npindex , nptotal-npused freepages > from systabnames st1, systabnames st2, sysptnhdr sph > where st1.partnum = sph.lockid and st2.partnum = sph.partnum > and st1.dbsname = 'dbname' > and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' > group by 1,2,3,4,5,6,7,8 > order by 2, 3, 1; > > But what I am seeing is that nptotal and npused pages are same. What I > don't understand is that this index that I am looking at grows > everyday. Question is that if free pages is 0 and index grows everyday > then how and where it's getting the space from. I consistently see > same number of npused and nptotal pages. For example if npused is 100 > and nptotal is 100, I see the same number even if I run this query > after a week. But I know for sure that 1000s of rows have been added > in between. This index is a serial value and is unique. Are rows also being deleted, if not currently, then in the past? For example, I created a small table with a detached index. I inserted 1000 rows. This set my nptotal to 10 and npused to 8. I then deleted every row in my table, made sure the btscanner cleaned my index, and again checked npused and nptotal. While obviously nptotal shouldn't change, npused also didn't change. However, oncheck -pT for that index partition now showed 8 free pages (1 was the bit map page and then there is still 1 page for the empty root node of the index). So my guess is you are deleting rows in your table, but npused doesn't go down. So nptotal - npused is not an accurate way to check for free pages in a detached index. I believe the product use to decrement npused if pages got totally free, however I think there were some bugs with that, and I think now npused doesn't get decremented anymore. It just relies on the bitmap pages to find totally "free/empty" pages. So nptotal - npused is really only an accurate count of pages that have been allocated to the table's extent list, but haven't ever been used yet. Once a page gets used, and then all the data on it deleted (either row data in tables or key values in indices) the only way to tell if it's "free" is to look at the bitmap page. Jacques

On Feb 7, 9:46 am, jpren...@yahoo.com wrote: > On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: > > > > > > > Version IDS 10: > > > I run below query to get free pages in index tblspace for individual > > indexes: > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > > database, st2.tabname partition, nptotal, npused, > >        npdata, (npused - npdata) npindex , nptotal-npused freepages > > from systabnames st1, systabnames st2, sysptnhdr sph > > where st1.partnum = sph.lockid and st2.partnum = sph.partnum > >     and st1.dbsname = 'dbname' > >     and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' > >     group by 1,2,3,4,5,6,7,8 > > order by 2, 3, 1; > > > But what I am seeing is that nptotal and npused pages are same. What I > > don't understand is that this index that I am looking at grows > > everyday. Question is that if free pages is 0 and index grows everyday > > then how and where it's getting the space from. I consistently see > > same number of npused and nptotal pages. For example if npused is 100 > > and nptotal is 100, I see the same number even if I run this query > > after a week. But I know for sure that 1000s of rows have been added > > in between. This index is a serial value and is unique. > > Are rows also being deleted, if not currently, then in the past?  For > example, I created a small table with a detached index.  I inserted > 1000 rows.  This set my nptotal to 10 and npused to 8.  I then deleted > every row in my table, made sure the btscanner cleaned my index, and > again checked npused and nptotal.  While obviously nptotal shouldn't > change, npused also didn't change.  However, oncheck -pT for that > index partition now showed 8 free pages (1 was the bit map page and > then there is still 1 page for the empty root node of the index).  So > my guess is you are deleting rows in your table, but npused doesn't go > down.  So nptotal - npused is not an accurate way to check for free > pages in a detached index. > > I believe the product use to decrement npused if pages got totally > free, however I think there were some bugs with that, and I think now > npused doesn't get decremented anymore.  It just relies on the bitmap > pages to find totally "free/empty" pages.  So nptotal - npused is > really only an accurate count of pages that have been allocated to the > table's extent list, but haven't ever been used yet.  Once a page gets > used, and then all the data on it deleted (either row data in tables > or key values in indices) the only way to tell if it's "free" is to > look at the bitmap page. > > Jacques- Hide quoted text - > > - Show quoted text - That's part of the puzzle. These rows are never deleted because it's supposed to hold historic data.

On Feb 7, 10:16 am, mohitanch...@gmail.com wrote: > On Feb 7, 9:46 am, jpren...@yahoo.com wrote: > > > > > > > On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: > > > > Version IDS 10: > > > > I run below query to get free pages in index tblspace for individual > > > indexes: > > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > > > database, st2.tabname partition, nptotal, npused, > > >        npdata, (npused - npdata) npindex , nptotal-npused freepages > > > from systabnames st1, systabnames st2, sysptnhdr sph > > > where st1.partnum = sph.lockid and st2.partnum = sph.partnum > > >     and st1.dbsname = 'dbname' > > >     and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' > > >     group by 1,2,3,4,5,6,7,8 > > > order by 2, 3, 1; > > > > But what I am seeing is that nptotal and npused pages are same. What I > > > don't understand is that this index that I am looking at grows > > > everyday. Question is that if free pages is 0 and index grows everyday > > > then how and where it's getting the space from. I consistently see > > > same number of npused and nptotal pages. For example if npused is 100 > > > and nptotal is 100, I see the same number even if I run this query > > > after a week. But I know for sure that 1000s of rows have been added > > > in between. This index is a serial value and is unique. > > > Are rows also being deleted, if not currently, then in the past?  For > > example, I created a small table with a detached index.  I inserted > > 1000 rows.  This set my nptotal to 10 and npused to 8.  I then deleted > > every row in my table, made sure the btscanner cleaned my index, and > > again checked npused and nptotal.  While obviously nptotal shouldn't > > change, npused also didn't change.  However, oncheck -pT for that > > index partition now showed 8 free pages (1 was the bit map page and > > then there is still 1 page for the empty root node of the index).  So > > my guess is you are deleting rows in your table, but npused doesn't go > > down.  So nptotal - npused is not an accurate way to check for free > > pages in a detached index. > > > I believe the product use to decrement npused if pages got totally > > free, however I think there were some bugs with that, and I think now > > npused doesn't get decremented anymore.  It just relies on the bitmap > > pages to find totally "free/empty" pages.  So nptotal - npused is > > really only an accurate count of pages that have been allocated to the > > table's extent list, but haven't ever been used yet.  Once a page gets > > used, and then all the data on it deleted (either row data in tables > > or key values in indices) the only way to tell if it's "free" is to > > look at the bitmap page. > > > Jacques- Hide quoted text - > > > - Show quoted text - > > That's part of the puzzle. These rows are never deleted because it's > supposed to hold historic data.- Hide quoted text - > > - Show quoted text - I think I found the answer. As you mentioned, even for Index pages I need to look at bitmap page. So I am running a query now that takes all the partitions in index dbspace and looks for bitmap = 0. This gives me all the free pages in the partition for that index. I'll run it after few days to see if the unused page count is decreasing. It should decrease as we add more rows.