Understanding INDEX_STATS view

I am trying to figure out a query that will tell me which indexes need to be rebuilt based on how many deleted rows are present in the index. From what I have read, I need to use the INDEX_STATS view after the index as been analyzed.

However, I am running into inconsistencies in the data being reported by INDEX_STATS. For example:
ops$jeffh@ibdev1.us> drop table jh_index_stat
2 /

Table dropped.

ops$jeffh@ibdev1.us> create table jh_index_stat (
2 run_ts date,
3 HEIGHT NUMBER,
4 BLOCKS NUMBER,
5 NAME VARCHAR2(30),
6 PARTITION_NAME VARCHAR2(30),
7 LF_ROWS NUMBER,
8 LF_BLKS NUMBER,
9 LF_ROWS_LEN NUMBER,
10 LF_BLK_LEN NUMBER,
11 BR_ROWS NUMBER,
12 BR_BLKS NUMBER,
13 BR_ROWS_LEN NUMBER,
14 BR_BLK_LEN NUMBER,
15 DEL_LF_ROWS NUMBER,
16 DEL_LF_ROWS_LEN NUMBER,
17 DISTINCT_KEYS NUMBER,
18 MOST_REPEATED_KEY NUMBER,
19 BTREE_SPACE NUMBER,
20 USED_SPACE NUMBER,
21 PCT_USED NUMBER,
22 ROWS_PER_KEY NUMBER,
23 BLKS_GETS_PER_ACCESS NUMBER,
24 PRE_ROWS NUMBER,
25 PRE_ROWS_LEN NUMBER
26 )
27 /

Table created.

ops$jeffh@ibdev1.us> drop table test1
2 /

Table dropped.

ops$jeffh@ibdev1.us> create table test1 (id number(10) , data_val varchar2(60))
2 /

Table created.

ops$jeffh@ibdev1.us> create unique index test1_pk on test1 (id)
2 /

Index created.

ops$jeffh@ibdev1.us> insert into test1 select rownum, column_name from all_tab_columns
2 /

26512 rows created.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
2 /

MIN_ID MAX_ID COUNT_ID
---------- ---------- ----------
1 26512 26512

ops$jeffh@ibdev1.us> analyze index test1_pk validate structure
2 /

Index analyzed.

ops$jeffh@ibdev1.us> insert into jh_index_stat
2 select sysdate,
3 HEIGHT,
4 BLOCKS,
5 NAME,
6 PARTITION_NAME,
7 LF_ROWS,
8 LF_BLKS,
9 LF_ROWS_LEN,
10 LF_BLK_LEN,
11 BR_ROWS,
12 BR_BLKS,
13 BR_ROWS_LEN,
14 BR_BLK_LEN,
15 DEL_LF_ROWS,
16 DEL_LF_ROWS_LEN,
17 DISTINCT_KEYS,
18 MOST_REPEATED_KEY,
19 BTREE_SPACE,
20 USED_SPACE,
21 PCT_USED,
22 ROWS_PER_KEY,
23 BLKS_GETS_PER_ACCESS,
24 PRE_ROWS,
25 PRE_ROWS_LEN
26 from index_stats
27 /

1 row created.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> delete from test1 where data_val < 'N'
2 /

13450 rows deleted.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
2 /

MIN_ID MAX_ID COUNT_ID
---------- ---------- ----------
1 26511 13062

ops$jeffh@ibdev1.us> analyze index test1_pk validate structure
2 /

Index analyzed.

ops$jeffh@ibdev1.us> insert into jh_index_stat
2 select sysdate,
3 HEIGHT,
4 BLOCKS,
5 NAME,
6 PARTITION_NAME,
7 LF_ROWS,
8 LF_BLKS,
9 LF_ROWS_LEN,
10 LF_BLK_LEN,
11 BR_ROWS,
12 BR_BLKS,
13 BR_ROWS_LEN,
14 BR_BLK_LEN,
15 DEL_LF_ROWS,
16 DEL_LF_ROWS_LEN,
17 DISTINCT_KEYS,
18 MOST_REPEATED_KEY,
19 BTREE_SPACE,
20 USED_SPACE,
21 PCT_USED,
22 ROWS_PER_KEY,
23 BLKS_GETS_PER_ACCESS,
24 PRE_ROWS,
25 PRE_ROWS_LEN
26 from index_stats
27 /

1 row created.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> select run_ts, lf_rows, br_rows, del_lf_rows
2 from jh_index_stat
3 /

RUN_TS LF_ROWS BR_ROWS DEL_LF_ROWS
-------- ---------- ---------- -----------
13:32:41 26512 209 0
13:32:42 26512 209 13450


At this point, The data looks right. However, when more data gets put into the table and subsequently deleted, things get all screwed up...
ops$jeffh@ibdev1.us> insert into test1 select rownum+40000, column_name from all_tab_columns
2 /

26512 rows created.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
2 /

MIN_ID MAX_ID COUNT_ID
---------- ---------- ----------
1 66512 39574

ops$jeffh@ibdev1.us> delete from test1 where data_val < 'N'
2 /

13450 rows deleted.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
2 /

MIN_ID MAX_ID COUNT_ID
---------- ---------- ----------
1 66511 26124

ops$jeffh@ibdev1.us> analyze index test1_pk validate structure
2 /

Index analyzed.

ops$jeffh@ibdev1.us> insert into jh_index_stat
2 select sysdate,
3 HEIGHT,
4 BLOCKS,
5 NAME,
6 PARTITION_NAME,
7 LF_ROWS,
8 LF_BLKS,
9 LF_ROWS_LEN,
10 LF_BLK_LEN,
11 BR_ROWS,
12 BR_BLKS,
13 BR_ROWS_LEN,
14 BR_BLK_LEN,
15 DEL_LF_ROWS,
16 DEL_LF_ROWS_LEN,
17 DISTINCT_KEYS,
18 MOST_REPEATED_KEY,
19 BTREE_SPACE,
20 USED_SPACE,
21 PCT_USED,
22 ROWS_PER_KEY,
23 BLKS_GETS_PER_ACCESS,
24 PRE_ROWS,
25 PRE_ROWS_LEN
26 from index_stats
27 /

1 row created.

ops$jeffh@ibdev1.us> commit;

Commit complete.

ops$jeffh@ibdev1.us> select run_ts, lf_rows, br_rows, del_lf_rows
2 from jh_index_stat
3 /

RUN_TS LF_ROWS BR_ROWS DEL_LF_ROWS
-------- ---------- ---------- -----------
13:32:41 26512 209 0
13:32:42 26512 209 13450
13:32:59 43292 424 17168

Since all the entries in this index are unique and distinct, I expected 53024 for LF_ROWS and 26900 for the DEL_LF_ROWS for the third entry. What am I misunderstanding?

Whoa, Jeff Hunter has asked a question - Coool! :)

Well, I better make this one count, then :), so here we go:

You start with 0, add 26,512 rows, hence 26,512 leaf rows
...so far, so good
You delete 13450 of them, hence 13,450 deleted leaf rows.
...still on track

Now.... you add back in another 26,512 rows, thereby expecting a total of 53,024 leaf rows, *but* you already deleted a bunch, so what if the index re-uses those deleted entries?

Assume, for example, that out of 13,450 empty leaf rows sitting around, say, oh, 9732 of them got re-used.

That leaves 26512 - 9732 or 16780 new leaf rows getting inserted:

26512 + 16780 = 43,292. Spiffy, eh?

Now, as we said, 9732 of those rows got re-used. Now, we delete a total of 13,450 rows again. 9732 of those rows occupied the same space as previously-deleted rows, so we only actually deleted 13450 - 9732 or 3718 *new* rows from the index. This brings our *total* of deleted rows to 13450 + 3718, or 17168.

Phew, gotta dot those i's when answering one for the man :D


HTH,

- Chris

To verify what chris has explained, u can dump index tree structure befor and after the process.

Quote:

---

Originally posted by chrisrlong
Now.... you add back in another 26,512 rows, thereby expecting a total of 53,024 leaf rows, *but* you already deleted a bunch, so what if the index re-uses those deleted entries?

Assume, for example, that out of 13,450 empty leaf rows sitting around, say, oh, 9732 of them got re-used.

---

This is the part I don't understand. Since my index entries are all unique, I would expect that the index to re-use an entry UNLESS it had the exact same key value. For example, if I inserted a row with an ID=1, and deleted it, I would not expect that index row to be used again if I inserted ID=2. It should only get re-used if I insert another row with ID=1. (According to "Oracle Performance Tuning Tips & Techniques, Richard Niemiec" at least.)

I buy your explanation, I just don't understand why the index rows would get re-used.

I was hoping you wouldn't go there :)

Unfortunately, I do not have a solid answer for that, but I have a theory, so let's run with it.

I just performed your same test with slightly different results. After the second set of insertions, only a few hundred 'leaf rows' were 're-used'. I then performed the same steps a couple more times with similar results. I then inserted a new set, deleted the entire set, then re-inserted the entire set again (with higher ids, of course). In that case, almost *all* the leaf rows were re-used.

Given all that, my theory is this: I have a much bigger block size than you do - mine is 16K. (genetics - it's not your fault :D)

Given that, when I delete the 'semi-random' records, it is very rare that I delete *all* the records from an entire index block. You, on the other hand (so my theory goes) have a smaller block size, so you have more blocks that have *all* their entries deleted. It would seem that deleting all the entries in the index block will put that block back on the free list within the index to be subsequently re-used on the next insert. Therefore, its corresponding 'leaf rows' also get 're-used' or subtracted from the total during that insert.

As you pointed out, it is not possible to actually re-use a row in an index unless you are actually re-inserting the exact same value. However, once the block is empty, the block, and its accompanying count of deleted rows *can* be 're-used' in a fashion.

What do you think?

- Chris

del_lf_rows or the deleted rows still in leaf blocks, if there are no rows in a leaf block, that leaf block is released and put under free list for leaf block splits.Other wise that block is not released.
del_lf_rows does not count the rows of the leaf blocks for which the whole leaf block becomes empty.

If u see ur stats, lf_rows and br_rows.
lf_rows according to u should have been total leaf rows inserted (minus some br_rows) but it is not so , so total rows allocated for leaf shows some of the leaf blocks did get full empty and thus space reused.


I hope it is clear.
GP

Quote:

---

Originally posted by chrisrlong
As you pointed out, it is not possible to actually re-use a row in an index unless you are actually re-inserting the exact same value. However, once the block is empty, the block, and its accompanying count of deleted rows *can* be 're-used' in a fashion.

---

True, I am working with a 2K block size (Don't laugh, I inherited it). I can agree with that theory. I didn't really think about what happens when the DB block becomes free. If the DB block becomes free and gets placed back on the free list, it looks like the corresponding data wouldn't get reported in the del_lf_rows because it's not really there anyway.

Thanks for the help. As always, it's appreciated...

I tested this today

cerated a test table
inserted 100 rows with values 1 to 100

then ran analyze validate structure, got
BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
----------- ---------- ---------- ---------- -----------
7996 1300 17 100 0

deleted 50 rows
analyze again
BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
----------- ---------- ---------- ---------- -----------
7996 1300 17 100 50

inserted 51 rows from 200 to 250
analyzed again
BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
----------- ---------- ---------- ---------- -----------
7996 1377 18 102 0

Hm, how come I got 102 back instead of 101?

My block size is 8K

It would be interesteing to see what you get if you "alter index xyz rebuild"

just rebuilt it, nothin were changed


SQL> alter index test_pk rebuild;

Index altered.

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
----------- ---------- ---------- ---------- -----------
7996 1377 18 102 0

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

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

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]

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.

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

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

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