인덱스 스캔 7가지 종류

개요

---

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

 

 

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

 

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

---

• 숫자형 컬럼 인덱스 가공

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

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

 

 

• 문자형 컬럼 인덱스 가공

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

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

 

 

• 날짜형 컬럼 인덱스 가공

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

-> 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;

 

 

 

 

 

인덱스 스캔 방법 7가지

 

 

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';

 

 

• 중복 데이터 컬럼 index range scan

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

---

table full scan은 다음과 같습니다. 아무런 인덱스가 없다고 가정합니다

 

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

 

 

결합 칼럼 인덱스의 첫번째 칼럼이 아닌 다른 칼럼의 데이터를 검색할 때 인덱스 테이블을 전부 스캔합니다.

결합칼럼 인덱스의 첫번째는 sal, 두번째는 ename인데, SELECT에서 조회는 두번째 칼럼인 ename을 기준으로 합니다.

 

만약에 첫번째 칼럼인 sal을 했으면 index range scan을 하지만, 두번째 칼럼이 WEHRE절 기준으로 왔기 때문에 index full scan을 합니다.

 

 

인덱스는 한번에 복수개를 생성할 수 있습니다. sal, ename 2개를 생성해 봅니다.

 

CREATE INDEX EMP_SAL_ENAME ON EMP(SAL, ENAME);

 

 

• 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을 조회합니다.

 

 

index skip scan

---

index skip scan은 결합 칼럼 인덱스의 첫 칼럼이 WHERE에 있지 않아도, 인덱스를 이용할 수 있는 방식입니다.

 

CREATE ON EMP_DEPTNO_JOB ON (DEPTNO, JOB);

 

 

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

 

결합 칼럽 인덱스의 첫번째 칼럼은  SAL이지만 job='MANAGER' 에서 JOB은 두번째 칼럼이므로 index full scan을 해야 하는 상황입니다.

 

index skip scan을 사용하면, DEPTNO의 각 값에서(10,20,30) WHERE절에 해당하는 job='MANAGER' 가 있으면 스캔합니다. 더이상 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 방식이 아닌 multiblock 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;

 

 

multiblock I/O는 다음과 같이, 여러 개의 행을 한번에 읽습니다.

 

 

DB 쿼리문을 사용하면, Database Buffer Cache에 캐시를 해둡니다. 이 때, sing block i/o는 한블럭씩만 읽는 것이고, multi block i/o는 한번에 여러개의 블럭을 읽습니다. 따라서, 여러개의 블럭을 읽을 때 I/O가 훨씬 줄어듭니다.

 

• 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 테이블에 엑세스 합니다. 이전에 수십만건의 엑세스를 해야했지만, 이제는 단순하게 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 튜닝법