Re: Optimzer Oddities

From: Richard A Foote <richard.foote_at_tbigpond.nospam.com>
Date: Thu, 29 Apr 2004 09:55:33 GMT
Message-ID: <pi4kc.3577$TT.3317@news-server.bigpond.net.au>

"Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0404290037.674304ae_at_posting.google.com...
> I have a correlated subquery which joins 3 tables together and I'm not
> exactly sure how Oracle will resolve the query...
>
> For example...
>
> select a.name, b.location
> from emp a, contract b
> where a.emplid = b.emplid
> and a.effdt =
> ( select max(effdt) from emp c
> where c.emplid = a.emplid
> and c.start_time > b.start_time )
> and a.sal > 10000;
>

Hi Matt,

It's actually very difficult for anyone to be exactly sure how Oracle will resolve such a query as it's dependent on a number of variables (e.g. size of tables, distribution and cardinality of data, assumption tables/indexes have been analyzed, available indexes, values of various parameters, Oracle version, type of optimizer, definitions of tables or views, etc. etc.).

So my suggestion would be to find out yourself. Explain the query (e.g. trace the session and tkprof, autotrace in sqlplus, explain command, 3rd party product, etc.) look at the execution plan and bingo, the answer to your question is there before your eyes.

No guesses, no assumptions, no mistakes ...

Cheers

Richard Received on Thu Apr 29 2004 - 04:55:33 CDT