Oracle Tuning 강좌
데이터타입 불일치 예제 0 2 10,908

by 강정식 DBMS_XPLAN.DISPLAY DBMS_XPLAN [2010.05.31]


튜닝 전

  첫 번째 예제는 데이터타입이 틀릴 경우 인덱스를 사용하지 못하는 내용이다.

  아래의 내용을 보면, EMP 테이블에 EMPNO_VARCHAR 컬럼으로 조건값 111이 들어가며, EMP 테이블에는 EMPNO_VARCHAR 컬럼으로 인덱스가 있기 때문에 빠르게 액세스를 할 수 있을 것이다.

  그러나, 플랜을 확인한 결과 인덱스가 있음에도 불구하고 TABLE ACCESS FULL을 하며 예상 시간(Time)은 2분 28초나 나왔다.

 
SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM   EMP E
     WHERE  E.EMPNO_VARCHAR = 111;

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

-- PLAN 정보
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 | 12327   (3)| 00:02:28 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    26 | 12327   (3)| 00:02:28 |
--------------------------------------------------------------------------

-- Predicate Information 정보
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("E"."EMPNO_VARCHAR")=111)
    

  이처럼, 인덱스가 있음에도 불구하고 TABLE ACCESS FULL을 하는 이유는 바로 조건값을 111로 주었기 때문이다

  EMPNO_VARCHAR 컬럼의 데이터타입은 VARCHAR2 타입이고 111은 숫자타입이므로 서로 직접 비교를 할 수 없기 때문에, 오라클에서 이 둘을 비교하고 위해 내부적 형변환을 시도하였다.

  문제는, EMPNO_VARCHAR 문자 컬럼을 숫자와 비교하기 위해 TO_NUMBER() 함수를 사용하여 숫자로 변경하였고, 이로 인해 EMPNO_VARCHAR 컬럼으로 인덱스는 존재하지만 TO_NUMBER(EMP_VARCHAR) 컬럼으로 인덱스는 존재하지 않기 때문에 FULL SCAN을 할 수밖에 없다.

  XPLAN 툴은 이렇게 내부적 형변환이 일어난 것에 대해 쉽게 확인할 수 있으며, 위에서 보는 것처럼 Predicate Information 절에서 'filter(TO_NUMBER("E"."EMPNO_VARCHAR")=111)'으로 확인이 가능하다.

튜닝 후

  결국 내부적 데이터 형변환 때문에 인덱스를 사용하지 못하고 있으므로, 인덱스로 생성되어 있는 컬럼의 데이터타입에 맞추어 조건값을 넣으면 된다.

  아래와 같이 데이터 타입이 맞게 조건절을 수정 한 후 Predicate Information을 확인해 보자

 
SQL> EXPLAIN PLAN FOR
     SELECT *
     FROM EMP E
     WHERE E.EMPNO_VARCHAR = '111';

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

-- PLAN 정보
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_N3 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


-- Predicate Information 정보
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."EMPNO_VARCHAR"='111')
    

  데이터 타입에 맞추어 조건값을 111 숫자가 아닌 '111'로 넣고 XPLAN을 확인한 결과 EMP_N3 인덱스를 사용하였고 Time 또한 00:01초밖에 안걸리는 것으로 확인되었다.

  이처럼, 데이터 타입이 틀릴 경우 내부적 형변환으로 인해 인덱스를 사용하지 못 할 수 있으므로 반드시 Predicate Information 절을 확인해야 한다.

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

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

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

by 이영호 [2010.10.08 10:55:43]
좋은정보 감사합니다.

by 참된신자 [2014.08.13 11:24:52]

감사합니다 :)

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