Re: Deframenting a table too big for ALTER INDEX

Mark D. Stock wrote: > > Richard Thomas wrote: > > > > Jacob Salomon wrote: > > > > > Q-1: How do I alter index to cluster for a primary key constraint? [SNIP] > > > Q-2: If the table is huge, the work involved in executing an ALTER INDEX [SNIP] > > 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 [SNIP] > 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. I'll contribute my dbcopy.ec utility. I've run up to 40 copies of it concurrently copying subsets of a table from one place to another. It is very fast (tends to be as fast as INSERT INTO ... SELECT ... for a single copy within the same server) and (which is not relevant here) can copy between databases and servers even with different logging modes! Jake you might try that instead of unloading/reloading. Art S. Kagel