인덱스 스캔 7가지 종류

개요

---

 오라클에서 인덱스 스캔 방식이 많이 있는데 7가지를 정리해 보겠습니다. 오라클 옵티마이저가 자신이 판단할 때 최선의 선택을 하겠지만, 가끔 비효율적인 인덱스 스캔을 한다면 7가지를 알아두고 적절한 인덱스 스캔을 적용하면 개선할 수 있습니다.

 

 

그전에 먼저 인덱스가 적용되기 위한 WHERE 절 가공에 대한 사전 지식을 알아보겠습니다.

 

WHERE절의 좌변을 가공하지 마라

---

인덱스 칼럼이 제대로 적용이 되기 위해서는 WHERE 절의 좌변을 절대로 가공해서는 안됩니다. 우변을 가공해야 합니다.

 

 

• 숫자형 컬럼 인덱스 가공

SELECT ename, sal*12 FROM emp WHERE sal * 12 = 36000; (X)

-> SELECT ename, sal*12 FROM emp WHERE sal = 36000/12; (O)

 

 

• 문자형 컬럼 인덱스 가공

SELECT ename, job FROM emp WHERE substr(job, 1, 5)='SALES'; (X)

-> SELECT ename,job FROM emp WHERE job like 'SALES%'; (O)

 

 

• 날짜형 컬럼 인덱스 가공

SELECT ename, hiredate FROM emp WHERE to_char(hiredate, 'RRRR') = '1981'; (X)

-> SELECT ename, hiredate FROM emp WHERE hiredate BETWEEN to_date('1981/01/01' 'RRRR/MM/DD')
and to_date('1981/12/31', 'RRRR/MM/DD') + 1; (O)

 

 

 

ORACLE 인덱스 스캔 방법 7가지

---

	인덱스 액세스 방법	인덱스 스캔 방법
1	index range scan	index
2	index unique scan	index
3	index skip scan	index_ss
4	index full scan	index_fs
5	index fast full scan	index_ffs
6	index merge scan	and_equal
7	index bitmap merge scan	index_combine

 

 

 

index range scan

---

range scan이란, index 값이 unique가 아니라 중복을 허용할 때, 2번 이상 범위를 탐색하는 방법입니다.

 

 

• 숫자형 컬럼 인덱스 range scan

CREATE INDEX EMP_SAL ON EMP(SAL);

SELECT ename, sal FROM emp WHERE sal = 1600;

 

 

 

• 문자형 컬럼 인덱스 range scan

CREATE INDEX EMP_ENAME ON EMP(ENAME);

SELECT ename, sal FROM emp WHERE ename = 'SCOTT';

 

 

• 중복 데이터 칼럼

CREATE INDEX EMP_JOB ON EMP(JOB);

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 

 

index unique scan

---

index range scan은 중복될 수 있어서 복수 개를 조회하지만, index unique scan은 값이 하나만 존재하는 것을 보장하기 때문에, 1번만 조회합니다.

 

 

아래 SELECT에서는 ename과 empno 중 어느 것을 먼저 조회할까요?

 

CREATE UNIQUE INDEX EMP_EMPNO ON EMP(EMPNO);

SELECT empno, ename, sal FROM emp WHERE ename='SCOTT' AND empno=7788;

 

ORACLE 옵티마이저는 UNIQE INDEX를 가지고 있는 empno를 먼저 조회합니다. 왜냐하면 empno는 유일한 값을 보장하므로 1번만 조회하면 되기 때문입니다.

 

 

 

index full scan

---

index full scan은 인덱스 테이블을 모두 스캔합니다.

 

index full scan의 성능을 보기 전에 table full scan을 먼저 확인해 보겠습니다. 아무런 인덱스가 없다고 가정합니다.

 

SELECT ename, sal FROM emp where ename = 'JONES';

 

 

 아무런 인덱스가 없기 때문에 실행 계획은 TABLE ACCESS FULL입니다.

 

• 결합 칼럼 인덱스로 개선하기

성능을 개선하기 위해 2개 이상의 칼럼으로 구성되는 결합 칼럼 인덱스 idx_sal_ename을 생성합니다. 주의 할 점은 결합 칼럼 인덱스는 순서가 중요합니다.

 

SELECT ename, sal FROM emp where ename = 'JONES';

CREATE INDEX EMP_SAL_ENAME ON EMP(SAL, ENAME);

 

index full scan

 

 위의 SQL문은 어떤 인덱스 전략을 사용할까요?

 

 ename이 EMP_SAL_ENAME 인덱스를 구성하는 칼럼이기 때문에 index range scan을 할 것이라고 생각할 수 있지만 아닙니다! JONES 칼럼은 sal이 먼저 정렬된 이후에 정렬이 되므로, 결국 ename으로 WHERE절에서 검색한다면 모든 인덱스 테이블을 검색하는 index full scan을 합니다. 더 좋은 성능으로 인덱스를 사용하려면 EMP_SAL_ENAME 인덱스는 ENAME을 첫 칼럼으로 만들었어야 합니다. 혹은 sal 기준으로 WHERE 절을 검색하면 index range scan이 가능합니다. 인덱스 예시는 아래와 같습니다.

 

• SELECT에 있는 모든 칼럼이 인덱스 칼럼에 속한 경우

SELECT ename, sal FROM EMP WHERE ename='JONES'; --SELECT 칼럼이 모두 인덱스 칼럼에 존재

 

인덱스 칼럼으로 모든 SELECT 조회 가능하기 때문에, 별도로 전체 테이블에 접근하지 않고 인덱스 테이블에서 조회를 끝냅니다. 따라서 TABLE ACCESS BY INDEX ROWID * 가 필요 없습니다.

 

SELECT /*+ gather_plan_statistics INDEX_FS(EMP EMP_SAL_ENAME) */ ename, sal
FROM EMP
WHERE ename='JONES';

 

이는 또한 직접적으로 힌트인 INDEX_FS(EMP EMP_SAL_ENAME)을 사용해, 강제로 index full scan을 하도록 만들 수 있습니다.

 

• SELECT에 있는 모든 칼럼이 인덱스 칼럼에 있지 않은 경우

SELECT ename, sal, job FROM EMP WHERE ename='JONES'; --job 칼럼은 인덱스 칼럼에 존재 X

 

 

이 경우에는 sal, ename만 인덱스 칼럼에 있고 job이 없기 때문에, ROWID를 통해 테이블을 추가로 검색해서 job을 조회합니다.

 

⚠️ 결합 칼럼 인덱스를 사용할 때 주의할 점 ⚠️

- 가급적 결합 칼럼 인덱스의 첫번째 칼럼은 WHERE 절에 오는 칼럼을 사용하도록 합니다.
- 첫번째 칼럼이 아니라면, 특정 범위만 스캔하는 range가 아닌 모든 인덱스 테이블을 검색하는 full을 합니다.

 

 

index skip scan

---

index skip scan은 결합 칼럼 인덱스의 첫 칼럼이 WHERE에 있지 않아도, 인덱스를 이용할 수 있는 방식입니다. 경우에 따라 index full scan을 개선해 사용할 수 있습니다.

 

CREATE ON EMP_DEPTNO_JOB ON (DEPTNO, JOB);

 

 결합 칼럼 인덱스의 첫 번째 칼럼인 DEPTNO가 WHERE절에 있지 않고 두번째 칼럼인 JOB이 WHERE절에 job='MANAGER'로 있습니다. JOB은 두 번째 칼럼이므로 index full scan을 합니다.

 

index full scan vs index skip scan

 

 index skip scan을 사용하면 순차적으로 DEPTNO와 WHERE절에 해당하는 job='MANAGER'를 스캔하다가 더 이상 없으면  나머지를 skip 하고 다음 DEPTNO로 이동하여 스캔합니다. skip을 할 수 있는 이유는 job도 결합 칼럼 인덱스의 두 번째 칼럼으로 알파벳 순으로 정렬되어 있기 때문입니다.

 

SELECT /*+ gather_plan_statistics INDEX_SS(EMP EMP_DEPTNO_JOB) */
    ename, deptno, sal
FROM EMP
WHERE job = 'MANAGER';

 

 

• INDEX 힌트

SELECT /*+ gather_plan_statistics INDEX(EMP EMP_SAL_JOB) */
    ename, deptno, sal
FROM EMP
WHERE sal BETWEEN 950 and 3000
and job = 'MANAGER';

 

 

 

• INDEX_SS 힌트

SELECT /*+ gather_plan_statistics INDEX_SS(EMP EMP_SAL_JOB) */
    ename, deptno, sal
FROM EMP
WHERE sal BETWEEN 950 and 3000
and job = 'MANAGER';

 

 

 

index fast full scan

---

 index fast full scan은 full scan을 해야 할 때, index full scan보다 속도가 더 향상된 것으로, 싱글 I/O 방식이 아닌 multi-block I/O 방식으로 한 번에 많은 데이터를 조회하는 방법입니다. 이 방법은 꼭 인덱스에 NOT NULL 제약이 필요하며 조회 결과 정렬을 보장하지 않습니다.

 

CREATE INDEX EMP_JOB ON EMP(JOB);

 

NOT NULL을 하는 방법은 2가지가 있습니다.

 

• WHERE 조건문에 NOT NULL 걸기

SELECT /*+ gather_plan_statistics index_ffs(emp emp_job) */ 
job, count(*)
from emp WHERE job IS NOT NULL
group by job;

 

• 테이블 제약 조건에서 NOT NULL 추가하기

ALTER TABLE EMP MODIFY JOB NOT NULL;

 

 

 

 DB 쿼리문을 사용하면, Database Buffer Cache에 캐시 데이터를 저장합니다. single-block I/O는 논리적으로 저장되어 있는 데이터를 순차적으로 한 블록씩만 읽기 때문에 정렬을 보장하지만 상대적으로 느립니다. multi-block I/O는 물리적으로 저장되어 있는 데이터를 한번에 읽으므로 I/O 횟수가 줄어들어 속도는 빠르지만 정렬을 보장하지 않습니다. 주로 GROUP BY를 활용한 쿼리문에서 사용됩니다.

 

• index full scan vs index fast full scan

	index full scan	index fast full scan
I/O 방식	single block I/O	multi block I/O
정렬	보장함	보장 안함
속도	상대적으로 느림	상대적으로 빠름
병렬읽기	지원 안함	지원함

 

 

 

index merge scan

---

여러 개의 인덱스를 사용하여 하나의 인덱스만 사용했을 때보다 테이블 액세스를 줄이는 방법입니다.

 

 

CREATE INDEX EMP2_COL1 ON EMP2(COL1);
CREATE INDEX EMP2_COL2 ON EMP2(COL2);

 

UPDATE EMP2 SET COL1='A' WHERE NUM1 BETWEEN 1 AND 917504;
UPDATE EMP2 SET COL1='B' WHERE NUM1 BETWEEN 917505 AND 1835008;
UPDATE EMP2 SET COL2='C' WHERE NUM1 BETWEEN 1 AND 917404;
UPDATE EMP2 SET COL2='D' WHERE NUM1 BETWEEN 917405 AND 1835008;

 

SELECT /*+ index(emp2 emp2_col1) */
count(*) FROM emp2 WHERE col1='A' and col2 ='D';

 

SELECT 쿼리문으로 100건이 조회되는데, 인덱스 EMP2_COL1을 기준으로 일반 index range scan으로 검색되는 경우, 아래와 같이 불필요한 접근이 많이 발생합니다.

 

불필요하게 많은 테이블 접근

emp2_col1 인덱스에서 COL1이 A인 것을 찾아 emp2 테이블을 모두 접근합니다.

 

하지만 여기서 EMP2_COL1과 EMP2_COL2를 모두 활용한 index merge scan을 사용하면, 테이블 접근이 훨씬 줄어듭니다.

 

SELECT /*+ and_equal(emp2 emp2_col1 emp2_col2) */count(*) 
FROM emp2 WHERE col1='A' and col2 ='D';

 

 

 따라서, 아래와 같이 emp2_col1 인덱스와 emp2_col2 인덱스에서 해당하는 ROWID를 먼저 찾고 emp2 테이블에 액세스 합니다. 이전에 각 인덱스 테이블과 emp2 데이블의 비교를 위해 수십만 번의 액세스를 해야 했지만 merge scan으로  2번만 emp2 테이블에 액세스 하면 됩니다.

 

 

 

index bit map merge scan

---

index merge scan에서 한 단계 더 발전해서 인덱스 테이블을 비트맵 인덱스 테이블로 변환해서 스캔하는 방법입니다.

비트맵으로 변환하고 접근하기 때문에 일반적으로 index merge scan보다 우수한 속도를 보입니다.

 

 

 

SELECT /*+ index_combine(emp2) */count(*) 
FROM emp2 WHERE col1='A' and col2 ='D';

 

 

참고

[SQL 실전적용] 실무에서 바로 쓰는 SQL 튜닝법