오라클 성능고도화 2권

Chap01. 02 인덱스 기본 원리

Tech_Wave 2021. 7. 14. 22:05

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) 인덱스 컬럼의 좌변가공(못탈때)

  • 위의 Query랑 비슷하지만 WHERE절에 TRIM 사용으로 좌변가공이 되었고, 실행계획 상에 Index Range Scan 확인이 안됩니다.
  • 좌변가공이란 WHERE 절에 적힌 조건컬럼을 가공하는 점을 말합니다.

Trim 사용한 Query

 

 

2) Null의 검색(못탈때)

  • WHERE절에 조회조건으로 NULL 사용 시 입니다. 마찬가지로 INDEX RANGE SCAN이 안나타나군요.
  • Oracle은 Index 구성 컬럼이 모두 NULL인 레코드는 저장하지 않는다는 부분이 기억이 나십니까? 어쩌면 당연할 수도 있겠군요.
  • 예로 IS NULL을 사용한 Query이지만 Range Scan을 타는 경우가 있습니다.
    • EMPNO 컬럼이 NOT NULL로 설정되어있고 WHERE 조건절에 EMPNO IS NULL로 찾을 때, 이는 Query 자체에 모순이 있습니다.

null 검색 시

 

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 사용이 없음을 볼 수 있습니다.

 

  • 위에서 인덱스를 사용못하는 4가지 경우를 예시로 들었지만, 중요한 점은 위 예시는 Index의 범위스캔이 불가능하지만 Index의 사용자체는 불가능하지는 않다는 점입니다. 즉 Query의 작성에 따라 Index Full Scan은 사용할 수 있습니다.
  • 어쩌면 Table Full Scan 하는 것 보다 Index Full Scan 사용이 더 좋을 때가 있기 때문입니다.
  • 인덱스를 사용못하는 경우
    1. 좌변가공
    2. IS NULL 검색
    3. 묵시적형변환
    4. 부정형 검색

 

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

02. Index 컬럼의 가공

[그림1]

 

  • 컬럼의 NOT NULL, NULL 허용 컬럼인지 확인 중요
  • 좌변가공된 부분을 같은 결과를 나타내도록 Query 변환
  • 묵시적 형변환 발생안하도록 Query 작성 시 주의, Table 생성 시 자료형 체크
  • 좌변가공으로 인해 좌변가공된 형태로 함수기반 인덱스 생성하여 사용은 권장사항 X
반응형