Re: Fragmentation in Oracle
Date: 1997/11/07
Message-ID: <346331ed.7800857_at_newshost>
On Thu, 6 Nov 1997 20:11:28 -0000, "Dave S." <dsingh_at_tgtsolutions.com> wrote:
>Try using an 80% (or higher) PCTUSED setting on the table. Please see >below. > >Also beware, Index space IS NEVER REUSED. If you add records, then delete >them, the index space is not release nor reused. >
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
DelRows -- Deleted index entries that will be reused only if the value it had
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;
end loop;
for i in 1 .. 500 loop
insert into test_tbl values (test_seq.nextval);
commit;
end loop;
dbms_sql.parse(exec_cursor, 'analyze index test_idx validate structure',
dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor); dbms_sql.close_cursor( exec_cursor );
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;
end;
/
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
REM repeat the above line as many times as you want to redo the delete/add REM routine
[snip]
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
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 Nov 07 1997 - 00:00:00 CET