Re: sub-selects

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.

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).

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 Sun Oct 26 2003 - 19:03:33 CST