Re: sub-selects

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. I clearly remember one of our developer going ga ga over this syntax when we were on 8i :)

>
> 2. Very rarely if ever (I've personally not seen one) Oracle produces a
> better execution plan for a statement with a scalar subquery in comparison
> to an equivalent join statement. You own example actually show that.
>
> P.S. ('Scalar subquery' is SQL'92 name for this construct).

I'd agree with you here. Even if I have seen improvements it has been rarely and minor improvements. Now that does not mean that no SQL will benefit from using this syntax.

>
> Rgds.
>
>
>
> "Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote in message
> news:SSBmb.5211$4O1.5182_at_nwrdny01.gnilink.net...
> >
> > "Ryan" <rgaffuri_at_cox.net> wrote in message
> > news:pUAmb.105101$a16.101960_at_lakeread01...
> > > ive talked to some people who have used sub-queries in the 'select' part
> > of
> > > a query to answer the same questions that you do with group by's, but
> get
> > > better performance.
> > >
> > > i have yet to see examples. anyone have some? Or any real uses for
> > > sub-selects? I have yet to need them.
> > >
> > > select (select stuff from table)
> > > from table;
> > >
> > > etc...
> > >
> > --snip--
> > Ryan,
> >
> > Here is an example of subselects you talk about:
> >
> > SQL> create table t1 as select rownum objid, 'name'||rownum objname from
> > user_objects;
> > Table created.
> >
> > SQL> create table t2 as select objid, dbms_random.value(1,10) price from
> t1;
> > Table created.
> >
> > SQL> insert into t2 select objid, dbms_random.value(1,10) price from t1;
> > 1979 rows created.
> >
> > SQL> create index t1_idx on t1(objid);
> > Index created.
> >
> > SQL> create index t2_idx on t2(objid);
> > Index created.
> >
> > SQL> exec dbms_stats.gather_table_stats(user,'T1', cascade=>true);
> > PL/SQL procedure successfully completed.
> >
> > SQL> exec dbms_stats.gather_table_stats(user,'T2', cascade=>true);
> > PL/SQL procedure successfully completed.
> >
> > SQL> set autotrace traceonly exp statistics
> >
> > ***** Sub Selects
> > SQL> select t1.objid, (select max(price) from t2 where t2.objid =
> t1.objid)
> > 2 from t1
> > 3 where objname = 'name120';
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=12)
> > 1 0 SORT (AGGREGATE)
> > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2
> Bytes=50)
> > 3 2 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1
> Card=2)
> > 4 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=12)
> >
> >
> >
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 12 consistent gets
> > 0 physical reads
> > 0 redo size
> > 493 bytes sent via SQL*Net to client
> > 498 bytes received via SQL*Net from client
> > 2 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 1 rows processed
> >
> > ****** The other way
> > SQL> select t1.objid, max(t2.price)
> > 2 from t1, t2
> > 3 where t1.objid = t2.objid and t1.objname = 'name120'
> > 4 group by t1.objid
> > 5 /
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=37)
> > 1 0 SORT (GROUP BY) (Cost=10 Card=1 Bytes=37)
> > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=2
> Bytes=50)
> > 3 2 NESTED LOOPS (Cost=4 Card=2 Bytes=74)
> > 4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=12)
> > 5 3 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1
> > Card=2)
> >
> >
> >
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 11 consistent gets
> > 0 physical reads
> > 0 redo size
> > 460 bytes sent via SQL*Net to client
> > 498 bytes received via SQL*Net from client
> > 2 SQL*Net roundtrips to/from client
> > 1 sorts (memory)
> > 0 sorts (disk)
> > 1 rows processed
> >
> > Two different ways of doing the same thing.
> > ... Even though it seems that in subselects the group by clause is not
> > needed. However,
> > its basically like issuing the subselect for each row of returned parent
> > query row.
> > You must have noticed the difference in explain plan. And I guess you can
> > read explain
> > plans well enough?
> >
> >
> > Kinda reminds you of the way "EXISTS" usually works.
> >
> > :) Try some examples .. digest it ..
> > Personally it took me a while to accept this method which makes the
> > SQL statement look quite ugly.
> >
> >
> > Anurag
> >
> >
>
>
Received on Mon Oct 27 2003 - 12:35:09 CST