Re: index block cleanout
- From: Robert Klemme <shortcutter@xxxxxxxxxxxxxx>
- Date: Sat, 07 Mar 2009 13:52:54 +0100
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@ora01 ~]$
Cheers
robert
.
- References:
- index block cleanout
- From: lsllcm
- index block cleanout
- Prev by Date: Re: index block cleanout
- Next by Date: Re: index block cleanout
- Previous by thread: Re: index block cleanout
- Next by thread: Re: index block cleanout
- Index(es):
Relevant Pages
|
