Re: sub-selects

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Sat, 25 Oct 2003 21:25:06 GMT
Message-ID: <SSBmb.5211$4O1.5182@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 Sat Oct 25 2003 - 16:25:06 CDT