Re: I still believe CBO is better than RBO, but WTH?

Nuno,
Unless I'm reading the wrong post, the two execution plans you posted are not the same.

With no stats on ZOT, making Oracle think it is small the query drives off ZOT, and for each row hits the PSTREESELECT10 index then the PSTREESELECT06 index.

  SELECT STATEMENT 4
    SORT GROUP BY 4

      NESTED LOOPS 4
        NESTED LOOPS 1
          TABLE ACCESS BY INDEX ROWID ZOT 1
            INDEX RANGE SCAN ZOT_I1 1
          INDEX RANGE SCAN PS_PSTREESELECT10 1
        INDEX RANGE SCAN PS_PSTREESELECT06 409

Putting back the stats on ZOT, thus telling Oracle that is is not a small table, Oracle drives off the PSTREESELECT10 index then accesses the table.

  SELECT STATEMENT 1
    SORT GROUP BY 1

      NESTED LOOPS 1
        NESTED LOOPS 1
          INDEX RANGE SCAN PS_PSTREESELECT10 1
          TABLE ACCESS BY INDEX ROWID ZOT 151
            INDEX RANGE SCAN ZOT_I1 151
        INDEX RANGE SCAN PS_PSTREESELECT06 409

You haven't reported the cardinality of the plan, you haven't given us much idea about the index definitions, and you haven't mentioned data distribution, histograms, or anything about analyzing the non-ZOT tables.

The only comment you have made is that you changed the order of the columns in an index - which can make a difference to the number of columns that Oracle can use to range-scan the index.

In one of your plans, you could be hitting ZOT once in an inefficient fashion, then getting into PSSELECT10 in a very efficient fashion. The other way round you could be getting into PSSELECT10 in an inefficient fashion, then hitting ZOT lots of times in an inefficient fashion. By swapping the order of the index columns you could then be allowing the multiple inefficient accesses to be multiple highly-efficient accesses.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Nuno Souto wrote in message <3b98aeab.4921402_at_news>...



>The thing I find deeply troubling is that the same SQL, with the same

>index, with the same execution plan, ends up running much, much faster

>with RBO than CBO!