Re: join over 12 tables takes 3 secs to plan

On Thursday, January 2, 2003, at 01:40  PM, Joe Conway wrote:

> I could be wrong, but I believe Oracle uses its rule based optimizer
> by default, not its cost based optimizer.

They changed it from 9i on. The cost-based is now the default. The
recent 16-table join example I was referring to was on the cost-based
optimizer.

They actually did an amazing good job on the CBO, at least in my
experience. I caught it screwing up badly only once, only to realize
that I had forgotten to compute the statistics ... It also allows for
different plans depending on whether you want some rows fast and the
total not necessarily as fast, or all rows as fast as possible. This
also caught me off-guard initially when I wanted to peek into the first
rows returned and had to wait almost as long as the entire query to
return. (optimizing for all rows is the default)

>  A rule based optimizer will be very quick all the time, but might not
> pick the best plan all the time, because it doesn't consider the
> statistics of the data.

True. In a situation with not that many rows though even a sub-optimal
plan that takes 10x longer to execute than the possibly best (e.g., 1s
vs 0.1s), but plans 10x faster (e.g. 0.3s vs 3s), might still return
significantly sooner. Especially if some of the tables have been cached
in memory already ...

    -hilmar
--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------