DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Index stagnation

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends.

    Does anyone know a way of detecting index stagnation through SQLPLUS. What are the relevant views/code to establish which indexes need rebuilding ?

    Thanks

    Suresh

  2. #2
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356

    Smile

    Hi!
    Visit http://www.dbascripts.com, Here U will all sorst of sql queries U need.
    There Nothing You cannot Do, The problem is HOW.

  3. #3
    Join Date
    Jun 2001
    Posts
    40
    Normally I would issue the following command:

    analyze index owner.index_name validate structure;

    Then I'd run the 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.

    Bear in mind that the index_stats table can only hold one row at a time so you must analyze and query each index at a time, but of course this can be automated.

    Hope this helps

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

    Talking

    Thanks Folks

    Much Appreciated.

    Suresh

  5. #5
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    Thanks. I thought one could get away with not having to analyze each index at a time.

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