Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: segment fragmentation

RE: segment fragmentation

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 9 Sep 2004 09:26:47 +1000
Message-ID: <E10A27083F21674ABFAF6CD3D0BAC2F90168EE@calbbsv025.cal.riotinto.org>


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_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/
Received on Wed Sep 08 2004 - 18:22:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US