Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:

Hi Hemant,

How I dislike being immortalised ;)

The note basically quoted me word for word on my feedback and that's fine, it's certainly an improvement on what was previously suggested (and yes, Oracle asked for my permission).

A point I would add though is that the whole subject of how Oracle indexes function and the various cases when one should or should not rebuild indexes is not black and white and is not easily covered in a couple of paragraphs. There are always exceptions and oddities, the key is determining when these scenarios arrive and taking the appropriate action. Many books/articles emphasise the need to rebuild generally and often, I'm suggesting the emphasis should be far more considered and "practical".

If anyone reading the note now questions the rebuild generally and often approach, then my comments serve their intentions.

Cheers

Richard

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, October 24, 2003 1:19 AM

>
> Yes. However, every time he has replied to me, he has been confident that
he
> IS right.
>
> Mind you, Richard, you are "immortalised" now !
> Hemant
>
> At 05:04 PM 22-10-03 -0800, you wrote:
> >So now the blame rests solely on Richard for any material in the note
that's
> >wrong. :)
> >
> >Check the latest update:
>
>http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data
b
> >ase_id=NOT&p_id=182699.1
> >
> >Pete
> >"Controlling developers is like herding cats."
> >Kevin Loney, Oracle DBA Handbook
> >"Oh no, it's not. It's much harder than that!"
> >Bruce Pihlamae, long-term Oracle DBA
> >
> >
> >
> >-----Original Message-----
> >Millsap
> >Sent: Wednesday, October 22, 2003 2:35 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Oops, I didn't see that part. Thanks for the catch, Hemant.
> >
> >
> >Cary Millsap
> >Hotsos Enterprises, Ltd.
> >http://www.hotsos.com
> >
> >Upcoming events:
> >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
> >- SQL Optimization 101: 12/8-12 Dallas
> >- Hotsos Symposium 2004: March 7-10 Dallas
> >- Visit www.hotsos.com for schedule details...
> >
> >
> >-----Original Message-----
> >Hemant K Chitale
> >Sent: Tuesday, October 21, 2003 10:15 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Unfortunately, the lines
> >" Unoccupied space on indexes 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 intensive value change should be rebuilt periodically,
since
> > they become naturally fragmentated. "
> >are still visible in Note 182699.1
> >
> >Hemant
> >
> >At 08:29 AM 20-10-03 -0800, you wrote:
> > >Fyi, Oracle updated note 182699.1 last Friday. The inaccurate
> >statements
> > >about "index fragmentation" have been removed.
> > >
> > >
> > >Cary Millsap
> > >Hotsos Enterprises, Ltd.
> > >http://www.hotsos.com
> > >
> > >Upcoming events:
> > >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
> > >- SQL Optimization 101: 12/8-12 Dallas
> > >- Hotsos Symposium 2004: March 7-10 Dallas
> > >- Visit www.hotsos.com for schedule details...
> > >
> > >
> > >-----Original Message-----
> > >Richard Foote
> > >Sent: Friday, October 17, 2003 6:29 AM
> > >To: Multiple recipients of list ORACLE-L
> > >Separate
> > >
> > >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: richard.foote_at_bigpond.com
> > >
> > >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: ListGuru_at_fatcity.com (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: Cary Millsap
> > > INET: cary.millsap_at_hotsos.com
> > >
> > >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: ListGuru_at_fatcity.com (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: hkchital_at_singnet.com.sg
> >
> >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: ListGuru_at_fatcity.com (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: Cary Millsap
> > INET: cary.millsap_at_hotsos.com
> >
> >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: ListGuru_at_fatcity.com (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: Pete Sharman
> > INET: peter.sharman_at_oracle.com
> >
> >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: ListGuru_at_fatcity.com (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: hkchital_at_singnet.com.sg
>
> 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: ListGuru_at_fatcity.com (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: Richard Foote
  INET: richard.foote_at_bigpond.com

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: ListGuru_at_fatcity.com (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).