Re: sub-selects

Anurag Varma wrote:

>"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
>
>
>

Thanks for the examples. No doubt these will be part of my curriculum very soon.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)