At 11:34 AM 10/17/2003 -0800, you wrote:
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 Please respond to ORACLE-L
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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jerome Roa 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).