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: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 08 Sep 2004 17:20:10 -0600
Message-Id: <6.1.0.6.2.20040908171115.02eab058@pop.centrexcc.com>


Alter table move is available as of Oracle 8 and is definitely faster than and preferable to an export/import, provided you determine the need to do that. If that deleted space is going to be reused by inserts it may not be worth to reorg the table, unless the reorg will help prevent the clustering 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 =
>extents, or at least mitigate it, if you have autoallocate extents. =
>However, if you delete a significant amount of data from a table, then, =
>there could be some impact due to high-water mark. Consider, however, =
>when the high-water mark is referenced. For a table, only on full table =
>scan. For indexes on the table, only on an index fast full scan. So, =
>if your queries don't utilize those access paths, then you really don't =
>need to concern yourself w/ HWM.
>
>You don't mention what version you're on. If HWM is an issue, you don't =
>need to resort to export/import, if you're at least at 9i. You can =
>ALTER TABLE table_name move; to reorganize the table data. Note that =
>when that's done, all indexes will be invalidated and will need to be =
>rebuilt. Consider the possibility of NOLOGGING, but make sure you fully =
>understand the recovery implications before proceeding there. Also, I'm =
>not really a 10g guy, yet, but in 10g, there's a 'shrink' clause to =
>ALTER TABLE that (from what I've read) does a nice job, maintains the =
>object on-line availability and maintains indexes at the same time. =
>But, I haven't used that....

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
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:16:13 CDT

Original text of this message

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