Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Fun Challenge #2

Re: SQL Fun Challenge #2

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Thu, 4 Mar 2004 23:40:17 +1100
Message-ID: <404723b6$0$19705$afc38c87@news.optusnet.com.au>


"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
Received on Thu Mar 04 2004 - 06:40:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US