Re: index block cleanout
Date: Sat, 07 Mar 2009 13:52:54 +0100
Message-ID: <71f92mFl2ejuU1_at_mid.individual.net>
On 07.03.2009 12:00, lsllcm wrote:
> 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
If you look at Richard's example then you'll notice that he places COMMIT in different locations - and this makes the difference!
http://richardfoote.wordpress.com/2008/06/23/deleted-index-entries-part-iii-slip-away/
This works for me:
SQL> select * from v$version
2 /
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table test_empty_block (id number not null, value varchar2(10))
2 /
Table created.
SQL> create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id)
2 /
Index created.
SQL> insert into test_empty_block
2 select level, 'Bowie'
3 from dual
4 connect by level <= 100000
5 /
100000 rows created.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE
2 /
System altered.
SQL> commit
2 /
Commit complete.
SQL> analyze index test_empty_block_idx validate structure
2 /
Index analyzed.
SQL> select height, blocks, LF_ROWS, DEL_LF_ROWS, DEL_LF_ROWS_LEN
2 from index_stats
3 /
HEIGHT BLOCKS LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
- ---------- ---------- ----------- ---------------
2 256 100000 0 0
SQL> delete from test_empty_block
2 where id < 100000
3 /
99999 rows deleted.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE
2 /
System altered.
SQL> commit
2 /
Commit complete.
SQL> analyze index test_empty_block_idx validate structure
2 /
Index analyzed.
SQL> select height, blocks, LF_ROWS, DEL_LF_ROWS, DEL_LF_ROWS_LEN
2 from index_stats
3 /
HEIGHT BLOCKS LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
- ---------- ---------- ----------- ---------------
2 256 1 0 0
SQL> select count(id)
2 from test_empty_block
3 where id > 100
4 /
COUNT(ID)
1
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE
2 /
System altered.
SQL> commit
2 /
Commit complete.
SQL> analyze index test_empty_block_idx validate structure
2 /
Index analyzed.
SQL> select height, blocks, LF_ROWS, DEL_LF_ROWS, DEL_LF_ROWS_LEN
2 from index_stats
3 /
HEIGHT BLOCKS LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
- ---------- ---------- ----------- ---------------
2 256 1 0 0
SQL> drop table test_empty_block
2 /
Table dropped.
SQL> exit
[oracle_at_ora01 ~]$
Cheers
robert Received on Sat Mar 07 2009 - 06:52:54 CST