When to Rebuild Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: When to Rebuild Index

  1. #1
    Join Date
    May 2001
    Location
    New Zealand
    Posts
    123

    Thumbs down

    Hi

    Can Anybody please tell when should one rebuild an Index and how often ...

    I would appreciate a document on that or some good material dba support help is perhaps the best so that will be perfect ..

    Thanks .

    Afshin

  2. #2
    Join Date
    Mar 2001
    Posts
    188
    Hi there,

    If your direct-load INSERT modifies most of the data in a table, you can avoid the performance impact of index maintenance by dropping the index before the INSERT and then rebuilding it afterwards.

    Regards
    Thomas

  3. #3
    Join Date
    May 2001
    Location
    New Zealand
    Posts
    123

    Rebuild Index

    Hi

    Thanks for your prompt reply ..
    Thats now what I want . I want How does index fragmentation occur and how should I detect that the index is getting in efficient and how do I rebuild the same ........


    Thanks

    Afshin

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi,

    Index fragmentation occurs when a row included in the index is deleted AKA index stagnation.

    You will need to analyze you indexes individually to find those stagnated indexes, once discovered they can be rebuilt.

    To analyze issue the following command :-

    analyze index owner.index_name validate structure;

    The index information will now be in table 'index_stats'

    Now issue the following query :-

    select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
    where name = 'index_ name'

    If 20%+ of rows are deleted then the index should be rebuilt.

    The index stats table can only hold one record of information at a time, therefore you will need to analyze each index individually and then interrogate index_stats, you can also automate this process using pl/sql.

    Alternatively, you can use Oracle Enterprise Manager, Index Tuning Wizard.

    This information was partly supplied by user : hacketta

    Cheers

    Suresh









  5. #5
    Join Date
    May 2001
    Location
    New Zealand
    Posts
    123

    Rebuild Index

    Thankx Suresh

    Appreciate it ..

  6. #6
    Join Date
    Aug 2000
    Posts
    236
    When I do a export import, does it have any positive./negative effect on my indexes?

    Nizar

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, it does have positive effects. After importing the table, its indexes are freshly rebuilt. So in fact it has the same consequences as ALTER INDEX REBUILD, you are just achieving it the hard way .
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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