De-fragmenting a table

IDS v7.24 on HP-UX 10.20. I've got a 13GByte, 12 million row table in 175 extents and I need to defrag it. Urgently! I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it still hadn't finished. this was with logging temporarily turned off for the database. I only have a 12 hour downtime wondow. Any other ideas? I'm testing a High perfoamnce load but, although therows take only 20 minutes or so, the indexes are going to be damned slow. thanks Neil

Hi! I have not have a problem like this yet, but I think someone mentioned here before, that "alter index to cluster" will actually rebuild table - if you have that much contigious space. You may want to search the archive for that. HTH Michael Neil Truby wrote: > IDS v7.24 on HP-UX 10.20. > > I've got a 13GByte, 12 million row table in 175 extents and I need to defrag > it. Urgently! > > I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it > still hadn't finished. this was with logging temporarily turned off for the > database. I only have a 12 hour downtime wondow. > > Any other ideas? I'm testing a High perfoamnce load but, although therows > take only 20 minutes or so, the indexes are going to be damned slow. > > thanks > Neil

Thanks Michael. I'm pretty sure that ALTER .. CLIUSTER will re-build the table in the same way as ALTER ... FRAGMENT, so I don;t think I'll be any further forward. Michael Krzepkowski wrote in message <3924517F.3699645C@sqlcanada.com>... >Hi! > >I have not have a problem like this yet, but I think someone mentioned here >before, that "alter index to cluster" will actually rebuild table - if you have >that >much contigious space. You may want to search the archive for that. > >HTH > >Michael > > >Neil Truby wrote: > >> IDS v7.24 on HP-UX 10.20. >> >> I've got a 13GByte, 12 million row table in 175 extents and I need to defrag >> it. Urgently! >> >> I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it >> still hadn't finished. this was with logging temporarily turned off for the >> database. I only have a 12 hour downtime wondow. >> >> Any other ideas? I'm testing a High perfoamnce load but, although therows >> take only 20 minutes or so, the indexes are going to be damned slow. >> >> thanks >> Neil >

You could consider the following : 1. INIT into a new dbspace on different disk(s). This will allow efficient reads and writes during the ALTER FRAGMENT. 2. Consider disabling indexes before the INIT. Then enable them one at a time in descending order of priority. This will, in effect, break down a single monolithic task into multiple tasks. This can be particularly useful if there are some indexes that you can, temporarily, do without (e.g one set of Business Analysts can do without querying). 3. Detach indexes into their own disk to speed up the index build process - which mean, drop indexes->alter frag->rebuild index in their own dbspace. 4. PSORT_NPROCS, PDQPRIORITY settings for the Index builds. 5. Unique Indexes/Primary Key (A bit of heresy). Save (a lot of!) time by changing them to non-unique during this run. When you next get a window, revert them back to unique. 6. Grab this opportunity to suggest archiving of "old" data :-) [getting desperate now!] Rudy Neil Truby wrote: > IDS v7.24 on HP-UX 10.20. > > I've got a 13GByte, 12 million row table in 175 extents and I need to defrag > it. Urgently! > > I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it > still hadn't finished. this was with logging temporarily turned off for the > database. I only have a 12 hour downtime wondow. > > Any other ideas? I'm testing a High perfoamnce load but, although therows > take only 20 minutes or so, the indexes are going to be damned slow. > > thanks > Neil

Neil Truby wrote: > > IDS v7.24 on HP-UX 10.20. > > I've got a 13GByte, 12 million row table in 175 extents and I need to defrag > it. Urgently! Ouch!! > > I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it > still hadn't finished. this was with logging temporarily turned off for the > database. I only have a 12 hour downtime wondow. > Hmmmmmm. > Any other ideas? I'm testing a High perfoamnce load but, although therows > take only 20 minutes or so, the indexes are going to be damned slow. > HPL is great. I've used it to handle various large tables and it works wonderfully, but I only use it for tables unload / reloads. I still rebuild the indices AFTER the load is complete. Are your indices detached? (I believe that the default Lawson table structure detaches the indices.) One thing I've found out is that creating a detached index takes quite a bit longer than creating an attached index. Somehow Informix isn't taking advantage of multiple sort threads; all I see in onstat -g ses are exchange threads. Enabling PDQ took a 4 1/2 hour index build down to 45 minutes, which was comparable to the 'old' way of creating attached indices. BTW, Which Lawson table is it? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */