[DB/Oracle] Oracle Index

인덱스 설명을 위한 참조 그림

 

1) 인덱스란 무엇인가?

정의
인덱스(Index)는 테이블의 특정 컬럼 값을 정렬·구조화해 포인터로 해당 Row의 물리적 위치(ROWID)로 빠르게 점프하게 돕는 스키마 객체입니다.

특징 요약

• 검색 성능 향상: Full Table Scan 대신 인덱스 경로로 I/O를 크게 줄임.
• 테이블과 독립적: 생성/삭제가 테이블 데이터에 직접 영향은 없음. (단, 테이블을 삭제하면 해당 테이블의 인덱스는 함께 삭제)
• 자동 사용/유지: 옵티마이저가 자동 선택, DML 시 인덱스도 자동 갱신.
• 생성 방식:
  • 명시적: CREATE INDEX …
  • 자동: PK/UNIQUE 제약 생성 시 자동 인덱스 생성

예제

-- 명시적 생성
CREATE INDEX idx_emp_ename ON scott.emp(ename);

-- PK 제약 → 인덱스 자동 생성
ALTER TABLE scott.emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);

 

---

2) 인덱스의 이면: 오버헤드(Overhead)

• 저장 공간: 인덱스 세그먼트가 별도 공간 차지.
• DML 비용:
  • INSERT → 인덱스 키 삽입(리프 블록 split 가능)
  • UPDATE(인덱스 컬럼 변경) → 기존 엔트리 삭제 + 새 엔트리 삽입
  • DELETE → 엔트리 삭제
• 관리 비용: 통계 수집/유지, 실행계획 탐색 비용 증가.

결론: SELECT는 빠르게, DML은 느리게 만드는 트레이드오프.
“많이” 만드는 게 아니라 “잘” 만드는 것이 핵심입니다.

---

3) 인덱스 스캔 방식 

3-1) INDEX UNIQUE SCAN — 정확히 1건

PK/UNIQUE 인덱스를 = 조건으로 찾을 때.

SELECT ename, sal
  FROM scott.emp
 WHERE empno = 7369;     -- empno가 PK/UNIQUE
-- PLAN: INDEX UNIQUE SCAN (EMP_PK)

 

3-2) INDEX RANGE SCAN — 범위/접두어 다건

BETWEEN, >, <, LIKE 'ABC%' 등 범위/접두어 조건.

SELECT empno, ename, sal
  FROM scott.emp
 WHERE sal BETWEEN 1500 AND 3000;
-- PLAN: INDEX RANGE SCAN (SAL 인덱스 존재 시)

SELECT empno, ename
  FROM scott.emp
 WHERE ename LIKE 'SM%';
-- PLAN: INDEX RANGE SCAN (ENAME 인덱스 존재 시)

 

4) 결합(Composite) 인덱스 — “어디까지 유효한가?”

결합 인덱스 (A, B, C, D)일 때:

• 항상 등장하고 =로 비교되는 컬럼을 선두로.
• 범위/LIKE가 나오는 순간, 그 이후 컬럼은 Filter로만 동작.

우선순위 규칙

1. 조건문에 항상 나오는 컬럼
2. 항상 =로 비교되는 컬럼
3. 둘 다 해당하면 정렬/소트 순서까지 고려(실무에선 분포도 좋은 컬럼을 선두에 두지만, 정렬 요구가 더 중요하면 정렬 컬럼을 앞에)

예제

-- ① 범위 등장 → 그 지점까지만 유효
WHERE A BETWEEN :a1 AND :a2 AND B=:b AND C=:c AND D=:d
-- → A까지만 인덱스 유효 (B,C,D는 Filter)

-- ② LIKE 접두어 → 그 지점까지만 유효
WHERE A=:a AND B LIKE :b||'%' AND C=:c AND D=:d
-- → A+B까지 인덱스 유효 (C,D는 Filter)

-- ③ 모두 '=' → 전 컬럼 유효
WHERE A=:a AND B=:b AND C=:c AND D=:d
-- → A+B+C+D 모두 유효 (가장 바람직)

 

5) “인덱스를 못 타는” 대표 패턴과 권고안

유형	설명	예시(문제 있는 쿼리)	권고안/대안
1. 외부적 변형	컬럼에 함수/연산	TO_CHAR(hiredate,'YYYYMM')='198102'	상수를 변환: hiredate >= DATE '1981-02-01' AND hiredate < DATE '1981-03-01' / 필요시 FBI
2. 내부적 변형	NVL/CASE 등으로 컬럼 값 자체 변경	NVL(comm,0)=0	쿼리 재작성 또는 FBI(NVL(comm,0))
3. 부정형 비교	!=, <>, NOT	deptno <> 10	긍정으로 변환: deptno IN (20,30,40) / 구조적 대안(파티션·비트맵)
4. NULL 비교	B-Tree는 NULL 저장 X	comm IS NULL	비트맵 인덱스(DW), 또는 FBI(NVL(comm,-1))

 

FBI 예시

CREATE INDEX idx_emp_hire_yyyymm ON scott.emp (TO_CHAR(hiredate,'YYYYMM'));
CREATE INDEX idx_emp_comm_nvl   ON scott.emp (NVL(comm,-1));

 

6) ORDER BY 최적화 — “정렬을 인덱스로 대체”

• ORDER BY 없으면: 읽는 즉시 스트리밍 반환(순서 보장 없음).
• ORDER BY 있으면: 정렬이 끝나야 첫 Row 반환(인덱스로 대체 가능하면 예외적으로 빠름).

예제

-- 인덱스 없이: Sort 필요(TEMP 사용 가능)
SELECT empno, ename, sal
  FROM scott.emp
 ORDER BY ename;

-- 인덱스로 대체: 정렬 생략
CREATE INDEX idx_emp_ename ON scott.emp(ename);
SELECT empno, ename, sal
  FROM scott.emp
 ORDER BY ename;
-- PLAN: INDEX FULL SCAN (정렬 없음)

 

성능 비교 질문에 대한 답
SELECT * FROM scott.emp; 가 보통 더 빠릅니다.
다만 ename 인덱스가 있으면 ORDER BY ename도 정렬비용 없이 빨라질 수 있습니다.

---

7) 다양한 사용자 검색 + 동시성·경합까지 고려

• 다양한 조건을 커버하려면 같은 Leading(TRD_DT 등)으로 중복 인덱스를 8개 만드는 것보다, 최소 개수의 결합 인덱스로 커버 범위를 넓히는 것이 정석.
• 단조 증가 키(날짜/시퀀스) 선두 인덱스는 INSERT가 오른쪽 리프 블록에 몰려 split·Hot Block 경합↑ → Lock/Deadlock 위험.
• 인덱스 split은 국지적/소규모로 발생하지만, 특정 블록에 집중되면 경합이 커짐.
• 접근 영역이 분리되도록(Leading 다양화/파티셔닝) 설계하면 동시성에 유리.

개선 아이디어

• Leading 재설계(업무 패턴·정렬 고려), 중복 인덱스 제거
• 범위 검색 많으면 파티셔닝으로 프루닝
• 필요 시 REVERSE KEY(OLTP 삽입 편중 완화; 단 범위 검색엔 부적합)

---

8) OLTP vs BATCH/DW — SQL과 인덱스는 “달라야” 한다

• OLTP: 소량 랜덤 액세스, 빠른 응답, 높은 동시성 → 인덱스 기반, Buffered I/O 유리
• BATCH/DW: 대량 처리/집계, 긴 쿼리, 동시 사용자 적음 → Full/병렬 스캔, Direct I/O 선호

같은 SQL을 두 환경에서 그대로 쓰면 병목·비효율이 생깁니다.
업무 특성에 맞춰 SQL/인덱스/I-O 전략을 분리해야 합니다.

---

9) SQL 성능 튜닝의 목표 = 비용(Cost) 절감

• I/O, CPU, 메모리(PGA/TEMP), 네트워크 비용을 줄여 응답 시간↓, 처리량↑.
• 예: Full Scan → Index 경로 / 불필요 Sort 제거 / 적절한 조인 방식 선택 / 통계 최신화 등.

---

10) ORDER BY 없는 SQL의 Fetch/PreFetch 동작 (현업 감각)

• 기본 접근: 실행 시 Segment Header, ASSM 비트맵 등 관리 블록 약 6블록 접근 후 실제 데이터 블록으로 진입.
• 클라이언트 전달 단위(PreFetch)
  • JDBC: 기본 10 Row씩 미리 가져옴(첫 Fetch는 커서 오픈 확인용으로 1 Row만).
  • SQL*Plus: SET ARRAYSIZE n 값만큼 가져옴.
• 요청-응답: Fetch마다 필요한 블록만 접근해 PreFetch 사이즈 단위로 Row를 운반.
  • 기억해둘 것: 운반 단위 ≒ PreFetch(드라이버) / ARRAY SIZE(SQL*Plus) (+ 최초 1Row)

---

11) ORDER BY 있는 SQL의 Fetch 특성

• 정렬 끝나야 첫 Row 반환(인덱스로 대체하면 예외적으로 빠르게 시작 가능).
• 인덱스 없이 대량 정렬이면 PGA 초과 → TEMP 스필 → I/O 증가.

---

12) Buffered I/O vs Direct I/O(Direct Path Read)

Buffered I/O (OLTP 친화)

• 장점: Cache Hit 시 매우 빠름, 소량 랜덤 액세스에 최적.
• 단점: 대량 스캔 시 캐시 오염·Aging-out, RAC 캐시 동기화 비용, 캐시 여부에 따른 성능 편차 큼.

Direct I/O / Direct Path Read (배치/DW 친화)

• 장점: Buffer Cache 바이패스 → 대량 스캔 시 일정하고 안정적, OLTP 캐시 간섭 최소.
  (병렬 쿼리는 기본 Direct I/O)
• 단점: 반복 참조에는 비효율, 디스크 캐시 영향은 받음.

한눈에 비교

구분	Buffered I/O	Direct I/O (Direct Path Read)
주 사용처	OLTP 소량 랜덤	배치/DW 대량 순차
경로	Buffer Cache 경유	Buffer Cache 무시, PGA 직독
장점	캐시 히트 시 매우 빠름	캐시 간섭↓, 대량 처리 성능 안정
단점	캐시 오염/RAC 동기화 비용	반복 참조 비효율, 디스크 캐시 영향
병렬	기본 아님	기본 Direct I/O

 

13) Direct Path Read — 동작과 주의점

• 무엇: Buffer Cache를 거치지 않고 PGA로 직접 읽는 방식.
  대량 Full/병렬 스캔, Index Fast Full Scan, TEMP 스필 재읽기(direct path read temp) 등에서 사용.
• 왜: 캐시 오염 방지, 큰 멀티블록 I/O + (가능 시) 비동기 I/O로 대량 처리 최적화.
• 전제: 관련 오브젝트의 체크포인트(동기화) 필요(Dirty 블록 디스크 반영 후 직독).
• 강제 유도(주의!):
  • 숨은 파라미터 "_serial_direct_read"=TRUE → 단일 프로세스 Full Scan도 직독 유도
  • 단점: 캐시 적재가 없어서 Delayed Logging Block Cleanout가 지연 → UNDO 지속 접근으로 추가 I/O 유발 가능. 즉, 무분별 사용 금지.

예시(개념)

-- 대량 집계: 병렬 + Direct Path (옵티마이저가 자동 선택)
SELECT /*+ full(t) parallel(t 8) */ deptno, SUM(sal)
  FROM big_emp t
 WHERE trd_dt BETWEEN :d1 AND :d2
 GROUP BY deptno;

 

---

14) 실제 저장: ROWID와 블록

• 오라클은 데이터를 Data Block 단위로 저장. 작은 테이블(예: scott.emp)은 하나의 블록에 모든 Row가 들어갈 수도 있음.
• 각 Row 위치는 ROWID = (FILE#, BLOCK#, ROW SEQ#) 로 확인 가능 → 디스크에서 정확히 점프 가능(가장 빠른 Access Path 중 하나).

SELECT ROWID, empno, ename
  FROM scott.emp;

 

15) 실무 체크리스트

• Leading 컬럼은 항상 나오고 = 비교되는가?
• 범위/LIKE는 뒤로 배치했는가?
• 중복 인덱스(같은 Leading) 없나?
• 함수/부정/NULL 비교는 피했거나 FBI/Bitmap으로 보완했나?
• **정렬(ORDER BY)**을 인덱스로 대체할 수 있게 설계했나?
• 통계(DBMS_STATS) 최신인가?
• OLTP vs 배치/DW 시간대·리소스 분리 설계했나?
• 대량 삽입에서 단조 증가 키 편중·split/경합 이슈는 없는가?
• Direct Path Read 강제("_serial_direct_read")는 정말 필요한 경우만 쓰나?

---

16) 마무리

이 글은 인덱스의 **개념 → 스캔 방식 → 결합 인덱스 설계 → 인덱스 미사용 패턴/대안 → ORDER BY와 Fetch 동작 → OLTP vs 배치/DW 차이 → Buffered vs Direct I/O → Direct Path Read 주의점 → 물리 저장(ROWID)**까지, 실무에서 꼭 필요한 논점들을 예제와 함께 한 번에 정리했습니다.

핵심은 단 하나입니다.

인덱스는 많이 만드는 게 아니라, 업무 패턴에 맞춘 “최소 개수의, 올바른 순서”가 성능을 만든다.
그리고, **I/O 동작(Buffered/Direct, Fetch/PreFetch, Sort)**까지 이해할 때 비로소 튜닝의 방향이 명확해집니다.