Chap01. 02 인덱스 기본 원리

01. Index 사용이 불가능하거나 범위 스캔이 불가능한 경우

---

 

흔히 Index를 탄다는 말을 하는데 그 말은 무엇일까요?

 

EMP Table에 어떤 Index가 생성되어있는지 확인합시다.

 

SELECT A.TABLE_NAME
      ,A.INDEX_NAME
      ,A.COLUMN_NAME
  FROM ALL_IND_COLUMNS A
 WHERE A.TABLE_NAME = 'EMP'
 ORDER BY A.INDEX_NAME, A.COLUMN_NAME;

 

EMP Table에 생성된 Index

 

제 로컬에 설치한 Oracle DB의 EMP Table에는 위 Index가 생성되어있습니다.

 

	1번 INDEX	2번 INDEX
INDEX 명	EMP_X01	PK_EMP
Key 컬럼	DEPTNO	EMPNO

 

 

어떠한 Query를 실행하였을 때 실행계획을 확인할 수 있습니다.

Query 와 실행계획

 

중간부분에 INDEX RANGE SCAN이라는 부분이 보이시나요?

PK_EMP 이름의 Index를 이용하여 Index Range Scan를 수행한 모습을 볼 수 있습니다.

이를 흔히 Index를 탄다(사용) 라고 말하는 것으로 이해가 됩니다.

 

만약 Index를 안탓다고 하면 실행계획 모양은 아래와 같을 것입니다. (Index 부분 빠지면서 Table Full Scan)

실행계획

 

 

본론으로.... 그럼 Index가 존재하는데 Index가 못타는 경우를 확인해보도록 하죠.

※여기서 못탄다는 의미는 Index를 타지 않거나 범위스캔(Index Range Scan)이 안될 때를 말합니다.

 

 

1) 인덱스 컬럼의 좌변가공(못탈때)

Trim 사용한 Query

 

    위의 Query랑 비슷하지만 WHERE절에 TRIM 사용으로 좌변가공이 되었고,

    실행계획 상에 Index Range Scan 확인이 안됩니다.

 

    좌변가공이란 WHERE 절에 적힌 조건컬럼을 가공하는 점을 말합니다.

 

2) Null의 검색(못탈때)

null 검색 시

    WHERE절에 조회조건으로 NULL 사용 시 입니다. 마찬가지로 INDEX RANGE SCAN이 안나타나군요.

 

    Oracle은 Index 구성 컬럼이 모두 NULL인 레코드는 저장하지 않는다는 부분이 기억이 나십니까?

    어쩌면 당연할 수도 있겠군요.

 

    예로 IS NULL을 사용한 Query이지만 Range Scan을 타는 경우가 있습니다.

    EMPNO 처럼 PK컬럼에 대해 IS NULL 조건으로 검색 시 INDEX를 정상적으로 사용할 수 있습니다.

    그러나 이는 Query 자체에 모순이 있습니다.

 

 

3) 묵시적 형변환(범위스캔X)

 

자 묵시적형변환이란?

 

TT라는 Table이 존재하고 아래와 같습니다.

TT Table

위 Table로 쿼리를 실행하였을 때

SELECT EMPNO

FROM TT

WHERE EMPNO = 10;

 

EMPNO 컬럼이 VARCHAR2인 문자형이지만 Query 상에 EMPNO = 10; 가 이상하게 보입니다.

Oracle Query에서 문자 인식할 때는 싱글쿼테이션(')을 사용해야하는데

숫자형으로 작성하여도 조회가 되기 때문이다.

 

이유는 Oracle 내에서 묵시적형변환이 일어났기 때문이다.

올바른 Query는 아래 입니다.

SELECT EMPNO

FROM TT

WHERE EMPNO = '10';

 

[테스트]

 

둘다 Index를 사용하였지만

하나는 Full Scan으로써 Index Leaf Block을 전체 탐색하였다고 생각하시면되겠습니다.

하나는 Unique Scan이 확인됩니다. Index Leaf Block을 하나만 콕 집어서 탐색하였다고 생각하시면 되겠습니다.

(답답하시더라도 다음 시간에 설명하겠습니다.)

 

어짜피 Index를 탄거니까 된거 아니냐란 생각이 들수도있지만

전체 탐색과 하나의 탐색은 어마무시한 차이가 존재합니다.  

 

 

 

4) 부정형 일때

 

 

 

    Query에 <>, !=, IS NOT NULL 등 부정형 사용 시 Index 사용이 없음을 볼 수 있습니다.

 

 

※ 이렇게 예시를 들었지만 중요한 점은 위에는 Index의 범위스캔이 불가능하지만

     Index의 사용자체는 불가능하지 않습니다. 즉 Query의 작성에 따라

     Index Full Scan은 사용할 수 있습니다.

 

     이말을 하는 이유는 어쩌면 Table Full Scan 하는 것 보다 Index Full Scan 사용이 더 좋을 때가

     있기 때문입니다.

 

 

 

그렇다면 위와 같은 상황이 발생안하도록 Query 작성하는 방법은 무엇일까요?

02. Index 컬럼의 가공

---

[그림1]

 

    - 컬럼의 NOT NULL, NULL 허용 컬럼인지 확인 중요

    - 좌변가공된 부분을 같은 결과를 나타내도록 Query 변환

    - 묵시적 형변환 발생안하도록 Query 작성 시 주의, Table 생성 시 자료형 체크

    - 좌변가공으로 인해 좌변가공된 형태로 함수기반 인덱스 생성하여 사용은 권장사항 X