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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > >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: [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). > -- 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).