RE: segment fragmentation

  • From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon@xxxxxxxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 9 Sep 2004 09:26:47 +1000

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/

Other related posts: