Rebuild oracle indexes

[vijay_3103]

I have three questions on rebuilding oracle table indexes.

1.What affects the table index tree, is it updates/inserts/deletes ?

2.I have a index defined over three columns in a table, and the table gets updated very frequently but those columns indexed are not updated.
Would those updates also affect the index tree..Do i need to rebuild the indexes in sucn cases too...

3.When does rebuilding index tree take more time, is it when the table has more number of records or the table had involved in more number of transactions ?

The core thing i would want to know is when to rebuild indexes and what indexes of a table need to be rebuilt.

[dknight]

These might help:

1. Edit the resulting file validate_index_ims.sql

set heading off
set pagesize 49999
set width 160

spool validate_index_ims.sql

select 'VALIDATE INDEX '||OWNER||'.'||INDEX_NAME||';',
'INSERT INTO MY_INDEX_STATISTICS(SELECT * FROM INDEX_STATS);'
from dba_indexes
where owner <> 'SYS'
and owner <> 'SYSTEM';

spool off

2. Run the edited validate_index_ims.sql

3. Run this script to get an idea about the fragmentation:

select name,
used_space,
del_lf_rows,
lf_rows,
round(del_lf_rows/(lf_rows+0.000000001))*100 "Frag Percent"
from index_stats;

HTH

[slimdave]

Unless you have identified a performance problem that you can reasonably expect will be solved by rebuilding indexes, don't bother. It's usually a waste of time -- try benchmarking application performance before and after a rebuild, and you'll find no difference in 80% of cases. In 19% of cases your performance will be degraded.

[Raminder]

1.What affects the table index tree, is it updates/inserts/deletes ?

Updates: Each update on the indexed column is treated as a delete followed by an insert in the index.

Inserts/Deletes: They can affect performance in a B-tree index if they exceed 10% and the inserted values do not follow normal distribution.

2.I have a index defined over three columns in a table, and the table gets updated very frequently but those columns indexed are not updated.
Would those updates also affect the index tree..Do i need to rebuild the indexes in sucn cases too..?

No. If the updates don't cause lot of records to be migrated.

3.When does rebuilding index tree take more time, is it when the table has more number of records or the table had involved in more number of transactions ?

When the table has more records. But if you are rebuilding the table 'online' and the table is having high transactional activity, you can expect it to take more time rebuilding.