rebuilding Indexes

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
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 .......

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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mark GeerlingsDatabase AdministratorCommented:
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.
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.  

ballioballiAuthor Commented:
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......

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
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;



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ballioballiAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.