orawin.info

Richard Foote has put up another excellent rebuttal of the myth that you should rebuild indexes based on the value of del_lf_rows/lf_rows from an appropriately valideated index structure. Greg asked a question similar to my comment on the OTN forums in this thread. Namely

Is Oracle able to reuse index space when indexed column is like current date (monolitically increasing). So if we delete some old rows (past dates) and inserted new rows are with higer (more recent) dates . Does Oracle reuse space from deleted rows ?

The short answer to Greg is that the Radiohead example Richard uses shows that index space is reused for montonically increasing indexes where the old data is completely deleted. What happens is that the block gets put back on the freelist and is eligible for reuse as a new leaf block. This pattern of insert, process, delete old is pretty common. Sometimes however not *all* the old data gets deleted. In this case Oracle won’t re-use the free space. I have modified Richard’s example so that I delete all the non-prime values below a threshold. Due to the pattern of distribution of the primes this should leave a relatively large number of almost empty index blocks. In this case we see that the old space is not reused until the laggard’s have been finally deleted. NB if you are tempted to use my prime number test, feel free but be aware it degrades as the value of n grows. In other words I only warrant accuracy for positive numbers, not performance. In my case a significant percentage of the available space is left held by these ‘old’ entries. It’s worth remembering however that

1. the ratio can’t tell you if you have a pattern that matches this usage and
2. in most cases you’d want to investigate why these values weren’t being deleted anyway.

NIALL @ NIALL1 >@index_deletes
NIALL @ NIALL1 >drop table rush purge;

Table dropped.

Elapsed: 00:00:03.83
NIALL @ NIALL1 >
NIALL @ NIALL1 >create table rush(
  2  	     id number
  3  ,	     code number
  4  ,	     name  varchar2(30)
  5  );

Table created.

Elapsed: 00:00:00.02
NIALL @ NIALL1 >
NIALL @ NIALL1 >insert into rush
  2  select rownum,mod(rownum,100),'MOVING PICTURES'
  3  FROM dual
  4  connect by level <= 1000000; 

1000000 rows created. 
Elapsed: 00:00:03.18 

NIALL @ NIALL1 >commit;

Commit complete.

Elapsed: 00:00:00.03
NIALL @ NIALL1 >
NIALL @ NIALL1 >create unique index pk_rush on rush(id);

Index created.

Elapsed: 00:00:05.64
NIALL @ NIALL1 >
NIALL @ NIALL1 >alter table rush
  2  add constraint pk_rush
  3  primary key (id) using index;

Table altered.

Elapsed: 00:00:00.14
NIALL @ NIALL1 >
NIALL @ NIALL1 >
NIALL @ NIALL1 >create or replace function is_prime(p in number) return number
  2  is
  3  	     l_limit number;
  4  	     i	     number := 3;
  5  	     retval  number := 1;  -- returns 0 if false, 1 if true
  6  begin
  7  	     l_limit := sqrt(p);
  8  	     if p = 1 then
  9  		     retval:=1;
 10  	     end if;
 11  	     if p = 2 then
 12  		     retval :=1;
 13  	     end if;
 14
 15  	     if mod(p,2) = 0 then
 16  		     retval := 0;
 17  	     end if;
 18
 19  	     while (i <= l_limit) loop  
 20  		if mod(p,i) = 0 then  
 21  			     retval := 0;  
 22  		end if;  
 23  		i := i + 2; -- (no need to test even numbers)
 24  	     end loop; 
 25  	     return retval; 
 26  end;  
 27  / 
Function created. 
Elapsed: 00:00:00.00 
NIALL @ NIALL1 >
NIALL @ NIALL1 >show errors
No errors.
NIALL @ NIALL1 >
NIALL @ NIALL1 >delete from rush
  2  where id < 400000  
  3  and is_prime(id) = 0;

 366139 rows deleted. 
Elapsed: 00:01:15.54 
NIALL @ NIALL1 >
NIALL @ NIALL1 >commit;

Commit complete.

Elapsed: 00:00:00.01
NIALL @ NIALL1 >
NIALL @ NIALL1 >analyze index pk_rush validate structure;

Index analyzed.

Elapsed: 00:00:00.97
NIALL @ NIALL1 >
NIALL @ NIALL1 >select lf_rows,del_lf_rows,round(del_lf_rows/lf_rows,2) del_pct
  2  from index_stats;

   LF_ROWS DEL_LF_ROWS    DEL_PCT
---------- ----------- ----------
   1000000      366139        .37

1 row selected.

Elapsed: 00:00:00.03
NIALL @ NIALL1 >
NIALL @ NIALL1 >begin
  2  for i in 1000001..1400000 loop
  3  insert into rush
  4  values(i,mod(i,100),'GRACE UNDER PRESSURE');
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:47.54
NIALL @ NIALL1 >
NIALL @ NIALL1 >analyze index pk_rush validate structure;

Index analyzed.

Elapsed: 00:00:00.49
NIALL @ NIALL1 >
NIALL @ NIALL1 >select lf_rows,del_lf_rows,round(del_lf_rows/lf_rows,2) del_pct
  2  from index_stats;

   LF_ROWS DEL_LF_ROWS    DEL_PCT
---------- ----------- ----------
   1400000      366139        .26

1 row selected.

Elapsed: 00:00:00.02
NIALL @ NIALL1 >
NIALL @ NIALL1 >
NIALL @ NIALL1 >
NIALL @ NIALL1 >
NIALL @ NIALL1 >begin
  2  for i in 1400001..2000000 loop
  3  insert into rush
  4  values(i,mod(i,100),'2112');
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:08.89
NIALL @ NIALL1 >
NIALL @ NIALL1 >analyze index pk_rush validate structure;

Index analyzed.

Elapsed: 00:00:00.63
NIALL @ NIALL1 >
NIALL @ NIALL1 >select lf_rows,del_lf_rows,round(del_lf_rows/lf_rows,2) del_pct
  2  from index_stats;

   LF_ROWS DEL_LF_ROWS    DEL_PCT
---------- ----------- ----------
   2000000      366139        .18

1 row selected.

Elapsed: 00:00:00.02
NIALL @ NIALL1 >spoo off

Possibly Related Posts:

• Index Rebuilds – A new Myth arrives