Re: Freeing space after delete

Also be aware that you must have enough space to create a duplicate copy of the table when running the cluster index. It copies the table to a temporary table when ordering the rows and then drops the original and renames the temporary table to the same name as the original. Also, if the table has a large number of insert/delete statements, then the benifits of clustering are soon gone. ---- original message from Malcolm Weallans ---- ++ ++ WARNING. Do not go overboard about ALTER INDEX ... TO CLUSTER Although ++ this is a recommended technique it can have adverse effects. ++ Particularly wrt OnLine. Think what happens when you do it. Before the ++ alter table the index and data might be located quite close to each ++ other, but, if the table is completely rebuilt will this still be the ++ case? ++ It has been known for applications to suddenly lose performance following ++ an ALTER TABLE or ALTER INDEX. ++ ++ > In article Dsx@cix.compulink.co.uk, onlinedbc@cix.compulink.co.uk ++ > ("Malcolm Weallans") writes:>With SE the actions are similar to ++ OnLine. > i.e. if you delete records >froman SE table the corresponding ++ areas are ++ > merely flagged as deleted >andcan be re-used. Hence the file size will ++ > not get smaller. With SE, to >make the file smaller do a table rebuild ++ > of any sort. > The easiest way to do this is with the alter index to ++ cluster statment. ++ > > Karl ++ ++ ++ Malcolm Weallans ++ Online Database Consultancy ++ 2 Arkley Court ++ Maidenhead ++ Berks ++ SL6 2YR ++ Phone 0628-72154 ++ Fax 0628-37463 ++ CIX - onlinedbc ++ -- Jerry M. Denman -- Director of Special Projects Sherwood Systems (a division of Sherwood Mfg Co. Inc.) jerry@sherwood.com ------------------------------------------------------------------------------ The manner in which one endures what must be endured is more important than the thing that must be endured. - DEAN ACHESON