Re: re Rebuilding Indexes in Oracle Apps --

Ah well ! I do not have statistics to present my case. However, it is not my case that Index rebuilds are necessary, only that in the case of some Indexes, rebuilds do make sense.

Last week, I'd just purged about 80% of the rows in a few tables and, of course,
then rebuilt both the tables [to reset the HWM] and indexes. {These were some Alert tables where the application administrator had never setup purging and we had 2 years of alerts}

FND_CONCURRENT_REQUESTS is a case of a table with monotonically increasing values for certain columns [REQUEST_ID and REQUEST_START_DATE].

Although the Note that I referred to in my earlier email [Note 182699.1] has been updated on 17-Oct, it still includes the paragraph " 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. "

Hemant

At 06:44 PM 17-10-03 -0800, you wrote:
>----- 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
>
>
>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).