Re: SQL Fun Challenge #2

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c276tu$d1o$1_at_titan.btinternet.com...
> > Of course, I wanna see a full 10046 of that 9i SQL...
> > :)
>
> No you don't !

;) Bingo!

> big snip
> When I switched to cost based, Oracle assumed that the
> generated temporary table had the standard 8,168 rows in
> it and changed the latter part of the execution path to sort/merge
> rather than nested loop full tablescan.

Is this 9ir2? The 8168 rows, I mean?

> When I enabled dynamic sampling at level 2 for the session,
> Oracle then got a better idea of cardinality, and changed the
> plan again.

With something like this (all temp tables, all joined), where does the dynamic sampling kick-in? I mean, how does it decide at which point to do a sample and of which? Is there a rule somewhere?

> Any which way, it's not really designed to be efficient -
> only to be informative.

Darn right it was informative! Thanks a lot.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam