인덱스 스캔 방식

옵티마이저는 조건절과 인덱스 구성에 따라 여러 인덱스 스캔 방식 중 하나를 고른다. 각 방식이 언제 선택되는지, 그리고 언제 인덱스를 정상적으로(Range Scan으로) 쓸 수 없는지가 시험의 핵심이다. 인덱스 구조는 인덱스 구조와 탐색을 먼저 참고한다.

스캔 방식 한눈에 보기

Index Range Scan

B*Tree 인덱스의 가장 일반적인 액세스 방식이다. 루트에서 리프까지 수직적 탐색 후, 필요한 범위만 수평적 탐색한다.

CREATE INDEX EMP_DEPTNO_IDX ON EMP (DEPTNO);

SELECT * FROM EMP WHERE DEPTNO = 20;

| Id | Operation                           | Name           |
|  0 | SELECT STATEMENT                    |                |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |
|* 2 |   INDEX RANGE SCAN                  | EMP_DEPTNO_IDX |

Index Full Scan

수직적 탐색 없이(정확히는 맨 왼쪽 리프까지만 내려가) 리프 블록 전체를 처음부터 끝까지 수평 탐색한다.

CREATE INDEX EMP_IDX ON EMP (ENAME, SAL);

SELECT * FROM EMP WHERE SAL > 2000 ORDER BY ENAME;

| Id | Operation                   | Name    |
|  0 | SELECT STATEMENT            |         |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |
|* 2 |   INDEX FULL SCAN           | EMP_IDX |
Predicate Information
   2 - filter("SAL">2000)

• 선두 컬럼(ENAME)이 조건절에 없어 Range Scan이 불가능할 때, 차선책으로 선택된다.
• 테이블보다 인덱스가 훨씬 작으므로, 인덱스 전체를 읽어 필터링 후 소량만 테이블을 액세스하면 Table Full Scan보다 유리할 수 있다.
• 결과 집합이 인덱스 순으로 정렬된다. 위 예처럼 ORDER BY ENAME의 소트 연산을 생략하기 위해, 또는 first_rows 모드에서 부분범위 처리를 위해 선택되기도 한다.

Index Unique Scan

수직적 탐색만으로 데이터를 찾는다. Unique 인덱스를 = 조건으로 탐색할 때 동작한다.

CREATE UNIQUE INDEX PK_EMP ON EMP (EMPNO);

SELECT ENAME FROM EMP WHERE EMPNO = 7788;

| Id | Operation                   | Name   |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|* 2 |   INDEX UNIQUE SCAN         | PK_EMP |

시험 포인트: Unique 결합 인덱스라도 일부 컬럼만 = 조건으로 검색하거나, 범위 조건(BETWEEN, 부등호, LIKE)으로 검색하면 Index Unique Scan이 아니라 Index Range Scan으로 처리된다. 예: PK가 (주문일자, 주문번호)인데 주문일자만 조건으로 주면 Range Scan.

Index Skip Scan

Oracle 9i부터 도입되었다. 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 방식이다. 루트·브랜치 블록 정보로 조건을 만족할 가능성이 있는 리프 블록만 골라 액세스하고 나머지는 건너뛴다.

CREATE INDEX 사원_IDX ON 사원 (성별, 연봉);

SELECT * FROM 사원 WHERE 연봉 BETWEEN 2000 AND 4000;

| Id | Operation                   | Name    |
|  1 |  TABLE ACCESS BY INDEX ROWID| 사원     |
|* 2 |   INDEX SKIP SCAN           | 사원_IDX |

내부적으로 성별='남' AND 연봉 BETWEEN ...과 성별='여' AND 연봉 BETWEEN ... 두 범위를 각각 찾아가는 것과 비슷하게 동작한다.

• 선두 컬럼에 부등호·BETWEEN·LIKE 같은 범위 조건이 있고 중간 컬럼 조건이 없을 때도 Skip Scan을 쓸 수 있다. 예: 인덱스 (일자, 업종, 지역)에 일자 BETWEEN ... AND 지역 = ....
• 선두 컬럼의 값 목록이 적고 명확하다면, 성별 IN ('남', '여')처럼 조건을 명시해 IN-List Iterator로 처리하는 방법도 있다. Skip Scan은 옵티마이저 판단에 의존하므로 이 방식이 더 확실하다.
• 힌트: INDEX_SS(테이블 인덱스), NO_INDEX_SS

시험 포인트: Index Skip Scan은 "선두 컬럼이 조건절에 없을 때"도 사용 가능하며, 선두 컬럼의 카디널리티가 낮을수록 효과적이다. "선두 컬럼 Distinct 값이 많을수록 유리하다"는 보기는 틀리다.

Index Fast Full Scan

인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O로 빠르게 읽는다. Table Full Scan과 같은 방식으로 인덱스를 읽는 것이다.

CREATE INDEX EMP_IDX ON EMP (ENAME, SAL);

SELECT /*+ INDEX_FFS(EMP EMP_IDX) */ COUNT(*)
FROM   EMP
WHERE  SAL > 2000;

| Id | Operation             | Name    |
|  0 | SELECT STATEMENT      |         |
|  1 |  SORT AGGREGATE       |         |
|* 2 |   INDEX FAST FULL SCAN| EMP_IDX |

• 브랜치 블록도 함께 읽지만 필요 없으므로 버린다.
• 인덱스가 디스크에 많이 있을 때(캐시되지 않았을 때) 효과가 크다.
• 힌트: INDEX_FFS(테이블 인덱스), NO_INDEX_FFS

시험 포인트: Index Fast Full Scan은 정렬 순서를 보장하지 않으며, 인덱스에 없는 컬럼을 하나라도 참조하면 사용할 수 없다. "Index Fast Full Scan 결과는 인덱스 키 순으로 정렬된다"는 틀린 문장이다.

Index Range Scan Descending

Index Range Scan과 같은 방식이지만 뒤에서 앞쪽으로 스캔하여 내림차순 결과를 얻는다.

SELECT * FROM EMP WHERE EMPNO > 0 ORDER BY EMPNO DESC;

| Id | Operation                    | Name   |
|  1 |  TABLE ACCESS BY INDEX ROWID | EMP    |
|* 2 |   INDEX RANGE SCAN DESCENDING| PK_EMP |

• 리프 블록이 양방향 연결 리스트라서 가능하다. 내림차순 정렬 요구에 대해 별도 소트 없이 결과를 낸다.
• MAX 값을 구할 때 인덱스를 거꾸로 읽어 첫 레코드만 읽고 멈춘다 (FIRST ROW / MIN/MAX 처리).

CREATE INDEX EMP_X02 ON EMP (DEPTNO, SAL);

SELECT DEPTNO, DNAME, LOC,
       (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)
FROM   DEPT D;

|  1 |  SORT AGGREGATE              |         |
|  2 |   FIRST ROW                  |         |
|* 3 |    INDEX RANGE SCAN (MIN/MAX)| EMP_X02 |

• 힌트: INDEX_DESC(테이블 인덱스)

Index Combine과 기타 방식

|  1 |  TABLE ACCESS BY INDEX ROWID      | EMP     |
|  2 |   BITMAP CONVERSION TO ROWIDS     |         |
|  3 |    BITMAP AND                     |         |
|  4 |     BITMAP CONVERSION FROM ROWIDS |         |
|* 5 |      INDEX RANGE SCAN             | EMP_X1  |
|  6 |     BITMAP CONVERSION FROM ROWIDS |         |
|* 7 |      INDEX RANGE SCAN             | EMP_X2  |

• IN-List Iterator: WHERE DEPTNO IN (10, 20)은 = 조건을 값 개수만큼 반복 수행하는 방식(INLIST ITERATOR)으로 처리된다. 결합 인덱스에서의 활용은 인덱스 스캔 효율화와 설계에서 다룬다.
• OR-Expansion: OR 조건을 UNION ALL 형태로 분기해 각각 인덱스를 사용하게 하는 변환이다(USE_CONCAT 힌트). 자세한 내용은 쿼리 변환을 참고한다.

인덱스를 Range Scan 할 수 없는 경우

인덱스는 정렬되어 있으므로 시작점과 끝점을 찾을 수 있어야 Range Scan이 가능하다. 아래 경우는 시작점을 찾을 수 없어 인덱스를 아예 못 쓰거나, Index Full Scan처럼 비효율적으로만 쓸 수 있다.

1. 인덱스 선두 컬럼을 가공

-- 나쁨: 인덱스 컬럼에 함수 적용
WHERE SUBSTR(ENAME, 1, 2) = '김민'
WHERE TO_CHAR(HIREDATE, 'YYYYMMDD') = '20240101'
WHERE SAL * 12 > 50000
WHERE NVL(COMM, 0) < 100

-- 좋음: 컬럼은 그대로 두고 상수 쪽을 변형
WHERE ENAME LIKE '김민%'
WHERE HIREDATE >= TO_DATE('20240101','YYYYMMDD')
  AND HIREDATE <  TO_DATE('20240102','YYYYMMDD')
WHERE SAL > 50000 / 12

부득이하게 가공해야 하면 함수기반 인덱스(FBI)를 만든다: CREATE INDEX EMP_X3 ON EMP (UPPER(ENAME));

2. 묵시적 형변환

컬럼과 상수의 데이터 타입이 다르면 DBMS가 자동으로 형변환하는데, 그 대상이 인덱스 컬럼 쪽이 되면 컬럼을 가공한 것과 같다.

-- EMPNO_CHAR는 VARCHAR2 컬럼
WHERE EMPNO_CHAR = 7788
-- 실제 처리: TO_NUMBER(EMPNO_CHAR) = 7788  → 인덱스 Range Scan 불가

Predicate Information
   1 - filter(TO_NUMBER("EMPNO_CHAR")=7788)

실행계획 Predicate Information에 컬럼을 감싼 TO_NUMBER, TO_CHAR, INTERNAL_FUNCTION이 보이면 형변환이 일어난 것이다. 실행계획 읽는 법은 실행계획을 참고한다.

3. 부정형 비교

WHERE DEPTNO <> 30
WHERE DEPTNO != 30
WHERE DEPTNO NOT IN (10, 20)

부정형 조건은 범위의 시작점과 끝점을 정할 수 없어 Range Scan이 불가능하다. 인덱스에 필요한 컬럼이 모두 있다면 Index Full Scan이나 Fast Full Scan은 가능하다.

4. LIKE 앞에 와일드카드

WHERE ENAME LIKE '%민'    -- Range Scan 불가 (시작점 없음)
WHERE ENAME LIKE '%민%'   -- Range Scan 불가
WHERE ENAME LIKE '김%'    -- Range Scan 가능

5. OR 조건

WHERE (DEPTNO = 10 OR JOB = 'CLERK')

서로 다른 컬럼에 대한 OR 조건은 하나의 인덱스로 시작점을 정할 수 없다. 옵티마이저가 OR-Expansion으로 UNION ALL 분기를 해야 각각 인덱스를 사용할 수 있다. 같은 컬럼의 OR(DEPTNO = 10 OR DEPTNO = 20)는 IN-List로 처리할 수 있다.

6. IS NULL 조건

WHERE COMM IS NULL

Oracle은 모든 인덱스 컬럼이 NULL인 레코드를 인덱스에 저장하지 않으므로 단일 컬럼 인덱스로 IS NULL 조건을 찾을 수 없다. 단, 다음 경우는 가능하다.

• 해당 컬럼에 NOT NULL 제약이 있으면 옵티마이저는 IS NULL 결과가 공집합임을 알고 처리한다.
• 결합 인덱스 (COMM, EMPNO)처럼 다른 NOT NULL 컬럼이 포함되어 있으면 NULL 레코드도 저장되어 Range Scan 가능하다.
• IS NOT NULL 조건은 단일 컬럼 인덱스에서 Index Full Scan으로 처리할 수 있다.

7. 선두 컬럼이 조건절에 없음

인덱스 (DEPTNO, SAL)에 WHERE SAL > 2000만 있으면 Range Scan이 불가능하다. Index Skip Scan, Index Full Scan, Index Fast Full Scan 중 하나로 처리되거나 Table Full Scan이 선택된다.

시험 포인트: "인덱스 컬럼을 가공하면 인덱스를 사용할 수 없다"는 정확히 말하면 "정상적인 Range Scan을 할 수 없다"이다. Index Full Scan이나 Fast Full Scan으로는 여전히 사용할 수 있다. 숫자형 컬럼과 문자 상수 비교(WHERE SAL = '3000')는 상수 쪽이 변환되므로 인덱스 사용에 문제가 없다는 점도 함정으로 자주 나온다.

핵심 요약

• Index Range Scan은 선두 컬럼이 가공되지 않은 조건으로 사용될 때 가능하며, 성능은 스캔 범위와 테이블 액세스 횟수로 결정된다.
• Index Full Scan은 리프 전체를 순서대로 읽어 정렬된 결과를 주며, 소트 생략이나 부분범위 처리를 위해 선택된다.
• Index Unique Scan은 Unique 인덱스 전 컬럼을 = 조건으로 찾을 때만 동작한다.
• Index Skip Scan은 선두 컬럼 조건이 없을 때 쓰며, 선두 컬럼 Distinct 값이 적을수록 효과적이다.
• Index Fast Full Scan은 Multiblock I/O로 세그먼트 전체를 읽고, 병렬 처리가 가능하지만 정렬을 보장하지 않으며 필요한 컬럼이 모두 인덱스에 있어야 한다.
• Index Range Scan Descending은 내림차순 정렬과 MAX 값 처리에서 소트를 생략한다.
• 선두 컬럼 가공, 컬럼 쪽 묵시적 형변환, 부정형, LIKE 앞 %, 다른 컬럼 간 OR, IS NULL, 선두 컬럼 조건 부재 시 Range Scan이 불가능하다.