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

튜닝 전

네 번째 예제는 View 또는 인라인 뷰 안에 인덱스로 존재하는 조인컬럼이 가공되어 있어 성능이 나빠지는 사례이다.

SQL 구조를 보면 EMP_V 인라인 뷰에 DATE 타입으로 되어 있는 HIREDATE 컬럼에 시분초를 제거하고 싶어 'YYYYMMDD'로 표현을 하였다.

문제는 인덱스로 존재하는 HIREDATE 컬럼을 TO_CHAR()로 변경한 상태로 인라인 뷰 밖에서 해당 컬럼으로 조건을 주었기 때문에 조건이 인라인 뷰 안으로 침투가 안되서 FULL SCAN을 하고 있다.

이 문제는 사례 첫번째에서 본 Case와 유사한데 첫번째 내용은 데이터 타입이 서로 틀려 내부적 형변환을 오라클이 시도했지만 지금 Case는 View 구성 자체를 처음부터 잘 못 만들었기 때문에 외부적 형변환이라 볼 수 있다.

또한 Predicate Information의 Id 2번째 내용에서도 HIREDATE 컬럼을 가공하여 인덱스를 사용하지 못하고 있음을 확인할 수 있다.

 
SQL> EXPLAIN PLAN FOR
     SELECT * 
     FROM  (SELECT EMPNO, JOB, 
                   TO_CHAR(HIREDATE, 'YYYYMMDD') HIREDATE, DEPTNO
            FROM   EMP) EMP_V
     WHERE  EMP_V.HIREDATE BETWEEN :B1 -- 20090101
                           AND     :B2 -- 20090131

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 25010 |   439K|  9802   (8)| 00:01:58 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  | 25010 |   439K|  9802   (8)| 00:01:58 |
---------------------------------------------------------------------------

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

   1 - filter(:B1<=:B2)
   2 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'YYYYMMDD')>=:B1
              AND TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'YYYYMMDD')<=:B2)

튜닝 후

결국 이 문제는 인라인 뷰 안에서 가공된 컬럼을 인라인 뷰 밖에서 조건으로 사용했기 때문에 발생된 문제인데, 왜 이런 비효율이 생긴것인지 유추를 해보면 다음과 같다.

개발자는 어플리케이션에서 HIREDATE 값을 문자타입(20100101)으로 보여주도록 프로그램을 구성하였고 이를 DB단계에서 매칭시키기 위해 해당 컬럼을 TO_CHAR() 함수를 통해 형변환을 시켰을 것이다.

하지만 이런 구현 방법으로 인해 DB 단계에서는 인덱스가 있음에도 불구하고 사용하지 못하고 FULL SCAN을 하여 수행속도가 오래 걸리는 원인이 되었던 것이다.

이제 원인이 나왔으니 해결방법 또한 찾을 수 있을 것이다.

즉, DB 단계(인라인 뷰)에서 인덱스를 사용할 수 있도록 HIREDATE를 그대로 사용하고 어플리케이션 단계에서 문자타입으로 조건값을 받는것이 아닌 날짜타입으로 조건값을 받으면 된다.

 
SQL> EXPLAIN PLAN FOR
     SELECT EMPNO, JOB,
            TO_CHAR(HIREDATE, 'YYYYMMDD') HIREDATE, DEPTNO    
     FROM   (SELECT EMPNO, JOB, HIREDATE, DEPTNO       
             FROM   EMP) EMP_V
     WHERE  EMP_V.HIREDATE BETWEEN :B1 -- 2009/01/01 00:00:00
                           AND     :B2 -- 2009/01/31 23:59:59

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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        | 25010 |   439K|   274   (1)| 00:00:04 |
|*  1 |  FILTER                      |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    | 25010 |   439K|   274   (1)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 | 45019 |       |   123   (1)| 00:00:02 |
---------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:B1)<=TO_DATE(:B2))
   3 - access("HIREDATE">=:B1 AND "HIREDATE"<=:B2)

실제 위의 내용처럼 조건값을 날짜 타입으로 받고 Select-List 단계에서 TO_CHAR(HIREDATE, 'YYYYMMDD')로 변환한 뒤 인라인 뷰에서는 인덱스 컬럼 그대로 변경하였다.

그 결과 실행계획에서는 인덱스 EMP_N2를 사용할 수 있게 되었고 Predicate Information 3번 단계에서 이를 확인할 수 있다.