꿈꾸는 개발자, DBA 커뮤니티 구루비

마지막 여섯 번째 예제는 View 또는 인라인 뷰 안에 ROWNUM이 존재하여 성능이 나빠지는 사례이다.

일반적으로 ROWNUM은 추출된 레코드에 Sequence 처럼 순차적인 번호를 부여하기 위한 구문인데, 문제는 ROWNUM을 인라인 뷰 안에 사용을 했기 때문에 성능 문제가 발생한 Case이다.

튜닝 전

해당 SQL을 보면 개발자가 순위를 부여하기 위해 EMP_V 인라인 뷰 안에 ROWNUM을 넣었고 인라인 뷰 밖에서 DEPTNO가 10인 대상들만 가져오도록 구성을 하였다.

문제는 'DEPTNO = 10' 조건으로 DEPT 테이블을 액세스한 뒤 조인키인 DEPTNO 컬럼이 EMP_V 인라인 뷰 안으로 침투되어 해당 데이터들만 가져온 뒤에 ROWNUM을 부여하는 것이 좋다.

하지만 ROWNUM이 존재하게 되면 오라클의 특성 상 ROWNUM이 존재하는 집합을 먼저 만들고 인라인 뷰 밖의 조건(DEPTNO 조인키)을 필터시키기 때문에 문제가 된다.

문제가 되는 이유는 EMP 테이블에 천만건의 데이터가 있는데 이 천만건을 모두 액세스한 뒤 9건밖에 존재하지 않는 'DEPTNO = 10' 데이터를 필터링 시키기 때문이며, 실행계획에서도 이러한 문제를 Id 3번를 보면 access가 아닌 filter로 처리된다는 것을 확인할 수 있다.

 
SQL> EXPLAIN PLAN FOR
     SELECT EMP_V.*
     FROM  (SELECT ROWNUM, EMPNO, JOB, HIREDATE, DEPTNO       
           FROM EMP) EMP_V,  DEPT D
     WHERE  EMP_V.DEPTNO = D.DEPTNO
       AND  D.DEPTNO     = 10;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  9993K|   562M|  9255   (3)| 00:01:52 |
|   1 |  NESTED LOOPS        |         |  9993K|   562M|  9255   (3)| 00:01:52 |
|*  2 |   INDEX UNIQUE SCAN  | DEPT_U1 |     1 |     6 |     0   (0)| 00:00:01 |
|*  3 |   VIEW               |         |  9993K|   505M|  9255   (3)| 00:01:52 |
|   4 |    COUNT             |         |       |       |            |          |
|   5 |     TABLE ACCESS FULL| EMP     |  9993K|   171M|  9255   (3)| 00:01:52 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."DEPTNO"=10)
   3 - filter("EMP_V"."DEPTNO"=10)

튜닝 후

이를 해결하기 위해서는 결국 문제가 되는 ROWNUM을 인라인 뷰 밖으로 가져오면 되며, 실제 그렇게 변경할 경우 아래처럼 조인조건이 인라인 뷰 안으로 침투가 되어 인덱스를 사용하는 것을 확인할 수 있다.

 
SQL> EXPLAIN PLAN FOR
     SELECT ROWNUM, EMP_V.*
     FROM   (SELECT EMPNO, JOB, HIREDATE, DEPTNO       
             FROM   EMP) EMP_V,  DEPT D
     WHERE  EMP_V.DEPTNO = D.DEPTNO
       AND  D.DEPTNO     = 10;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    21 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    21 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN          | DEPT_U1 |     1 |     3 |     0   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    18 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_N1  |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("D"."DEPTNO"=10)
   4 - access("DEPTNO"=10)