Re: Is Richard Niemiec Right

Hi!

> > > Is this statement true?
> > >
> > > By testing, I found that changing the table position in the from
clause
> or
> > > changing the position of join conditions at where clause does not
affect
> > > optimizer at all, if you do not put ORDERED, or RULE hint.

Yeah, this is definitely a myth from RBO times. Unless you got ordered or rule hint as you said, the table order in from clause is irrelevant. If I recall correctly it matters only when CBO calculations of different join orders happen to exactly match, but I might remember wrong.

> > >
> > > My understanding is, optimizer will calculate all the possibility of
> all
> > > possible join conditions up to 80,000 or something like that, so it
> should
> > > not matter where you put your table, or which join conditions first,
> > unless
> > > it has more than 10 table joins.
> > >
> > > You imput is highly appreciated.

The minimum amount of different allowable join order permutations is _optimizer_search_limit! (! means factorial) So, by default _optimizer_search_limit is 5, thus if there's enough tables, at least 5! = 120 permutations are done, (Of course, if there's not enough tables, then less permutations are done) When this 5! is hit, but all possible permutations aren't checked yet, then calculations continue until optimizer_max_permutations value, which defaults to 80000 in 8i, maybe early 9i as well, but in 9.2.0.4 it's 2000. Oracle has optimized CBOs best join order calculations quite much, thus such high max limit isn't probably feasible in normal environment anymore (one example is Oracle Applications - few of it's complex views get very slow due increased parsing time when max permutation is 80000).

Anyway, when number of calculations has exceeded _optimizer_search_limit factorial and optimizer_max_permutations is hit, no more calculations are done and "best so far" join order is used.

Note that _optimizer_search_limit is "stronger" than optimizer_max_permutations. If you set _optimizer_search_limit to 5 (5! = 120) and optimizer_max_permutations to 10, then minimum number of permutations is still taken from _optimizer_search_limit.

To make this even more easy -> in Steve Adams' site there's a note that optimizer_search_limit is actually divided by 2 and added by 1 when calculating the real max number of permutations it allows. There are probably reasons behind that (which I unfortunately am unaware of).

There's one more glitch: If there's up to _optimizer_search_limit number of tables in join, then all join possibilities are considered immediately (that means cartesian joins are included), but if there's more tables involved, then cartesian joins are left to the very end in list of permutations, because usually they are expensive anyway.

Tanel.

> > >
> >
> > in tom kytes newest book he states that that is a myth. It worked in the
> > days of the RBO, but if you have accurate statistics Oracle will do it
for
> > you.
> >
> > its easy to test... the order matters in the RBO, but is irrelevant in
the
> > CBO(if you have accurate statistics). Atleast he took the stuff out
about
> > cache/hit ratios this time...
> >
> >
> >
> >
> >
>
>
Received on Sat Sep 20 2003 - 18:30:18 CDT