Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

How to drop all indexes in a SCHEMA : Oracle 10g

How to drop all indexes in a SCHEMA

–Login as the schema user whose indexes you want to drop

SQL>set pages 10000
SQL>set wrap off
SQL>set heading off
SQL>spool /u2/scripts/idxswx.txt

SQL> select ‘ drop index ‘ || index_name ||’;’ from user_indexes;
drop index IND1;
drop index IND2;
drop index IND3;

SQL> spool off;

— This will spool the file to the set location and now run the Script as the schema user
SQL> conn scott/tiger

SQL>@indx.sql

–in the particular schema in which you want to drop indexes.

A similar script can be used for synonyms especially when you import a schema into a different name than when it was exported.

Myths about Index Rebuilding

Index Re-building is a debatable concept amongst DBA’s. Experts like Ask Tom think  rebuilding indexes doesn’t  create any considerable performance improvement.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112
Testimony to this is a small exercise I did in my production environment. We have a table with over 10 million rows. A full table scan on this takes approximately 15 minutues. the table has 6 b*tress indexes. I rebuild all the indexes online, the result was same. Full table scan took a few seconds less. The result was neglible. For performance tuning you have to carefully consider all symptoms and not blinly rebuild all indexes. The Cost Based Optimizer(CBO) in Oracle takes care of optimizing your queries. Below are the general myths associated with index rebuilding.

1. The vast majority of indexes do not require rebuilding
2. Oracle B-tree indexes can become “unbalanced” and need to
be rebuilt is a myth
3. deleted space in an index is “deadwood” and over time
requires it to be rebuilt is a myth
4. If an index has a height of “X” levels, it’s inefficient
and must be rebuilt is a myth
5. If an index has a poor CF, rebuilding it is a myth
6. To improve performance indexes must be rebuilt on a regular basis is a myth