Detaching Indexes

Hi All, IDS 7.31.UD6 AIX 4.3 I recently began experimenting with detaching indexes. I was under the impression that moving the indexes to a different dbspace would decrease the space used in the main data dbspace. However, this is what I found: (4K Pgs) TableData Space Index Space --------------------------------------------------- Before 392521 Avail 283992 Avail After 392497 Avail 141299 Avail As you can see, detaching the indexes used 142693 pgs (570 MB), but only released 25 Data pages (100 KB). If the index pages were interleaved with the data, shouldn't I get the same 500 MBs returned in the Tabledata space? Thanks, Michael Hoffman P.s. -- Still awaiting any help on why detaching the indexes seems to be causing "dbschema" to core dump with a Segmentation Fault.

"Michael Hoffman" <mrh@panix.com> wrote in message news:c01n4g$pbn$1@reader2.panix.com... > Hi All, > > IDS 7.31.UD6 > AIX 4.3 > > I recently began experimenting with detaching indexes. I was under > the impression that moving the indexes to a different dbspace would decrease > the space used in the main data dbspace. > > However, this is what I found: > > (4K Pgs) TableData Space Index Space > --------------------------------------------------- > Before 392521 Avail 283992 Avail > After 392497 Avail 141299 Avail How are you measuring the space used and available?

Michael Hoffman <mrh@panix.com> wrote in message news:<c01n4g$pbn$1@reader2.panix.com>... > Hi All, > > IDS 7.31.UD6 > AIX 4.3 > > I recently began experimenting with detaching indexes. I was under > the impression that moving the indexes to a different dbspace would decrease > the space used in the main data dbspace. <snip> Did you drop and recreate the tables after the indexes were fragged? > Thanks, > Michael Hoffman > > P.s. -- Still awaiting any help on why detaching the indexes seems to be > causing "dbschema" to core dump with a Segmentation Fault. Never caused us a problem on HPUX, IDS7.31UC2 , sorry...... Malc_p

If you are checking free space in the dbspaces, then yes, simply detaching the indexes will not automatically release pages to the free pool. The now unused space within the table IS free for the table to use for more data pages (run oncheck -pt or -pT to check), but to release the space back to the common free extent pool you have to reorg the table to release unused pages and compress partial pages. Art S. Kagel Michael Hoffman wrote: > Hi All, > > IDS 7.31.UD6 > AIX 4.3 > > I recently began experimenting with detaching indexes. I was under > the impression that moving the indexes to a different dbspace would decrease > the space used in the main data dbspace. > > However, this is what I found: > > (4K Pgs) TableData Space Index Space > --------------------------------------------------- > Before 392521 Avail 283992 Avail > After 392497 Avail 141299 Avail > > As you can see, detaching the indexes used 142693 pgs (570 MB), but only > released 25 Data pages (100 KB). If the index pages were interleaved with > the data, shouldn't I get the same 500 MBs returned in the Tabledata space? > > Thanks, > Michael Hoffman > > P.s. -- Still awaiting any help on why detaching the indexes seems to be > causing "dbschema" to core dump with a Segmentation Fault. > >

Neil, Sorry for not explaining better: the numbers are sums of the output from 'onstat -d' for the chunks comprising the respective dbspaces. Michael In <c02acq$1215fb$1@ID-162943.news.uni-berlin.de> article, Neil Truby mentioned that: : "Michael Hoffman" <mrh@panix.com> wrote in message : news:c01n4g$pbn$1@reader2.panix.com... : > Hi All, : > : > IDS 7.31.UD6 : > AIX 4.3 : > : > I recently began experimenting with detaching indexes. I was : under : > the impression that moving the indexes to a different dbspace would : decrease : > the space used in the main data dbspace. : > : > However, this is what I found: : > : > (4K Pgs) TableData Space Index Space : > --------------------------------------------------- : > Before 392521 Avail 283992 Avail : > After 392497 Avail 141299 Avail : How are you measuring the space used and available?

"Michael Hoffman" <mrh@panix.com> wrote in message news:c06b7n$8ra$1@reader2.panix.com... > Neil, > Sorry for not explaining better: the numbers are sums of the output > from 'onstat -d' for the chunks comprising the respective dbspaces. > > Michael In which case it's for the reason Art Kagel and Malc P gave - you need to drop and re-create the table to free up the space (or use ALTER FRAGMENT .. INIT IN DBSPACE or some other method of re-organising the table data).

Thanks Art & Malc! I expected that to be the case, so before posting these numbers, I onunloaded & onloaded the database. In the past, we've used this method to reorg the entire database in one fell swoop. In this case, the numbers did not change much at all. I will reorg the 10 largest tables this weekend and post my results next week. Thanks for easing my mind that I wasn't nuts to expect the reclaimed space. My boss was begining to doubt my skills. ;-) Michael Hoffman In <4026A001.9000207@erols.com> article, Art S. Kagel mentioned that: : If you are checking free space in the dbspaces, then yes, simply : detaching the indexes will not automatically release pages to the free : pool. The now unused space within the table IS free for the table to : use for more data pages (run oncheck -pt or -pT to check), but to : release the space back to the common free extent pool you have to : reorg the table to release unused pages and compress partial pages. : Art S. Kagel : Michael Hoffman wrote: : > Hi All, : > : > IDS 7.31.UD6 : > AIX 4.3 : > : > I recently began experimenting with detaching indexes. I was under : > the impression that moving the indexes to a different dbspace would decrease : > the space used in the main data dbspace. : > : > However, this is what I found: : > : > (4K Pgs) TableData Space Index Space : > --------------------------------------------------- : > Before 392521 Avail 283992 Avail : > After 392497 Avail 141299 Avail : > : > As you can see, detaching the indexes used 142693 pgs (570 MB), but only : > released 25 Data pages (100 KB). If the index pages were interleaved with : > the data, shouldn't I get the same 500 MBs returned in the Tabledata space? : > : > Thanks, : > Michael Hoffman : > : > P.s. -- Still awaiting any help on why detaching the indexes seems to be : > causing "dbschema" to core dump with a Segmentation Fault. : > : >