Oracle Tuning 강좌
뷰 안에 ROWNUM이 존재할 경우 1 1 99,999+

by 강정식 ROWNUM DBMS_XPLAN DBMS_XPLAN.DISPLAY_CURSOR [2010.08.25]


튜닝 전

  DBMS_XPLAN.DISPLAY 강좌 예제와 동일한 예문이지만, DBMS_XPLAN.DISPLAY_CURSOR를 사용하였다.

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

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

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

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

 
ALTER SESSION SET STATISTICS_LEVEL = ALL;

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;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS        |         |      1 |   9991K|      9 |00:00:25.47 |   44243 |   7482 |
|*  2 |   INDEX UNIQUE SCAN  | DEPT_U1 |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|*  3 |   VIEW               |         |      1 |   9991K|      9 |00:00:25.47 |   44242 |   7482 |
|   4 |    COUNT             |         |      1 |        |     10M|00:00:10.00 |   44242 |   7482 |
|   5 |     TABLE ACCESS FULL| EMP     |      1 |   9991K|     10M|00:00:10.00 |   44242 |   7482 |
---------------------------------------------------------------------------------------------------

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

튜닝 후

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

 
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;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  COUNT                        |         |      1 |        |      9 |00:00:00.01 |       7 |
|   2 |   NESTED LOOPS                |         |      1 |      1 |      9 |00:00:00.01 |       7 |
|*  3 |    INDEX UNIQUE SCAN          | DEPT_U1 |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      1 |      9 |00:00:00.01 |       6 |
|*  5 |     INDEX RANGE SCAN          | EMP_N1  |      1 |      1 |      9 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------

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

- 강좌 URL : http://www.gurubee.net/lecture/2146

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 참된신자 [2014.08.14 09:16:43]

감사합니다 :)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입