Re: ALTER INDEX COALESCE

[Tony Jambu <tjambu_freelists@xxxxxxxxxxxx>]

Hi Jared

There is a myth about unusable space in a B+ tree where there
are frequent inserts and deletes especially a right handed index.
eg a PK with a ascending Sequence number or date+time
and rolling insert/delete operations where the number of records
remains fairly constant.

In this scenario (right handed index with rolling deletes ie FIFO ),
there is hardly any unused leaf blocks.  The whole index structure
remains steady state.  it is the nodes that get updated to balance the leaf 
blocks.

Maybe the following examples might help.
It shows how the Btree behaves and also at the end if you drop and
recreate the index (i could hav rebuilt it), would the structure change
much.



column table_name format a10
column object_name format a10
column column_name format a10
column index_name format a10
set lines 132
set pagesize 5000


Prompt "Test Reuse of Deleted space - 1st Create Objects"

drop table t2; 
create table t2 (col1 number); 
create UNIQUE index t2_IDX on t2(col1)  ;
alter table t2 add constraint T2_PK PRIMARY KEY (col1);

ACCEPT cont Prompt "Insert data (inserts and deletes)"
begin
for i in  100000..600000 loop
    delete t2 where col1 = i-1000 ;
    commit;
    insert into t2 values(i);
    commit;
end loop;
end;
/
Commit;

ACCEPT cont Prompt "Analyse data"
EXEC DBMS_STATS.gather_table_stats('TJAMBU', 'T2', estimate_percent => 20, 
cascade=>TRUE);
analyze index t2_IDX validate structure;

ACCEPT cont Prompt "Display Stats"
select index_name, blevel, leaf_blocks, distinct_keys,
        CLUSTERING_FACTOR, NUM_ROWS,
        AVG_LEAF_BLOCKS_PER_KEY        ,AVG_DATA_BLOCKS_PER_KEY
from user_indexes where table_name like 'T2'
/
select lf_rows, del_lf_rows, lf_blks  from index_stats;






ACCEPT cont Prompt "Can we reuse the deleted space with new inserts?"
begin
for i in  600001..601000 loop
    insert into t2 values(i);
end loop;
end;
/
alter session set events 'immediate trace name flush_cache';
Commit;



EXEC DBMS_STATS.gather_table_stats('TJAMBU', 'T2', estimate_percent => 20, 
cascade=>TRUE);
analyze index t2_IDX validate structure;

ACCEPT cont Prompt "Display Stats"
select index_name, blevel, leaf_blocks, distinct_keys,
        CLUSTERING_FACTOR, NUM_ROWS,
        AVG_LEAF_BLOCKS_PER_KEY        ,AVG_DATA_BLOCKS_PER_KEY
from user_indexes where table_name like 'T2'
/
select lf_rows, del_lf_rows, lf_blks  from index_stats;







ACCEPT cont Prompt "Drop, recreate and analyse index"
Alter table t2 drop primary key;
create UNIQUE index t2_IDX on t2(col1)  ;
alter table t2 add constraint T2_PK PRIMARY KEY (col1);
EXEC DBMS_STATS.gather_table_stats('TJAMBU', 'T2', estimate_percent => 20, 
cascade=>TRUE);
analyze index t2_IDX validate structure;

ACCEPT cont Prompt "Display Stats"
select index_name, blevel, leaf_blocks, distinct_keys,
        CLUSTERING_FACTOR, NUM_ROWS,
        AVG_LEAF_BLOCKS_PER_KEY        ,AVG_DATA_BLOCKS_PER_KEY
from user_indexes where table_name like 'T2'
/
select lf_rows, del_lf_rows, lf_blks  from index_stats;


ta
tony


At 03:16 AM 3/08/2006, Jared Still wrote:

>One example is  when a primary key is generated by a sequence,
>and the table undergoes frequent deletes.  This leaves a lot of unusable
>entries in the index.  Unusable because the values continually ascend, 
>with new values continually going to new blocks.
> 
>One cure for that is a reverse index, but it has its own problems, 
>depending on how you use the index.  Reverse indexes don't
>do well with range scans. 
>
>That particular scenario may be a good reason to use SYS_GUID(),
>even if you don't use RAC. (doesn't everybody?)
>
>There's a fair bit of writing about this index topic on AskTom.
>
>-- 
>Jared Still 
>Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

• References:
  • ALTER INDEX COALESCE
    • From: Roger Xu
  • Re: ALTER INDEX COALESCE
    • From: Dennis Williams
  • Re: ALTER INDEX COALESCE
    • From: Jared Still

Other related posts:

ALTER INDEX COALESCE

Re: ALTER INDEX COALESCE

Re: ALTER INDEX COALESCE

Re: ALTER INDEX COALESCE