Understanding INDEX_STATS view - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Understanding INDEX_STATS view

  1. #11
    Join Date
    Nov 2000
    Posts
    212
    on ixora recently was a mail "RE: Deleted rows and empty blocks in indexes" that explains in good detail what and why.

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    just carried out another test with larger values


    SQL> exec filltest(1, 10000)

    PL/SQL procedure successfully completed.

    SQL>
    SQL> analyze index test_pk validate structure;

    Index analyzed.

    SQL> select btree_space, used_space, pct_used, lf_rows, del_lf_rows
    2 from index_stats;

    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    151956 139971 93 10000 0

    SQL> delete test where empno > 5000;

    5000 rows deleted.

    SQL> analyze index test_pk validate structure;

    Index analyzed.

    SQL> select btree_space, used_space, pct_used, lf_rows, del_lf_rows
    2 from index_stats;

    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    151956 139971 93 10000 5000

    SQL> exec filltest(10001, 11000)

    PL/SQL procedure successfully completed.

    SQL> analyze index test_pk validate structure;

    Index analyzed.

    SQL> select btree_space, used_space, pct_used, lf_rows, del_lf_rows
    2 from index_stats;

    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    151956 134985 89 9572 3572


    The last part is pretty confusing, the numbers dont match....

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hey marist I thought deleted indexes are never reused and thatīs why pctused is always 0?

    Quoted from : Note:108573.1 on METALINK

    Indexes will become fragmented with inserts and deletes because
    of the way indexes are implemented. When a row is deleted,
    Oracle will not reuse the index space. Pctused for indexes
    is always 0, which means the index blocks will not be put on
    the free list for reuse. Therefore, indexes are always growing
    and can become very fragmented. Hence we have to drop and
    recreate indexes at times. This articles explains when we should do it.

    This contradicts from the mail LND mentioned from IXORA

    Quoted from IXORA:

    If a table has 100,000 rows and 99,999 of 100,000 rows and index entries are
    deleted. How is the index balanced?
    In this case the rows are deleted from the index, and the empty blocks inserted
    onto the index free list. These blocks are still part of the index and will
    need to be accessed when traversing the index.

    One says blocks wont be put on free list and the other says the blocks are inserted in the freelist....?

    [Edited by pando on 04-06-2001 at 10:59 AM]

  4. #14
    Join Date
    Nov 2000
    Posts
    212
    i would trust ixora more. in fact, this site and http://www.jlcomp.demon.co.uk are quite critical to many books/docs on oracle both by oracle and others.
    And it seems thay have a point in most cases.

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well I think the Note has a point stating why PCTUSED is always 0 for indexes no?

  6. #16
    Join Date
    Nov 2000
    Posts
    212
    but where on metalink article 108573.1 it is stated that index blocks can not be reused FOR THE SAME INDEX?

  7. #17
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well the point is even the index are NOT the same the index is being reused if you check my previous post with example

    I have 100 rows
    i deleted 50 rows, 51 to 100
    I added again 51 rows but 200 to 250, the index space were reused

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