Re: Deframenting a table too big for ALTER INDEX

Art and Mark have both recommended using a sectioned "select from .. insert into ..". That's quite silly of me to forget, since I suggested this very schem to a colleague months ago for his need to insert columns on a huge (millions of rows) table. Yes, this would take care of the log problem. However, niether of your methods will restore referential constraints from other tables to the table I'm defragmenting. You know, the ones that would be lost the moment I dropped the original fragmented table. Toward that end, I might consider a variation of the techniques proposed by David, wherein he reproduces the "alter table" commands on the referencing tables. He gets that via a query on sysconstraints and sysreferences. I can use a similar stunt in sysviews - scanning for any views where the text part contains the name of the fragged table - and rebuild the view after rebuilding the table. Thanks all. Art S. Kagel wrote: > > 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 -- -- Jake (Pondering the color of an asphyxiated smurf) +------------------------------------------------------------+ | 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) | +------------------------------------------------------------+