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: sub-selects

Re: sub-selects

From: VC <boston103_at_hotmail.com>
Date: Mon, 27 Oct 2003 22:30:51 GMT
Message-ID: <v0hnb.44207$e01.102907@attbi_s02>


Anurag,

"Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote in message news:xzdnb.18270$1C5.3891_at_nwrdny02.gnilink.net...
> Comments below:
>
> Anurag
> "VC" <boston103_at_hotmail.com> wrote in message
news:F9_mb.37325$Fm2.16115_at_attbi_s04...
> > Hello Anurag,
> >
> > Two small comments on your nice example.
> >
> > 1. The original poster'd better use Oracle 9i whilst trying to digest
> > execution plans for scalar subqueries as Oracle 8i (8.1.7.2) produces
none.
> > E.g. for your scalar subquery, Oracle 8i would show this:
> >
> > SQL> set autot on
> > SQL> select t1.objid, (select price from t2 where t2.objid = t1.objid)
> > from t1
> > where objname = 'name120';
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
> > 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1 Bytes=11)
> >
> > ... As if table t2 does not exist at all.
> Thanks for pointing that out. I did not know that. However, just as an
addition .. this
> syntax is actually valid in 8i also.

Yes, of course. I've just meant that the execution plan is not very useful under 8i for this kind of SQL..

No argument about the rest.

Rgds. Received on Mon Oct 27 2003 - 16:30:51 CST

Original text of this message

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