Re: index block cleanout

> Maxim, I don't think that removing the COMMIT will help release the
> unused blocks in the index (there by reducing the number of deleted
> rows in the index structure):
> SQL> SELECT VERSION FROM V$INSTANCE;

>
> Just to be clear, the above is NOT a suggestion to rebuild indexes.
> Inserting a single value into the table took care of 40 of the deleted
> leaf rows automatically, as a block in the index structure containing
> completely deleted rows was reused.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Charles, the point of index block cleanout ( as i understood it) is , that if you force to write index blocks to disk before commiting associated transaction, then deleted entries may be cleaned out of index structure ( bad rephrasing of original blog entry ). So, what i suggested to do is - take original test case and comment out commits to demonstrate the described behaviour:

SQL> -- 1. create table test_empty_block (id number, value varchar2(10)); SQL> create table test_empty_block (id number, value varchar2(10));

Table created.

SQL>
SQL> -- 2. create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);
SQL> create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);

Index created.

SQL> -- 3.
SQL> -- begin
SQL> -- for i in 1..100000 loop
SQL> -- insert into test_empty_block values (i, 'Bowie');
SQL> -- end loop;
SQL> -- commit;
SQL> -- end;
SQL> -- /
SQL>
SQL> begin

   2 for i in 1..100000 loop
   3 insert into test_empty_block values (i, 'Bowie');    4 end loop;
   5 commit;
   6 end;
   7 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- 4. delete from test_empty_block where id < 99999;
SQL> delete from test_empty_block where id < 99999;

99998 rows deleted.

SQL> -- commit;
SQL> -- *Commit* is not issued at this point as suggested by me
SQL>
SQL> -- 5. analyze index test_empty_block_idx validate structure;
SQL> -- *Analyze* is not issued as suggested at this point as well
SQL>
SQL> -- select * from index_stats;
SQL>
SQL> -- 6.
SQL> -- ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
SQL> -- alter system flush buffer_cache;
SQL> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

Session altered.

SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> -- 7.
SQL> -- analyze index test_empty_block_idx validate structure;
SQL> -- select * from index_stats;
SQL> analyze index test_empty_block_idx validate structure;

Index analyzed.

SQL> select blocks,name,lf_rows,del_lf_rows from index_stats;

     BLOCKS NAME                              LF_ROWS DEL_LF_ROWS
---------- ------------------------------ ---------- -----------
        256 TEST_EMPTY_BLOCK_IDX                    2           0

Best regards

Maxim

(Of course, it is not meant to be a substitute for index rebuild, but it may accent the point, that index rebuild may be avoided in some cases). Received on Sat Mar 07 2009 - 07:31:34 CST