Also, something others may know but I ran into recently. When you move a table, posts normally point out that indexes will need = to be rebuilt (as the post below does). You also need to recreate statistics (by your method of preference - = dbms_stats gathering, analyze or dbms_stats setting) on the table and = indexes. HTH, Bruce Reardon -----Original Message----- From: Wolfgang Breitling Sent: Thursday, 9 September 2004 9:20 AM Subject: RE: segment fragmentation Alter table move is available as of Oracle 8 and is definitely faster = than=20 and preferable to an export/import, provided you determine the need to = do=20 that. If that deleted space is going to be reused by inserts it may not = be=20 worth to reorg the table, unless the reorg will help prevent the = clustering=20 factor of important indexes to worsen. At 03:06 PM 9/8/2004, Bobak, Mark wrote: >David, > >As Ryan mentioned, LMTs prevent fragmentation, if you have uniform =3D >extents, or at least mitigate it, if you have autoallocate extents. = =3D >However, if you delete a significant amount of data from a table, then, = =3D >there could be some impact due to high-water mark. Consider, however, = =3D >when the high-water mark is referenced. For a table, only on full = table =3D >scan. For indexes on the table, only on an index fast full scan. So, = =3D >if your queries don't utilize those access paths, then you really don't = =3D >need to concern yourself w/ HWM. > >You don't mention what version you're on. If HWM is an issue, you = don't =3D >need to resort to export/import, if you're at least at 9i. You can =3D >ALTER TABLE table_name move; to reorganize the table data. Note that = =3D >when that's done, all indexes will be invalidated and will need to be = =3D >rebuilt. Consider the possibility of NOLOGGING, but make sure you = fully =3D >understand the recovery implications before proceeding there. Also, = I'm =3D >not really a 10g guy, yet, but in 10g, there's a 'shrink' clause to =3D >ALTER TABLE that (from what I've read) does a nice job, maintains the = =3D >object on-line availability and maintains indexes at the same time. = =3D >But, I haven't used that.... Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com __________________________________________________________________ NOTICE =20 This e-mail and any attachments are private and confidential and=20 may contain privileged information. =20 If you are not an authorised recipient, the copying or distribution=20 of this e-mail and any attachments is prohibited and you must not=20 read, print or act in reliance on this e-mail or attachments. =20 This notice should not be removed. __________________________________________________________________ -- To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe To search the archives - //www.freelists.org/archives/oracle-l/