인덱스 스캔 방식

#핵심 요약

공식 문서(SQL Tuning Guide 19c, Optimizer Access Paths)는 결합 인덱스 선두 컬럼이 조건에 없을 때 인덱스를 고르지 않을 수 있지만 Index Skip Scan을 고를 수 있다고 적음
각 스캔의 공식 정의 문장은 확인 불가

#필수 명령어 또는 SQL

#Index Unique Scan과 Range Scan

CREATE INDEX emp_empno ON emp (empno);          -- non unique

SELECT empno, ename, sal
  FROM emp
 WHERE empno = 7788;

결과 해석:

실습 결과: INDEX RANGE SCAN(Buffers 2) 위에 TABLE ACCESS BY INDEX ROWID BATCHED, 합계 Buffers 3

DROP INDEX emp_empno;
CREATE UNIQUE INDEX emp_empno ON emp (empno);

결과 해석:

실습 결과: 같은 조건으로 SELECT *를 하면 INDEX UNIQUE SCAN(Buffers 1)과 TABLE ACCESS BY INDEX ROWID, 합계 Buffers 2
중복이 있는 job에 Unique 인덱스를 만들면 ORA-01452가 남

퀴즈 답: PK가 주문일자 + 주문번호인데 주문번호만 조건으로 주면 같은 주문번호가 여러 날짜에 있을 수 있어 한 건을 보장할 수 없으므로 Range Scan이 됨

#Index Full Scan

CREATE INDEX emp_job ON emp (job);
ALTER TABLE emp MODIFY job CONSTRAINT emp_job_nn NOT NULL;

SELECT job, COUNT(*)
  FROM emp
 GROUP BY job;

결과 해석:

실습 기록: 인덱스만 만든 상태에서는 여전히 TABLE ACCESS FULL이었고, 실습에서는 이어서 job에 NOT NULL 제약을 걸어 Index Full Scan을 확인하게 했음
공식 문서도 인덱스는 NULL 항목을 담지 않으므로 인덱스 컬럼에 NULL이 있으면 행 수를 세는 데 인덱스를 쓸 수 없다고 적음(Full Table Scan과 인덱스 접근)
실습 다른 답은 WHERE deptno IS NOT NULL을 붙이고 index 힌트를 줬으며 "Index Full Scan은 정렬된 결과를 보장해 SORT ORDER BY가 나오지 않는다"고 설명했음

실습 대용량 실습(10만 건 emp, emp(ename, sal) 인덱스, ORDER BY ename):

실습 결론: Index Full Scan은 한 블록씩 읽어 블록 양이 많아지면 Table Full Scan보다 크게 느려질 수 있음
주석의 분포 목표(SAL > 9000 약 1%, 6%)와 결과 6000건이 서로 맞지 않음

#Index Fast Full Scan

SELECT /*+ index_ffs(emp emp_job) */ job, COUNT(*)
  FROM emp
 WHERE job IS NOT NULL
 GROUP BY job;

결과 해석:

설명: Index Full Scan보다 빠른 이유는 Multiblock I/O와 병렬 처리 두 가지임
실습에서는 둘을 살린 예로 다음을 실행했음

ALTER SESSION SET db_file_multiblock_read_count = 512;

SELECT /*+ index_ffs(emp emp_job) parallel_index(emp, emp_job, 4) */ job, COUNT(*)
  FROM emp
 WHERE job IS NOT NULL
 GROUP BY job;

결과 해석:

db_file_multiblock_read_count 512가 실제로 적용되는 상한은 기재 없음

#Index Skip Scan

CREATE INDEX emp_deptno_job ON emp (deptno, job);

SELECT /*+ full(emp) */ * FROM emp WHERE job = 'MANAGER';
SELECT /*+ index_ss(emp emp_deptno_job) */ * FROM emp WHERE job = 'MANAGER';

결과 해석:

실습 실무 문제(120만 건 mcustsum, 인덱스 salemm + salegb):

SELECT /*+ full(t) */ COUNT(*)
  FROM mcustsum t
 WHERE salegb = 'A';                         -- 실습 기록 3835

SELECT /*+ index_ss(t m_salemm_salegb) */ COUNT(*)
  FROM mcustsum t
 WHERE salegb = 'A';                         -- 실습 기록 302

결과 해석: 선두 컬럼 salemm의 값이 12가지뿐이라 스킵 스캔이 효과적임(퀴즈의 "선두 컬럼 종류가 적어야 한다"와 같은 원리)
mcustsum 생성 스크립트는 스캔 범위와 필터 조건에 있음
반대로 막는 힌트는 NO_INDEX_SS임

#Index Range Scan Descending과 MIN/MAX

SELECT *
  FROM emp
 WHERE empno > 0
 ORDER BY empno DESC;

결과 해석:

설명: empno에 PK가 있으면 INDEX RANGE SCAN DESCENDING으로 가장 큰 값부터 리프를 오른쪽에서 왼쪽으로 읽어 정렬을 생략함
MAX 함수로 가장 큰 값 하나만 구할 때도 인덱스 뒤에서 한 건만 읽고 멈춤(FIRST ROW, INDEX RANGE SCAN (MIN/MAX))
실습 예:

CREATE INDEX emp_x02 ON emp (deptno, sal);

SELECT deptno, dname, loc,
       (SELECT /*+ no_unnest */ MAX(sal) FROM emp WHERE deptno = d.deptno)
  FROM dept d;

결과 해석:

MIN/MAX 최적화가 깨지는 경우는 인덱스 사용을 방해하는 조건에서 다룸