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: Q: an Inline SELECT forming a column

Re: Q: an Inline SELECT forming a column

From: <fitzjarrell_at_cox.net>
Date: 26 Oct 2006 15:11:59 -0700
Message-ID: <1161900719.564365.23810@f16g2000cwb.googlegroups.com> 





Comments embedded.


klabu wrote:


> Please give me an example of this type of query:

>

> SELECT....(SELECT col_x FROM some_table where....)

> FROM......


>

> i.e. the inline SELECT forms a column - I'm not sure whether the inline SQL

> returns a scalar value or many rows

>




SQL> select ename, empno, deptno, (select dname from dept where deptno
= emp.deptno) d_name


  2  from emp;



ENAME           EMPNO     DEPTNO D_NAME



---------- ---------- ---------- --------------

SMITH            7369         20 RESEARCH
ALLEN            7499         30 SALES
WARD             7521         30 SALES
JONES            7566         20 RESEARCH
MARTIN           7654         30 SALES
BLAKE            7698         30 SALES
CLARK            7782         10 ACCOUNTING
SCOTT            7788         20 RESEARCH
KING             7839         10 ACCOUNTING
TURNER           7844         30 SALES
ADAMS            7876         20 RESEARCH

ENAME           EMPNO     DEPTNO D_NAME
---------- ---------- ---------- --------------
JAMES            7900         30 SALES
FORD             7902         20 RESEARCH
MILLER           7934         10 ACCOUNTING





14 rows selected.



SQL>




> and what is the name of this type of query ?




Such queries are correlated subqueries. They must be correlated or
you'll get an error:



SQL> select ename, empno, deptno, (select dname from dept /*where
deptno = emp.deptno*/) d_name


  2  from emp;



select ename, empno, deptno, (select dname from dept /*where deptno =
emp.deptno*/) d_name

                              *




ERROR at line 1:


ORA-01427: single-row subquery returns more than one row



Re-writing the query to use an in-line view instead:



SQL> select e.ename, e.empno, e.deptno, d.d_name
  2  from emp e, (select deptno, dname d_name from dept) d
  3  where d.deptno = e.deptno;



ENAME           EMPNO     DEPTNO D_NAME



---------- ---------- ---------- --------------

SMITH            7369         20 RESEARCH
ALLEN            7499         30 SALES
WARD             7521         30 SALES
JONES            7566         20 RESEARCH
MARTIN           7654         30 SALES
BLAKE            7698         30 SALES
CLARK            7782         10 ACCOUNTING
SCOTT            7788         20 RESEARCH
KING             7839         10 ACCOUNTING
TURNER           7844         30 SALES
ADAMS            7876         20 RESEARCH

ENAME           EMPNO     DEPTNO D_NAME
---------- ---------- ---------- --------------
JAMES            7900         30 SALES
FORD             7902         20 RESEARCH
MILLER           7934         10 ACCOUNTING





14 rows selected.



SQL>


But which one is more efficient?



SQL> set autotrace traceonly


SQL> select ename, empno, deptno, (select dname from dept where deptno
= emp.deptno) d_name


  2  from emp;



14 rows selected.




Execution Plan




Plan hash value: 1571744956





| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
 |




|   0 | SELECT STATEMENT  |      |    14 |   140 |     3   (0)|

00:00:01 |


|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)|

00:00:01 |


|   2 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)|

00:00:01 |





Predicate Information (identified by operation id):





   1 - filter("DEPTNO"=:B1)




Statistics



          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
        945  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed





SQL> select e.ename, e.empno, e.deptno, d.d_name
  2  from emp e, (select deptno, dname d_name from dept) d
  3  where d.deptno = e.deptno;



14 rows selected.




Execution Plan




Plan hash value: 615168685





| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
  |




|   0 | SELECT STATEMENT   |      |    14 |   294 |     7  (15)|

00:00:01 |


|*  1 |  HASH JOIN         |      |    14 |   294 |     7  (15)|

00:00:01 |


|   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)|

00:00:01 |


|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)|

00:00:01 |





Predicate Information (identified by operation id):





   1 - access("DEPTNO"="E"."DEPTNO")




Statistics



          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        945  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed





SQL>


10.2.0.2, current statistics on all tables.  You eliminate a hash join
with your syntax and gain 1 second in time, which could add up with a
more involved query or a larger result set.



But how about some subquery factoring?



SQL> with dp_name as (select deptno, dname d_name from dept)
  2  select ename, empno, emp.deptno, d_name
  3  from emp, dp_name


  4  where dp_name.deptno = emp.deptno;



ENAME           EMPNO     DEPTNO D_NAME



---------- ---------- ---------- --------------

SMITH            7369         20 RESEARCH
ALLEN            7499         30 SALES
WARD             7521         30 SALES
JONES            7566         20 RESEARCH
MARTIN           7654         30 SALES
BLAKE            7698         30 SALES
CLARK            7782         10 ACCOUNTING
SCOTT            7788         20 RESEARCH
KING             7839         10 ACCOUNTING
TURNER           7844         30 SALES
ADAMS            7876         20 RESEARCH

ENAME           EMPNO     DEPTNO D_NAME
---------- ---------- ---------- --------------
JAMES            7900         30 SALES
FORD             7902         20 RESEARCH
MILLER           7934         10 ACCOUNTING





14 rows selected.



Execution Plan




Plan hash value: 615168685





| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
  |




|   0 | SELECT STATEMENT   |      |    14 |   294 |     7  (15)|

00:00:01 |


|*  1 |  HASH JOIN         |      |    14 |   294 |     7  (15)|

00:00:01 |


|   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)|

00:00:01 |


|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)|

00:00:01 |





Predicate Information (identified by operation id):





   1 - access("DEPTNO"="EMP"."DEPTNO")




Statistics



          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        945  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed





SQL>


Hmm, that's even worse than the in-line view.  More readable, I think,
but less efficient.  Apparently if you're going for efficiency (at
least with this query) the first example is the query to write.  I'd be
checking query plans, though, on any query you write in such a manner;
the plans could be worse for such a query given the size of the tables
and the result set returned.



> 

> thanks

> 

> -- 

> 10gR2





David Fitzjarrell
Received on Thu Oct 26 2006 - 17:11:59 CDT












Original text of this message













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