Re: alter index

Graham mitchell wrote: > A REALY fast way of doing a table defrag without > an unload is this. > You must have some spare dbspace ( add a cooked chunk > if you want and drop it later ) > eg table address is fragmented with 2 indexes > 1. Create table address_x with same structure > 2. insert into address_x select * from address > ( You may want to turn logging off or not ) > 3. Drop indexe on address > 4. create indexes on address_x > 5. rename table adress_x to address > Unloading tables is SLOWWWWWW. > This is much faster AND safer as you can keep the original table > for a while if you want Even faster is just: ALTER TABLE address NEXT SIZE <significant fraction of size of table); ALTER FRAGMENT ON TABLE address INIT IN same_or_other_dbspace; The intelligent ALTER FRAGMENT code does minimal page copies and does not REQUIRE enough free space for a second copy of the table only enough for a single new extent as it works. With extent compression this can usually to a great job of defragging a table very quickly. You may still get one small extent the size of the table's initial extent but there will be at most one other extent per chunk required to hold the table. You can keep one extra dbspace just for such reorgs and shift all of the tables in each dbspace one dbspace over periodically. Art S. Kagel