Re: index block cleanout
Date: Sat, 07 Mar 2009 12:46:17 +0100
Message-ID: <49B25E89.5060409_at_gmail.com>
lsllcm schrieb:
> Hi All,
>
> I have one question about index block cleanout. When I use two
> commands to cleanout index block. But it does not work at all.
>
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> alter system flush buffer_cache;
>
>
> Below are my test case
>
> 1. create table test_empty_block (id number, value varchar2(10));
>
> 2. create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);
>
> 3.
> begin
> for i in 1..100000 loop
> insert into test_empty_block values (i, 'Bowie');
> end loop;
> commit;
> end;
> /
>
> 4. delete from test_empty_block where id < 99999;
>
> commit;
>
> 5. analyze index test_empty_block_idx validate structure;
>
> select * from index_stats;
> DEL_LF_ROWS
> ---------------------------
> 91209
>
> 6.
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> alter system flush buffer_cache;
>
> 7.
> analyze index test_empty_block_idx validate structure;
>
> select * from index_stats;
> DEL_LF_ROWS
> ---------------------------
> 91209
>
> Thanks at first
> Jacky
I suppose, you could as well directly ask Richard Foote ( where you
probably read about this behaviour), but anyway, from
http://richardfoote.wordpress.com/2008/06/23/deleted-index-entries-part-iii-slip-away/
<cite>
If an Oracle index block with deleted index entries is written to disk
*before* the associated transaction performing the index delete
operation is committed, the next time the index block is accessed,
Oracle will not only clean out the transaction details from the index
block (such as the lock byte) but the deleted index entries themselves
*may also* be cleaned out as well.
</cite>
(i tried to retain original emphasis)
So, if you repeat your test without *commit* at the step 4 as well as step 5 (analyze does implicit commit), you'll probably see what you expect.
Best regards
Maxim Received on Sat Mar 07 2009 - 05:46:17 CST