• 이러한 현상은 대부분 WHERE 절에서 사용된 컬럼과 조회 변수값의 데이터 타입(컬럼:VARCHAR2, 조건:NUMBER)다르기 때문에 발생된다.
• 또한, 통계정보의 의한 OPTIMIZER의 판탄오류, DB서버 파라미터 설정 중 BIND PEEKING에 의해서 발생된다.

스크립트생성

CREATE TABLE T1 AS
SELECT 1000000-(LEVEL-1) C1
      ,TO_CHAR(SYSDATE-(LEVEL-1),'YYYYMMDD') C2
 FROM DUAL
CONNECT BY LEVEL <= 1000000

CREATE INDEX IDX_T1_01 ON T1(C1);
CREATE INDEX IDX_T1_02 ON T1(C2);

BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'BSHMAN'
                                ,TABNAME=>'T1'
                                ,ESTIMATE_PERCENT=>99
                                ,METHOD_OPT=>'FOR ALL INDEXED COLUMNS'
                                ,GRANULARITY=>'ALL'
                                ,CASCADE=>TRUE
                                ,NO_INVALIDATE=>FALSE);
END;

테스트[1]. VARCHAR2(8) 타입인 C2를 조회하는 변수의 타입이 NUMBER일 때

컬럼 데이터 타입 - VARCHAR2(8)
변수 데이터 타입 - NUMBER
SELECT *
 FROM T1
WHERE C2= 20110124

Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |   647  (10)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    26 |   647  (10)| 00:00:08 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   1 - filter(TO_NUMBER("C2")=20110124)  ----> 내부에서 컬럼변형으로 인덱스를 사용하지못함
   

테스트[2].NUMBER 타입인 C1을 조회하는 변수의 타입이 VARCHAR2일경우

컬럼 데이터 타입 - NUMBER
변수 데이터 타입 - VARCHAR2

SELECT *
 FROM T1
WHERE C1='111111';
 
Plan hash value: 3018735338
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    13 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_01 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"=111111)     <---- 컬럼 변형없이 인덱스 스캔처리