Recently I had to rebuild some indexes, since they met two major conditions under which an index rebuild most likely will improve performance.
According to Burleson, an index rebuild can help under the following conditions.
High index fragmentation: The SQL workload has lots of table DML causing lots of deleted leaf blocks.
High index scan access plans: The SQL workload is rich with index scans (index fast-full scans and index range scans)
The second condition definetely holds valid for my clients database. The application, RSA IGL is used for over 10 years without any archiving or regular house keeping and up to 100 Million entries in several important tables.
Well, it is what it is and I can only preach so much until I take action myself .
Furthermore the source states the following criteria to identify high index fragmentation.
deleted entries represent 20% or more of the current entries.
the index depth is more then 4 levels.
Only indexes that have a high number of deleted leaf blocks and are accessed in these ways will benefit from rebuilding:
- index fast full scan
- index full scan
- index range scan
For index unique scans an index rebuild will not improve performance, since it does not reduce I/O.
The more fragmented an index is, the more read operations will have to be performed in one of the index access types listed above.
Well, makes sense to me, but read this and other articles.
I did not find hard proof that an index rebuild does improve performance online.
However, I think that the pragmatic approach to handle this is to rebuild an index, once the fragmentation is high and you have a lot of range or full scans on the index.
What I do take is that you should not listen to DBAs, which recommend to rebuild an or even all indexes on a regular basis, unless your type of application really requires to.
The source has the following to say regarding this.
Today, a battle is raging between the: academics? who do not believe that indexes should be rebuilt without expensive studies, and the: pragmatists? who rebuild indexes on a schedule because their end-users report faster response times.
To date, none of the world's Oracle experts has determined a reliable rule for index rebuilding, and no expert has proven that index rebuilds "rarely" help.
Enough theory, let's get to it.
DO NOT RUN THIS IN AN ACTIVE SYSTEM! You might cause a dead lock.
Always bring the applciation down / put it into maintenance mode and make sure that you have enough space available.
The currently available index is used as the data source for the index, instead of the table’s being used as the data source.
After the new index has been created, the old index will be dropped.
You should be able to rebuild an index while it is being accessed, but I strongly recommend not to do so since the analysis alone and the rebuild are very resource intense.
Since I do not have SSH access to the PRD databases and Burleson provides a shell script, I wrote a little PL / SQL script that basically does the same job.
Only later I saw that Burleson also provides an PL SQL version of the script.
Anyhow, here is my version.
You will have to activate the DBMS output view in SQL Developer to see the results.