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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, October 18, 2003 4:42 AM

Hi Hermant,

>
> I wonder if "it is not necessary to rebuild indexes" is also a myth.

It might be but I've yet to hear it. It's certainly not something I've ever claimed, unless it's a quote taken out of context (the start and end are missing) which would be unfortunate.

I would re-phrase it as "it is *rarely* necessary to rebuild indexes" and it would be a hell of a lot more accurate than many quotations on this subject. So let's not confuse and cloud the issue.

>
> It IS in some cases necessary

Yes it is but the point I'm trying to make that the "some cases" are relatively *rare*. The "emphasis" as I often hear it is that indexes "usually/always" need to be rebuilt. This is simply incorrect. The Metalink note claims that deleted space is not reused. This is not only incorrect but helps promote the myth that indexes hence need frequent rebuilding. One incorrect claim promotes one incorrect conclusion.

> 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID
> based on a Sequence

As I've previously stated *but* and it's a big BUT only if there are subsequent sparse deletions. No spares deletions, no rebuilds are necessary. What ratio of indexes in Oracle financials actually meet this criteria ? Monotonically increasing *and* sparse deletions.

> or even on date columns which signify when the record is created] if the
> table is also
> purged by the same columns frequently

Similar case to the above. But this implies a specific range of index values being deleted which results in a range of index nodes being emptied. These blocks therefore *can* be reused. If records are subsequently inserted *at the same rate* they are being purged, then again index rebuilds are potentially unnecessary.

> 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

How ?

We covered one case above. Another is that we simply reduce the volume of data within a table (and hence index). How does "time" result inordinately enlarged indexes ? As previously discussed, Oracle is very efficient in the way it reuses space within an index, suggestions that indexes just become unnecessarily enlarged over time are generally false.

>
> Have you administered an Oracle Applications database ?

No, but I have a number of SAP applications and they suffer from the same bad advice that indexes generally require frequently rebuilding. In actual fact, the ratio of indexes that actually benefit from rebuilding is tiny and then it's generally the table that needs rebuilding more so than the indexes directly and then the tiny tiny ratio of indexes that remain generally need coalescing rather than rebuilding.

Indexes that exist in Oracle Applications are not special, they follow the same rules as those indexes in SAP, or in-house applications, etc.

Hemant, take a look at Jonathan Lewis's article "When Should You Rebuild An Index" at www.dbazine.com . In it he concludes "Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system ? The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily".

Amen to that !!

Cheers ;)

Richard

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