rebuilding Indexes

Posted on 2007-03-17
Last Modified: 2013-12-18
We are running the below script every week.
We are not rebuilding Indexes every week.
Is it a good Idea to rebuild Indexes every week or periodically.(All the indexes in the database)
We have a OLTP database.
we are  not facing any performance problem yet

Question by:ballioballi
  • 3
  • 2
  • 2
  • +2
LVL 69

Expert Comment

ID: 18741753
reorganizing or rebuilding indexes does only make sense when data is removed very often., or you have a strictly ascending/descending key. Even in this cases, I would recommend to reorganize no more than once a month.

Expert Comment

ID: 18742691
It is best to check first which indexes are required to be rebuild and then rebuild only those indexes. You can use
Analyze index .... validate structure command to  check an index candidate for rebuild. the result of this command will populate index_stats table. In this table, you can query to check the candidate indexes
The criteria of rebuild may be
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
rebuild .......

LVL 48

Expert Comment

ID: 18747319
Rebuilding indexes periodically is very good thing.
After some DML they got unusual big and also possibly
some blocks get logically corrupted (mainly because of
Oracle bugs).
So there are 2 main things:
1. When run the statistics use the option cascade to run the statistics also
over the indexes.
2. 2-3 times yearly rebuild them in case of normal use.
If you expertience difficulties do this often.

There is nothing bad to rebuild the indexes.
Some of them cannot be rebuilded - Index Organized tables and LOB indexes.
So be prepared to get error messages in these case - it is normal.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 35

Expert Comment

by:Mark Geerlings
ID: 18748350
I agree with Qlemo and Fayyaz, that is: you don't regularly need to rebuild indexes in most Oracle OLTP databases.  An exception would be indexes on any tables that are used as "queue" tables.  For example, if you have tables that are used as "staging" or "work" tables that get lots of inserts and deletes every day, indexes on those tables should be rebuilt regularly (maybe nightly or on the weekend).

I have worked with Oracle databases for over 15 years and I don't recall ever seeing an index with logical corruption.  Maybe this happened a time or two and I have forgotten, but this certainly isn't common or frequent in my experience.
LVL 48

Expert Comment

ID: 18748440
I haven't seen corrupted blocks on Windows.
But I have met every 5 months corrupted index
blocks on Linux. I mean Novel linux (SuSE Linux).
So I have prepared a script that rebuilds the
indexes, but do not run it regularly.

Please if you have time pay attention to:

Inside Oracle b-tree indexes

There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing passionate debate about rebuilding of indexes for improving performance.  Some experts claim that periodic rebuilding of Oracle b-tree indexes greatly improves space usage and access speed, while other experts maintain that Oracle indexes should “rarely” be rebuilt.  Interestingly, Oracle reports that the new Oracle10g Automatic Maintenance Tasks (AMT) will automatically detect indexes that are in need of re-building.  Here are the pros and cons of this highly emotional issue:


Arguments for Index Rebuilding – Many Oracle shops schedule periodic index rebuilding, and report measurable speed improvements after they rebuild their Oracle b-tree indexes.  In an OracleWorld 2003 presentation titled Oracle Database 10 g: The Self-Managing Database by Sushil Kumar of Oracle Corporation, Kumar states that the Automatic Maintenance Tasks (AMT) Oracle10g feature will automatically detect and rebuild sub-optimal indexes.  “AWR provides the Oracle Database 10g a very good 'knowledge' of how it is being used. By analyzing the information stored in AWR, the database can identify the need of performing routine maintenance tasks, such as optimizer statistics refresh, rebuilding indexes, etc. The Automated Maintenance Tasks infrastructure enables the Oracle Database to automatically perform those operations.”

Arguments against Index Rebuilding – Some Oracle in-house experts maintain that Oracle indexes are super-efficient at space re-use and access speed and that a b-tree index rarely needs rebuilding.  They claim that a reduction in Logical I/O (LIO) should be measurable, and if there were any benefit to index rebuilding, someone would have come up with “provable” rules.

So who is right?  I suspect that they both are correct.  There is a huge body of evidence that index rebuilding makes the end-users report faster response time, and I have to wonder if this is only a Placebo Effect, with no scientific basis.  Some experts suspect a Placebo Effect may be at work here, and the end-users, knowing that they have new index trees, report a performance gain when none exists.

Because of their extreme flexibility, Oracle b-tree indexes are quite complex, and to really gather scientific evidence we must examine all of the index metrics.  Getting a meaningful measure of the benefit of an index rebuild in a production environment is very difficult because the system is under heavy load and usage patterns change constantly.  Plus, many IT managers require periodic index re-building because it is a low-risk job and it curries favor from the end-user community.  


Author Comment

ID: 18761102
I agree with all of you .. experts
I have a question especially for Favvaz,

I have like 35,000 tables and lot many indexes.
How can I check which index has to be rebuild by Analyze index .... validate structure command . This looks  impossible for me to put the individual Indexes......

LVL 48

Expert Comment

ID: 18762339
These entries are in index_stats:

SQL> descr index_stats;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 HEIGHT                                             NUMBER
 BLOCKS                                             NUMBER
 NAME                                               VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 LF_ROWS                                            NUMBER
 LF_BLKS                                            NUMBER
 LF_ROWS_LEN                                        NUMBER
 LF_BLK_LEN                                         NUMBER
 BR_ROWS                                            NUMBER
 BR_BLKS                                            NUMBER
 BR_ROWS_LEN                                        NUMBER
 BR_BLK_LEN                                         NUMBER
 DEL_LF_ROWS                                        NUMBER
 DEL_LF_ROWS_LEN                                    NUMBER
 DISTINCT_KEYS                                      NUMBER
 MOST_REPEATED_KEY                                  NUMBER
 BTREE_SPACE                                        NUMBER
 USED_SPACE                                         NUMBER
 PCT_USED                                           NUMBER
 ROWS_PER_KEY                                       NUMBER
 BLKS_GETS_PER_ACCESS                               NUMBER
 PRE_ROWS                                           NUMBER
 PRE_ROWS_LEN                                       NUMBER
 OPT_CMPR_COUNT                                     NUMBER
 OPT_CMPR_PCTSAVE                                   NUMBER

Accepted Solution

Fayyaz earned 125 total points
ID: 18762357
Here is the PL/SQL code

 c_name        INTEGER;
 ignore        INTEGER;
 height        index_stats.height%TYPE := 0;
 lf_rows       index_stats.lf_rows%TYPE := 0;
 del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
 distinct_keys index_stats.distinct_keys%TYPE := 0;
 cursor c_indx is
  select owner, table_name, index_name
  from dba_indexes
  where owner like upper('&schema')
    and owner not in ('SYS','SYSTEM');
 dbms_output.enable (1000000);
 dbms_output.put_line ('Owner          Index Name                              % Deleted Entries Blevel Distinctiveness');
 dbms_output.put_line ('--------------- -------------------------------------- ----------------- ------ ---------------');
 for r_indx in c_indx loop
  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
  ignore := DBMS_SQL.EXECUTE(c_name);
  select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
         decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
         into height, lf_rows, del_lf_rows, distinct_keys
  from index_stats;
  if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
                          lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
  end if;
 end loop;



Author Comment

ID: 18910701
I run this and it is Great.
Can I just rebuild this Indexes. I am getting like 250  out of a big list.

Once again it is just Great.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Retreiving column names in Windows but not in Unix 11 75
use lov values 2 71
Fill Date time Field 12 29
making a message body variable from an oracle select statement 4 34
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question