The article states that leaf blocks are not reused, which is indeed incorrect,
and has been for a very long time.
Hemant K Chitale <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 10/17/2003 11:42 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: |
I wonder if "it is not necessary to rebuild indexes" is also a myth.
It IS in some cases necessary
1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID
based on a Sequence
or even on date columns which signify when the record is created] if the
table is also
purged by the same columns frequently
2. Because the disk space used by an Index can be inordinately larged
after a couple of years
and index fast_full_scans are impacted
Have you administered an Oracle Applications database ?
hemant
At 03:29 AM 17-10-03 -0800, you wrote:
>Hi Hemant,
>
>One word perfectly describes the Metalink article you highlighted:
>
>Crap ;)
>
>A nice example of how Oracle Corp is the greatest myth generator of them
>all !! It's all rather sad and embarressing isn't.
>
>Thanks for the headsup. Anyone in a position to get the note removed ?
>
>Cheers
>
>Richard
>
> >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds
>Fragmentated Indexes (8.0->9.0)
> >
> >Index fragmentation occurs when a key value changes, and the index row is
> >deleted from one place (Leaf Block) and inserted into another.
> >
> > Deleted Leaf Rows are not reused. Therefore indexes whose columns are
> >subject to value change must be rebuilt periodically since they become
>naturally fragmentated.
> >
> >An index is considered to be 'fragmentated' when more than 20% of its Leaf
>Rows space is
> >empty because of the implicit deletes caused by indexed columns value
>changes.
> >
> >Fragmentated indexes degrade the performance of index range scan
>operations.
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Richard Foote
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).