Re: ALTER INDEX COALESCE

From: Tony Jambu <tjambu_freelists_at_yahoo.com.au>
Date: Thu, 03 Aug 2006 23:15:16 +1000
Message-Id: <7.0.1.0.2.20060803230403.052ca998@yahoo.com.au>

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

Received on Thu Aug 03 2006 - 08:15:16 CDT