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

On Fri, 7 Sep 2001 20:09:24 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>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
Yeah, but this should in fact be faster. This one, in all, will pull about 230 rows from ZOT, which then get joined to PSTREESELECT06. It should be faster, but isn't. The other one pulls about 9000 rows (using the same index!) off ZOT, then goes through the other two indexes. Yet, it executes much faster.

>
>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.

Tables are not very large. ZOT is 92000 rows, PSTREESELECT10 is 9000 odd, PSTREESELECT06 is around 5000. All indexes involved are unique.

>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.

The only "abnormal" thing is that ZOT_I1 is an index on 12 columns (this is Peoplesoft, after all!). I had a look at the order of the columns in the index and some high cardinality ones were up front but not used by the predicates. I simply reversed them (low upfront, increasing to last ones). That fixed it for CBO. The worrying thing is the bad order of the columns worked fine in RBO...

>
>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.
>

Probably. Another thing I tried was to sort the rows in the PSTREESELECT tables in the same order as the columns in their indexes. This usually helps range scans. I didn't do ZOT, because that one would be harder with the 12 column index. But I'll still try it.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Sat Sep 08 2001 - 07:31:07 CDT