Re: Fragmentation in Oracle

From: "Jorge A. San Martín" <jsanmart_at_tortuga.its.cl> paraphrase - process deletes a fairly large number of selected rows from table  on routine basis; what should be done about fragmentation? <<

Oracle can reuse space from deleted rows in the table if the pctfree parameter  is set high enough to be effective. The Admin guide provides a formula but  basically set pctused above the default of 40 to around 60. This should allow  you to re-org the table only once or twice a year. or maybe even less if a  persistent data use pattern exists.

Indexes on the table are another matter as Oracle can not reuse index space for  deleted rows unless the exact same key is reused. With 7.3 and up the alter index rebuild command is pretty quick if adequate  free space exists in the tablespace otherwise you will have to drop and  rebuild the indexes that do not repeat the same keys.

You should recalculate your statistics after a full re-org, and after index  rebuilds.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Fri Nov 07 1997 - 00:00:00 CET