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 47

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Fastest way to replace data in Oracle 5 64
Oracle sql query 7 62
PL/SQL Display based on value 4 29
ODBC in excel 2016 in Windows 10 via VBA 16 85
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

809 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