RE: Deframenting a table too big for ALTER INDEX

Jacob Salomon wrote: > Warning: Verbose (so what else is new with me? ;-) > > Hi Family. (Yeah, I know.. I should write more often..) > > I have a recurring problem. > > Some enormous tables get fragmented (or should I say fractured?) no > matter what I do. The most obvious way to defrag such a table is to > pick an index - like the primmary key column - and give the command > "alter index yada to cluster". > > Quick problem: If the index was created as part of a primary (or even > foreign) key constraint, the first character of the index name is = blank. > This poses: > Q-1: How do I alter index to cluster for a primary key constraint? > > Yes, I know there are tricks like creating (& naming) the index first, > then altering the table to create a primary key constraint. But this is > not the way constraints are built most of the time. > > The main question relates to the practical issue: > > Q-2: If the table is huge, the work involved in executing an ALTER = INDEX > command - copying the data to more compact pages - can run afoul of > LTXHWM. Most sites don't want to devote gigabytes to the logs, yet many > have gigabyte tables. Obviously, the ALTER INDEX TO CLUSTER can't be > done here. > > I came up with an algorithm that could be made into a script. I'll = tear > it apart shortly. > > 1. UPDATE STATISTICS FOR TABLE yadatab -- So that npused is accurate > 2. dbschema for yadatab to produce an SQL file. > 3. Use awk/sed to enter 2 or 3 edits on yadatab.sql: > a. Change the table name to yadaspare (or something besides = original) > b. Change EXTENT SIZE to npused from above. > c. (Optional for now) Change NEXT SIZE to suit your tastes. > 4. Unload yadatab to a .UNL file > 5. Load yadaspare with the data from yadatab.UNL, but in parts. This > might be accomplished by: > a. Splitting the UNL file and loading each one in a separate LOAD > command, hence a separate trnasaction. > OR > b. Generating a simple DBLOAD command file and using dbload to = insert > the data into yadaspare at 1000 lines per transaction. > 6. Drop table yadatab. > 7. RENAME table yadaspare to yadatab > > I was about to write a shell script to do all of the above - invoking > dbaccess, sed, and awk. (Hey! It's even worth learning perl over!). > > Then I remembered some details: > - I need to recreate the indexes. > - I need to recreate any foreign key constraints that touch this table. > This includes FK references from yadatab to other tables and other > tables' FK references to yadatab. Dropping the table loses these = all! > - Triggers that reference yadatab will be lost when I drop yadatab. > - Any views that reference yadatab will be lost when I drop yadatab. > > Now I have been playing with some awk scripts that divert ALTER TABLE, > CREATE INDEX, and CREATE TRIGGER statements to another file. However, > even using these, I would still lose other tables' FK references to > yadatab. > > [As yet, I have not had to deal with table fragmented across multiple > dbspaces. (Count your blessings..)] > Fear not! In fragmentation lies your answer. Someone recently (I think = Peter Tashkoff but not sure) posted recently that the best way to perform = an on-line de-fracturing, as you put it, is the following: ALTER FRAGMENT ON TABLE yadatab INIT IN [new_DBspace]; I've tried it and it works a treat. It is the fastest way to move the = data from one place to another, and in the process get rid of the = interleaving. In your case where you need to do this to a number of = tables, if you can get a spare DBspace you can perform this task in = sequence, moving your tables around like one of those sliding = numbered-tile puzzles (although I doubt the engine will say "Ta Da" to = you the way the Macintosh Puzzle does when you complete it successfully = ;-) RET > Has anyone out there ever defragmeted a table without losing = constraints > and views? > -- > -- Jake (DEMENTED: Partially DEfragMENTED) > +------------------------------------------------------------+ > | The expedient performance of a task with excessive concern | > | regarding its duration-to-completion engenders a virtual | > | certainty of diminished benefit therefrom. | > | -- Benjamin Franklin (but he said it in 3 words) | > +------------------------------------------------------------+ > +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+