조회 컬럼과 변수의 데이터 타입을 맞추자
조회조건에 적합한 인덱스가 있는데도 인덱스 스캔으로 처리 되지않고
Full Table Scan을 하는 경우가 있는데, 왜 이런 현상이 발생하는 건가요?
이러한 현상은 대부분 WHERE 절에서 사용된 컬럼과 조회 변수값의 데이터 타입(컬럼:VARCHAR2, 조건:NUMBER)다르기 때문에
발생된다.
또한, 통계정보의 의한 OPTIMIZER의 판탄오류, DB서버 파라미터 설정 중 BIND PEEKING에 의해서 발생된다.
| 스크립트생성 |
|---|
| {code:sql} 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일 때||
|{code:sql}
컬럼 데이터 타입 - 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일경우 |
|---|
| {code:sql} |
컬럼 데이터 타입 - 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 |
| INDEX RANGE SCAN | IDX_T1_01 | 1 | 3 (0) | 00:00:01 |
Predicate Information (identified by operation id):
|