Re: re Rebuilding Indexes in Oracle Apps --

Hi Hemant,

If you purge 80% of rows from a table without intending to reinsert them anytime soon, then yes, a table reorg would be a recommended step. No arguments from me there ;)

I had lunch with Pete Sharman today and he mentioned that he sent an email to the support person responsible for the note with a copy of my little demo highlighting how deleted row space can be reused. I also received an email form Lex de Haan at Oracle inviting me to provide feedback on how the note can be improved.

So hopefully, a modified note might be available soon. That being the case, one has to give Oracle much credit for trying to put things to the right.

The tide is turning ;)

Cheers

Richard

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

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

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