oracle / ORA-00904 / 스칼라 서브쿼리 / with

2016. 1. 5. 15:47database/oracle

오라클에서 스칼라서브쿼리에서 Top-N(1) 쿼리를

 

아래와 같이 사용하면 에러가 날 것입니다.

 

아래는 사원과 같은 부서에 근무하는 동료사원중

 

아무나 한명을 가져오는 스칼라서브쿼리입니다.

 

 

 

 

 

SCOTT  PKS11 > select EMPNO,

 

  2         ENAME,

 

  3         (select ename

 

  4          from   (select ename

 

  5                  from   emp b

 

  6                  where  b.deptno = a.deptno

 

  7                  order by DBMS_RANDOM.VALUE)

 

  8          where  rownum = 1) co_emp

 

  9  from   emp a;

 

 

 

                where  b.deptno = a.deptno

 

                                  *

 

ERROR at line 6:

 

ORA-00904: "A"."DEPTNO": invalid identifier

 



 

에러가 나는 이유는 스칼라서브쿼리가 참조하는 외부테이블(a)은
스칼라서브쿼리의 메인 쿼리블럭에서는 참조할 수 있지만
스칼라서브쿼리의 서브쿼리블럭(인라인뷰)에서는 참조할 수 없기 때문입니다.

이 경우 아래와 같이 WITH 절을 이용하면 해결할 수 있습니다.

성능을 위해 조인열에 생성한 인덱스도 잘 이용되고 있는 것을 볼 수 있습니다. 

 



 

SCOTT  PKS11 > create index ix_emp_deptno on emp(deptno);

Index created.

SCOTT  PKS11 > select EMPNO,
  2         ENAME,
  3         (with v as (select ename,
  4                            deptno
  5                     from emp b
  6                     order by DBMS_RANDOM.VALUE)
  7         select ename
  8         from v
  9         where v.deptno = a.deptno
 10         and rownum = 1) co_emp
 11  from emp a;

     EMPNO ENAME      CO_EMP
---------- ---------- ----------
      7369 SMITH      SMITH
      7499 ALLEN      BLAKE
      7521 WARD       BLAKE
      7566 JONES      SMITH
      7654 MARTIN     BLAKE
      7698 BLAKE      BLAKE
      7782 CLARK      MILLER
      7788 SCOTT      SMITH
      7839 KING       MILLER
      7844 TURNER     BLAKE
      7876 ADAMS      SMITH
      7900 JAMES      BLAKE
      7902 FORD       SMITH
      7934 MILLER     MILLER

14 rows selected.

SCOTT  PKS11 > set autotrace traceonly explain
SCOTT  PKS11 > /

Execution Plan
----------------------------------------------------------
Plan hash value: 1495600694

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    14 |   182 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |               |       |       |            |          |
|   2 |   VIEW                         |               |     2 |    40 |     2   (0)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY       |               |     2 |    18 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP           |     2 |    18 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IX_EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |
|   6 |  TABLE ACCESS FULL             | EMP           |    14 |   182 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   5 - access("DEPTNO"=:B1)

 

 

 

 

 

도움이 되셨다면 공감을 부탁드립니다. ^^