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

Thread: index rebuild question

  1. #1
    Join Date
    Apr 2001
    Location
    Berlin
    Posts
    65

    Cool

    My index needs rebuilding so I analyzed it and based on the information from the index_stats, the pct_used was 81% so I decided to rebuild it.
    After rebuilding it, I decided to analyze it again to make sure that pct_used column of the index_stats is good. After analyzing it again, Irealized that the pct_used column has not changed.
    I thought that after rebuilding the index the pct_used column of index_stats would change. Am I wrong? Or I'm doing something wrong
    Below are the steps that I followed:


    1.
    analyze index BOB.PK_NEWSTABLE validate structure;

    2.
    SQL> select name,btree_space,used_space,pct_used from index_stats;


    NAME BTREE_SPACE USED_SPACE PCT_USED
    ------------------------------ ----------- ---------- ----------
    PK_NEWSTABLE 58532 47046 81

    3.
    ALTER INDEX BOB.PK_NEWSTABLE REBUILD storage
    (initial 4M next 4M pctincrease 0)
    tablespace news_ind_tbs;

    4.
    SQL> analyze index BOB.PK_NEWSTABLE validate structure;

    Index analyzed.

    5.
    SQL> select name,btree_space,used_space,pct_used from index_stats;

    NAME BTREE_SPACE USED_SPACE PCT_USED
    ------------------------------ ----------- ---------- ----------
    PK_NEWSTABLE 58532 47010 81


  2. #2
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    Find the ratio '(del_lf_rows_len/lf_rows_length)*100'. This is the ratio you might have to observe.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    http://otn.oracle.com/docs/products/...2292.htm#10867

    I prefer to look at (del_lf_rows/lf_rows)*100 to see how much of my index is empty.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Apr 2001
    Location
    Berlin
    Posts
    65
    OK like you guys suggested I used del_lf_rows_len and lf_rows_length and below is the result that I got.

    Deleted Used % Deleted
    Index Name Leaf Rows Leaf Rows Leaf Rows
    ------------------------------ --------- --------- ----------
    PK_NEWSTABLE 0 1613 .00000


    QUESTION: From the above results what is the best way to determine if the index needs to be rebuild?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by marist89
    [url]
    I prefer to look at (del_lf_rows/lf_rows)*100 to see how much of my index is empty.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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