Tom Kyte's book obsolescence

This is a longer post and I beg your forgiveness for that.

Recently, I've had a complaint that analytic function is not executing very fast. The Oracle versions involved were 10.2.0.3 and 11.1.0.6. I wrote the same query as a self-join and, to my infinite surprise, the query was running faster then with the analytic functions. I remembered a book that says it shouldn't be that way. The book was "Effective Oracle by Design", by Tom Kyte. I created the structures, ran the queries and the book was wrong on versions 10g and 11g. It was correct on the version 9i.

In other words, in the Tom Kyte's book "Effective Oracle By Design", the 2nd query on the page 517 is executing faster then the first one, the one with the analytic functions. Plans look very similar to what is in the book but the query that Tom claims should execute faster now executes slower.

This discovery is loaded with implications: 1) Oracle does I/O in versions 10g and 11g in the different way then it

   used to in versions 9i and below. I am not sure what exactly is the    difference, but there is a difference. 2) Analytic functions are now just a neat trick, no longer a performance

   improvement. The cost of the window buffer sort is relatively high    compared to the cost of simple join. 3) Some information in "Effective Oracle by Design" is obsolete. How much,

   exactly?

Below is the script needed to set up the infamous BIG_TABLE used in all of the queries. I hope Tom Kyte will not mind and I hope he will read this. He used to follow this group once upon a time, in a version far, far away.

create table bigtab

          as
          select rownum id, a.*
  from all_objects a
           where 1=0;
          alter table bigtab nologging;           


-- Fill 1'000'000 Rows into            the Table

    declare
    l_cnt  number;
    l_rows number := 1000000;

          begin

    -- Copy ALL_OBJECTS
    insert /*+ append */
    into bigtab
    select rownum, a.*
      from all_objects a;
    l_cnt := sql%rowcount;
    commit;
          
    -- Generate Rows
    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into bigtab
        select rownum+l_cnt,
                         OWNER, OBJECT_NAME, SUBOBJECT_NAME,
                         OBJECT_ID, DATA_OBJECT_ID,
                         OBJECT_TYPE, CREATED, LAST_DDL_TIME,
                         TIMESTAMP, STATUS, TEMPORARY,
                         GENERATED, SECONDARY
          from bigtab
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;

          end;
          /
          
          alter table bigtab add constraint
          bigtab_pk primary key(id);

--

Mladen Gogala
http://mgogala.freehostia.com Received on Fri Feb 01 2008 - 09:02:11 CST