RE: Detaching Indexes

When You create detached indexes, please, create one of them as 'cluster'. This will release all unused extents. For efficiency, first drop all existing indexes and create cluster index as the first index in the list. Also, 'set PDQRIORITY=100' should dramatically improve performance of that operation. Best regards, Alexey Sonkin -----Original Message----- From: Michael Hoffman [mailto:mrh@panix.com] Sent: Sunday, February 08, 2004 5:08 PM To: informix-list@iiug.org Subject: Re: Detaching Indexes 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. : > : > sending to informix-list