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).
