Deframenting a table too big for ALTER INDEX

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..)] 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) | +------------------------------------------------------------+