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.

http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm

  • 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 innocent.

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. 

Source Code

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
DECLARE
 CURSOR all_idx IS 
 select distinct
 a.index_name
 from all_ind_columns a, all_indexes b
 where a.index_name = b.index_name 
 and a.table_name = 'T_AV_EXPLODEDUSERENTITLEMENTS'
 and a.index_name in (
 --'IXN_XUE_ENT_TYPE' --, 
 'PK_T_EXPLODEDUENTS_ID' , 'IXN_XUE_DC' , 'IXN_XUE_MASTER' , 'IXN_XUE_ENT_ID' , 'IXN_XUE_APPLICATION' , 'IXN_XUE_ENTITLED_TYPE'
 );
 
 qu varchar2(200 char);
 
 idx_name varchar2(40);
 lf_count number;
 del_count number;
 ratio number;
 idx_height number;
 
begin
 /*Get all INDEXES of interest. */
 open all_idx;
 Loop
 Fetch all_idx Into idx_name;
 Exit When all_idx%NOTFOUND;
 
 dbms_output.put_line('Analyzing index ' || idx_name);
 
 /* Compute statistics for an index. */
 qu := 'ANALYZE INDEX avuser.' || idx_name || ' COMPUTE STATISTICS'; 
 execute immediate qu;
 /* Validate structure for an index. */
 qu := 'ANALYZE INDEX avuser.' || idx_name || ' VALIDATE STRUCTURE';
 execute immediate qu;
 /* Get the validation results for an index. */
 qu := 'select height, 
 lf_rows, 
 del_lf_rows, 
 round((del_lf_rows/lf_rows)*100,2) as ratio 
 from index_stats 
 where name = ''' || idx_name || '''';
 /* Store validation results in temp variables. */
 execute immediate qu into idx_height, lf_count, del_count, ratio;
 /* Print validation results for an index on the console. */
 dbms_output.put_line('Index: ' || idx_name || ' | Height: ' || idx_height || ' | Leaf Count: ' || lf_count || ' | Del Leaf Count: ' || del_count || ' | Ratio: ' || ratio);
 
 if ( idx_height > 3 OR (ratio >=19)) then
 qu := 'alter index ' || idx_name || ' rebuild online';
 execute immediate qu;
 dbms_output.put_line('Index ' || idx_name || ' re-built.');
 end if;
 End Loop;
 /* Close the cursor for indexes. */
 close all_idx;
end;