| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Exactly what are "leaf_blocks"??? Script included.
A copy of this was sent to Sybrand Bakker <postbus_at_sybrandb.demon.nl>
(if that email address didn't require changing)
On Fri, 07 Aug 1998 20:39:51 +0200, you wrote:
>Hi Steve,
>Indexes are in the form of a B+ tree. The leaf blocks are the lowest level
>of the tree, so the blocks containing the actual keys + rowids in the
>table.
>Your query is incorrect, regrettably. To my best knowledge there is only
>one method to check this out:
>analyze index < index name> validate structure
>followed by
>select * from index_stats.
>This table will show you the actual size of the B-tree, and how much is
>really in use. NB: deleted rows are NOT reclaimed.
>
Thats not true. Index space is reused. (like a table, space is not released to be used by other objects, but it is reused within the index).
To show this, I created a table. I indexed a column in that table. Put 1,000 rows in there. I repeatedly delete and add records to this table. After I delete 500 records and add 500 new records (using values never before seen in the table), I analyze the index and dump the results. I do this over and over and over. If Oracle never reused index space, we would expect the index to grow infinitely large over time. In fact, the index never really grows -- it reuses the space as it is freed up. the following table is the output of the procedures (below). It shows you
Blocks -- total blocks allocated to the index LfRow -- total number of leaf row entries in the index LfBlk -- total number of leaf blocks in the indexBrRow/BrBlk -- Branch rows/blocks
been indexing is reused.
MinX/MaxX/Count minimum value we have indexed, maximum value, and count of rows
in the table
. Blocks LfRow LfBlk BrRow BrBlk DelRows MinX MaxX Count
. 20 1,000 10 9 1 0 501 10,499 1,000
. 20 1,006 14 13 1 6 521 10,999 1,000
. 20 1,006 14 13 1 6 821 11,499 1,000
. 20 1,004 14 13 1 4 821 11,999 1,000
. 20 1,003 14 13 1 3 821 12,499 1,000
. 20 1,003 14 13 1 3 821 12,999 1,000
. 20 1,003 14 13 1 3 821 13,499 1,000
. 20 1,003 14 13 1 3 821 13,999 1,000
. 20 1,003 14 13 1 3 821 14,499 1,000
. 20 1,003 14 13 1 3 821 14,999 1,000
. 20 1,003 14 13 1 3 821 15,499 1,000
. 20 1,003 14 13 1 3 821 15,999 1,000
. 20 1,003 14 13 1 3 821 16,499 1,000
. 20 1,003 14 13 1 3 821 16,999 1,000
. 20 1,003 14 13 1 3 821 17,499 1,000
. 20 1,003 14 13 1 3 821 17,999 1,000
. 20 1,003 14 13 1 3 821 18,499 1,000
. 20 1,003 14 13 1 3 821 18,999 1,000
. 20 1,003 14 13 1 3 821 19,499 1,000
. 20 1,003 14 13 1 3 821 19,999 1,000
. 20 1,003 14 13 1 3 821 20,499 1,000
. 20 1,003 14 13 1 3 821 20,999 1,000
As you can see, the index never grew even though we deleted and added over and over again. The index stayed pretty much the same size after the initial delete/add took place (this was with a 2k block size, you will get different results with different block sizes).
the script to reproduce this is:
drop table test_tbl;
drop sequence test_seq;
create table test_tbl as
select rownum x
from ( select null from all_objects union all select null from all_objects)
where rownum <= 1000;
create index test_idx on test_tbl(x);
create sequence test_seq start with 10000;
create or replace procedure delete_insert as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
for i in 1 .. 500 loop
delete from test_tbl where rownum = 1;
commit;
insert into test_tbl values (test_seq.nextval);
commit;
dbms_sql.parse(exec_cursor, 'analyze index test_idx validate structure',
dbms_sql.native );
for x in (select blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows
from index_stats )
loop
dbms_output.put( '.' || to_char( x.blocks, '999,999' ) );
dbms_output.put( to_char( x.lf_rows, '999,999' ) );
dbms_output.put( to_char( x.lf_blks, '999,999' ) );
dbms_output.put( to_char( x.br_rows, '999,999' ) );
dbms_output.put( to_char( x.br_blks, '999,999' ) );
dbms_output.put( to_char( x.del_lf_rows, '999,999' ) );
end loop;
for x in ( select min(x) l, max(x) b, count(*) c from test_tbl ) loop
dbms_output.put( to_char(x.l, '999,999') );
dbms_output.put( to_char(x.b, '999,999') );
dbms_output.put_line( to_char(x.c, '999,999') );
end loop;
set feedback off
prompt . Blocks LfRow LfBlk BrRow BrBlk DelLfRows MinX MaxX Count
exec delete_insert exec delete_insert exec delete_insert exec delete_insert
>Hth
>
>Sybrand Bakker, Oracle DBA
>(postbus_at_sybrandb.demon.nl)
>
>Steve Orr wrote:
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 07 1998 - 14:35:09 CDT
 |
 |