Re: SELECT col_a, (SELECT col_b .....

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 21 Jul 2001 21:29:56 GMT
Message-ID: <994408774.8982.0.nnrp-10.9e984b29@news.demon.co.uk>

You are correct that many select statements of the (select as column) form could be rewritten differently. However, there may be some differences in the optimizer's ability to execute them.

Unfortunately "explain plan" tells you NOTHING about how Oracle is executing the query, but it seems, from some experiments I have done, that the path is always a nested loop for each row returned from the main select
statement - which is not necessarily the path that Oracle would take otherwise.

An interesting corollary to your example is the following:

select

        col1,
        decode( {select statement returning 1 or 0 rows},
                        null, {different select statement 1}
                               {different select statement 2}
        )

from tab1
where ....

You can decode() (or CASE) the return from the 'column select' statement, and if it found no data the return is deemed to be a NULL, not an error.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Frank wrote in message


>Hi!

>Oracle 8.1.6/Windows2000

>

>I have found that Oracle now handles queries with the form:

>SELECT col_a,

>               (SELECT col_b FROM table_b WHERE find only one row)

>FROM table_a a

>WHERE find stuff;

>

>What is this construction called ? (so I can search for it in the

>documentation)

>How does Orcale optimize this ?

>It seems to me that a expression like this could be rewritten to a "old"

>query like:

>SELECT col_a, col_b

>FROM table_a a, table_b

>WHERE a.id=b.id(+);

>

>Is that happening behind the scenery?

>

>Frank

>

>

>

>

>

Received on Sat Jul 21 2001 - 16:29:56 CDT