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

오라클에서 스칼라서브쿼리에서 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)

 

 

 

 

 

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