DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: REBUILD Index on RBO DB!!

  1. #1
    Join Date
    May 2002
    Posts
    163

    REBUILD Index on RBO DB!!

    Is that make any difference to REBUILD the indexes in a RBO database, as RBO never follows the statistics?


    Regards
    Nwcomer
    Student

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Some confusion here I think.

    - one would gather stats with ANALYSE or DBMS_STATS. Stats are not used by the RBO - so you don't want to do that.

    - REBUILDing indexes is almost never required. (Search this forum or "Ask Tom" for opinions.) So you probably don't want to do that either.

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by DaPi:

    - REBUILDing indexes is almost never required. (Search this forum or "Ask Tom" for opinions.) So you probably don't want to do that either.
    Well.......that's debatable, especially in the older versions: Oracle 7/8 , where coalesce is not an option and in situations where we can't change the code.
    I have seen dramatic performance gains on rebuilding indexes in several Oracle 8 databases.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Pardon my Naiveity, what is RBO ?
    Sridhar R Patnam

  5. #5
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Rule Based Optimizer

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  6. #6
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    As far as my understanding goes, we Rebuild Indexes when there have been enuf deletes, inserts or updates on the table that makes the index tree really big.

    So how does "Rule" or "Cost" Option related to Rebuiding Indexes ?
    Sridhar R Patnam

  7. #7
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    To put it in a simplistic fashion:

    When the index is badly fragmented, and is analyzed (ie., stats are available), the cost based optimizer will find it too expensive to use the index and go for a full table scan instead.

    However, when the Rule based optimizer is used, it might be 'hard-coded' to use the index and therefore index would be used, no matter what. If there are large number of deletes/ updates to the indexed colun, might lead to poor performance?

    However, things are not that simple as they seem (or should be). There are a large number of variables interplaying.
    Experts feel that there is no performance gain obtained by index rebuilds........

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by Raminder
    Originally posted by DaPi:



    Well.......that's debatable, especially in the older versions: Oracle 7/8 , where coalesce is not an option and in situations where we can't change the code.
    I have seen dramatic performance gains on rebuilding indexes in several Oracle 8 databases.
    Raminder,
    So, thus this means that earlier versions of Oracle at least Oracle8 down, the rebuilding of indexes was advisable and in 9i version and up especially if the tablespace was locally managed, there is no need to rebuild index. And for those version in between oracle8 and oracle817(this also features local management of tbs) depending on how the tbs was created, we used to gather statistics first before we consider rebuilding of indexes?

    Because I for one benefits the rebuilding of index before.
    many thanks.

  9. #9
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    redyp,

    I do not know if rebuild indexes is (or was) 'advisable' anytime, but in 9i, you have the option to coalesce indexes and many DBAs prefer that over rebuild.
    As per Tom Kyte and other Oracle Gurus, rebuilding indexes is a waste of time. However, I feel it depends upon the application and in an OLTP environment, with large number of deletes/ updates on the indexed column, you might want to pick indexes that require rebuild based on deleted leaves.
    I have'nt had much of a chance to check what happens in our application on 9i without a rebuild over a long time (we have migrated sites to 9i in the last 6-7 months), but I know for sure that the same application performed much better after index rebuild on our oracle 8 sites. Particularly, I also noticed ORA-0600 errors while analyzing indexes in Oracle 8 when they were fragmented.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ====
    - REBUILDing indexes is almost never required. (Search this forum or "Ask Tom" for opinions.) So you probably don't want to do that either.
    ===

    I disagree with Tom when it comes for rebuilding indexes.
    He suggested coalesce in stead of rebuilding indexes.
    Many times when split block occurs, the index block gets splitted either 50/50 or 99/1 depending upon the incoming key value. So oracle adds another block in to the extents and old leaf block maintains a pointer to the new block. During the range scan this pointer mechanism helps oracle to traverse to the adjacent block. But physically this block need not necessarily be adjacent to the prev block in the disk. It could be anywhere in the disk.
    Second, the empty blocks due to large number of deletion in the index are still maintained in the index tree stucture.

    That is why in a large DSS/DW system, I usually drop and create indexes during the maintenance window to get best performance from the index.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width