Re: Deframenting a table too big for ALTER INDEX

Richard Thomas wrote: > > Jacob Salomon wrote: > > > 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. > > 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 ;-) That's right, however, beware Jacob that this will also be executed in a single transaction, and will give you the same problems with your logs. You may have to move 'bits' of the table around by using FRAGMENT BY EXPRESSION initially, then merging fragments one by one. I haven't tested this, so be careful you don't end up with as many extents as there were fragments. Incidentally, on your manual manoeuvres, why do you unload to disk and back again? Wouldn't it be easier to use: INSERT INTO newtable SELECT * FROM oldtable? Or is this back to problems with logs? In which case use a WHERE clause to split the rows in to manageable blocks. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+