Understanding INDEX_STATS view

[LND]

on ixora recently was a mail "RE: Deleted rows and empty blocks in indexes" that explains in good detail what and why.

[pando]

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....

[pando]

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]

[LND]

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.

[pando]

Well I think the Note has a point stating why PCTUSED is always 0 for indexes no?

[LND]

but where on metalink article 108573.1 it is stated that index blocks can not be reused FOR THE SAME INDEX?

[pando]

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