-
Rebuild oracle indexes
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.
Chance favours the prepared mind.
vijay_3103@yahoo.com
-
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
David Knight
OCP DBA 8i, 9i, 10g
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|