| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Explanation of index statistics
It's not difficult to get an index into that state, for example:
drop table t1;
create table t1 (n1 number);
create index i1 on t1(n1);
insert into t1 values (0);
begin
for i in 1..100000 loop
insert into t1 values (i);
delete from t1 where n1 = i - 1;
end loop;
end;
/
commit;
select count(*) from t1;
validate index i1;
select lf_rows, lf_blks, del_lf_rows
from index_stats;
/*
LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
100001 199 100000
*/
Your code is obviously not doing exactly this, but there are probably some similarities between what you are doing and the pl/sql loop above.
It's often the result of performing a piece of batch processing in single-row mode.
Is the table is supposed to be empty all the time, apart from the moment when it holds the next little batch of data ? If so, do you really need that index anyway ?
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
I did an analyze against the index that is using so many buffers (when there was less than 10 rows in the table).
analyze index &indexname validate structure
The INDEX_STATS table shows
HEIGHT BLOCKS NAME PARTITION_NAME
3
LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN 106536 6465 2450320 3540 6464 157 122626 BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY
3932 106533 2450251 30847 18
BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
PRE_ROWS
23503424 2572946 11 3.4536908 5.2268454
0
PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
0 2 28
How do these numbers match to such a small table (at the time of the analyze)?
 |
 |