Is that make any difference to REBUILD the indexes in a RBO database, as RBO never follows the statistics?
Regards
Printable View
Is that make any difference to REBUILD the indexes in a RBO database, as RBO never follows the statistics?
Regards
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.
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.Quote:
- 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 have seen dramatic performance gains on rebuilding indexes in several Oracle 8 databases.
Pardon my Naiveity, what is RBO ?
Rule Based Optimizer
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 ?
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,Quote:
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.
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.
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.
====
- 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