Rebuild oracle indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Rebuild oracle indexes

  1. #1
    Join Date
    Jul 2002
    Posts
    11

    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

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width