myth

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

I read a post this morning by Grant Ronald talking about fusion apps. In Grant’s post he mentioned things that people have been saying about Fusion over the years. Middleware and Apps are not my specialist field, but I get to hear a lot about them from the conferences and ACE Director meetings, so I have been witness to the Oracle Fusion myth from the beginning.

Cast your mind back several years and the whole concept of Fusion was launched at OOW. We were told that the middleware stack was going to become a single coherent product, rather than the buggy rag-tag bunch of technologies we had in 9iAS and AS10g. Sounds good so far, but then all the existing stuff got rebranded as Fusion Middleware when the products it was made up of hadn’t significantly changed. That’s confusing.

Fast forward a bit and we were expecting something like real Fusion Middleware to appear, then the BEA buyout was announced and WebLogic became the core of Fusion Middleware. Oh. So this wonderful coherent product that Oracle had been developing and we were expecting soon was swapped for a best-of-breed app server from an acquisition. Strange and a little disconcerting, but at least we have a better app server now, except that some of the existing features still required you to install the old AS10g stuff. Still the name Fusion is plastered everywhere.

Fast forward a bit more and we have got to a point where applying the term “Fusion” to the middleware stack is less insulting, but if anyone experienced Fusion along the way they would probably have been left with a bad feeling about what Fusion actually means. It’s very hard to overcome a bad first impression. Are Oracle really surprised that the term “Fusion” is associated with myth and confusion?

OK. That’s the Middleware. What about Fusion Apps? Well, the name includes the word “Fusion”, so it takes on all the bad connotations associated with the infancy of Fusion Middleware. Added to that, since the original announcement of Fusion Apps there have been numerous acquisitions, all of which have no doubt added to the confusion about what Fusion Apps actually is. Then we are told there is no natural upgrade from eBusiness Suite to Fusion Apps. It’s a new product and we have to migrate data to it as we would any new ERP. Next we are told that the initial release will only be a subset of the modules we require, so we will have to run it alongside eBusiness Suite. Wow. This is really confusing. That sounds like a half-finished ERP built on a half-finished middleware stack. Once again, are Oracle really surprised people react like this?

Now I’m not saying the Fusion Middleware is bad. It’s come a long way. I’m also not saying Fusion Apps are bad. I’ve seen the demos and they look amazing. I’ve also talked to people in that field who are genuinely impressed and exited by it. I believe it will be a big eye opener and possibly a game-changer for a lot of people. What I’m saying is I can totally understand when people on the outside of our little goldfish bowl have a really bad and confused impression of anything containing the term “Fusion”, because it does have a very long and sordid history.

In my opinion the term Fusion needs to be scrapped and replaced, then perhaps we can forget the history and focus on the now. Kinda like they did with Beehive.

Cheers

Tim…