Oracle 인덱스 스캔 비교: 실행 계획과 클러스터링 팩터로 성능 읽기

1장. 인덱스를 사용했는데 왜 느린가#

직원 테이블에 100만 행이 있습니다.

다음 SQL을 실행했습니다.

SELECT
    emp_id,
    emp_name,
    salary
FROM employee
WHERE dept_id = 20;

실행 계획에는:

INDEX RANGE SCAN

이 표시됩니다.

개발자는 말합니다.

인덱스를 탔으니 문제없습니다.

그런데 응답시간은 여전히 느립니다.

확인해 보니 부서 20에는:

700,000명

이 있습니다.

100만 행 가운데 70만 행을 가져와야 합니다.

인덱스를 사용하더라도:

인덱스에서 70만 ROWID 탐색
↓
테이블 블록 반복 접근
↓
70만 행 반환

이 필요할 수 있습니다.

따라서:

인덱스를 사용했는가?

보다 더 중요한 질문은:

실제로 몇 행과 몇 블록을 읽었는가?

입니다.

2장. 실행 계획의 연산자 이름은 성능 점수가 아니다#

Oracle 실행 계획에서 다음과 같은 연산자를 볼 수 있습니다.

INDEX UNIQUE SCAN

INDEX RANGE SCAN

INDEX FULL SCAN

INDEX FAST FULL SCAN

INDEX SKIP SCAN

TABLE ACCESS FULL

이를 다음처럼 순위로 외우면 위험합니다.

UNIQUE
최고

RANGE
좋음

FULL
나쁨

TABLE ACCESS FULL
최악

실제 성능은 그렇게 단순하지 않습니다.

3장. 한 행 조회와 70만 행 조회는 전혀 다른 작업이다#

다음 쿼리를 비교해 보겠습니다.

사번 한 명 조회#

SELECT *
FROM employee
WHERE emp_id = 1001;

결과:

1행

특정 부서 전체 조회#

SELECT *
FROM employee
WHERE dept_id = 20;

결과:

700,000행

둘 다 인덱스를 사용할 수 있습니다.

하지만 작업량은 크게 다릅니다.

4장. 인덱스 접근 방식은 조건과 결과 범위에 따라 달라진다#

대표적인 예를 정리하면 다음과 같습니다.

조건 가능한 접근 방식 핵심 의미
emp_id = 1001 INDEX UNIQUE SCAN 유일 키 한 건 탐색
dept_id = 10 AND salary > 500 INDEX RANGE SCAN 연속된 키 범위 탐색
ORDER BY dept_id, salary INDEX FULL SCAN 인덱스 순서대로 전체 탐색 가능
COUNT(*) 등 인덱스만으로 해결 INDEX FAST FULL SCAN 인덱스를 순서 없이 넓게 읽음
salary > 500 on (dept_id,salary) INDEX SKIP SCAN 가능 선두 키 그룹을 건너가며 탐색
대부분의 행 반환 TABLE ACCESS FULL 가능 전체 스캔이 더 저렴할 수 있음

5장. INDEX UNIQUE SCAN — 유일한 키 한 건 찾기#

다음 기본키가 있다고 하겠습니다.

PRIMARY KEY (emp_id)

쿼리:

SELECT
    emp_id,
    emp_name,
    salary
FROM employee
WHERE emp_id = 1001;

Oracle은 상황에 따라:

INDEX UNIQUE SCAN

을 선택할 수 있습니다.

6장. UNIQUE SCAN은 하나의 키 값에 대응하는 엔트리를 찾는다#

기본키 또는 UNIQUE 인덱스에서:

emp_id = 1001

은 최대 한 행입니다.

개념적으로:

루트 블록
↓
브랜치 블록
↓
리프 블록
↓
ROWID

를 찾습니다.

7장. 하지만 인덱스만 읽고 끝나는지는 별도 문제다#

인덱스가:

emp_id

만 가지고 있습니다.

SELECT는:

emp_name

salary

도 필요합니다.

따라서 실행 계획은 개념적으로:

TABLE ACCESS BY INDEX ROWID
  INDEX UNIQUE SCAN

처럼 나타날 수 있습니다.

인덱스는 행의 위치를 찾고 실제 컬럼은 테이블에서 가져옵니다.

8장. UNIQUE SCAN이 항상 전체 쿼리 비용이 가장 작은 것은 아니다#

한 행을 찾는 쿼리에서는 매우 효율적인 경우가 많습니다.

하지만 다음과 같은 문제가 있을 수 있습니다.

락 대기

스토리지 지연

원격 DB 링크

함수 호출

애플리케이션 네트워크

즉 INDEX UNIQUE SCAN이라는 연산자 하나만 보고 전체 요청 성능을 판단하면 안 됩니다.

9장. INDEX RANGE SCAN — 일정 범위의 키를 읽는다#

복합 인덱스:

CREATE INDEX employee_dept_salary_idx
ON employee (
    dept_id,
    salary
);

가 있다고 하겠습니다.

쿼리:

SELECT
    emp_id,
    emp_name,
    salary
FROM employee
WHERE dept_id = 10
  AND salary > 500;

이 경우 INDEX RANGE SCAN이 후보가 될 수 있습니다.

10장. 선두 컬럼을 고정하고 다음 컬럼의 범위를 읽는다#

인덱스 순서:

dept_id
→ salary

입니다.

조건:

dept_id = 10

으로 한 범위를 찾고:

salary > 500

인 부분을 연속적으로 읽을 수 있습니다.

11장. 하지만 조건에 맞는 행이 너무 많으면 비용이 커진다#

부서 10:

전체 직원
100,000명

그중 급여 500 초과:

90,000명

이라면 인덱스로 9만 개 엔트리를 읽은 뒤 테이블까지 접근할 수 있습니다.

이 경우:

INDEX RANGE SCAN

이라는 이름이 있다고 해서 가벼운 작업이라는 뜻은 아닙니다.

12장. 선택도를 함께 보자#

전체 부서 10 직원:

100,000

조건 만족:

1,000

이라면 선택도는:

1%

입니다.

반면:

90,000 / 100,000
=
90%

라면 대부분의 행을 읽습니다.

인덱스의 유용성은 조건의 선택도와 관련이 있습니다.

13장. TABLE ACCESS BY INDEX ROWID 비용이 커질 수 있다#

인덱스에서 찾은 ROWID가:

10,000개

라고 하겠습니다.

필요한 컬럼이 인덱스에 없으면 각 ROWID를 이용해 테이블 블록에서 데이터를 가져옵니다.

문제는 이 행들이 물리적으로 얼마나 모여 있느냐입니다.

14장. 1만 행이 100블록에 모여 있는 경우#

예:

블록 1
100행

블록 2
100행

...

블록 100
100행

이라면 1만 행이 비교적 적은 블록에 모여 있습니다.

범위 조회에서 테이블 접근 효율이 좋을 수 있습니다.

15장. 1만 행이 거의 모두 다른 블록에 흩어진 경우#

예:

행 1
블록 5

행 2
블록 800

행 3
블록 17

행 4
블록 2,300

처럼 흩어져 있다면 많은 블록을 반복 방문할 수 있습니다.

여기서 클러스터링 팩터가 중요한 단서가 됩니다.

16장. 클러스터링 팩터란 무엇인가#

Oracle의 클러스터링 팩터는 인덱스 키 순서대로 ROWID를 따라갈 때:

테이블 블록이 얼마나 자주 바뀌는가

의 경향을 나타내는 통계입니다.

단순화하면:

비슷한 인덱스 키
→ 같은 테이블 블록에 모여 있음
→ 낮은 CF 방향

입니다.

반대로:

비슷한 인덱스 키
→ 여러 테이블 블록에 흩어짐
→ 높은 CF 방향

입니다.

17장. 클러스터링 팩터는 상대적으로 읽어야 한다#

테이블:

행 수
1,000,000

블록 수
20,000

이라고 하겠습니다.

인덱스 A의 CF:

25,000

이라면 블록 수에 비교적 가깝습니다.

인덱스 B:

900,000

이라면 행 수에 더 가깝습니다.

일반적으로 A 쪽이 인덱스 순서와 테이블 배치가 더 잘 맞는 경향을 보입니다.

18장. CF가 낮다는 이유만으로 모든 쿼리가 빠른 것은 아니다#

다음 쿼리가:

전체 행의 80%

를 반환한다면 클러스터링 팩터가 낮더라도 많은 데이터를 읽어야 합니다.

또 필요한 컬럼이 모두 인덱스 안에 있다면 테이블을 방문하지 않을 수 있습니다.

따라서 CF는:

단독 성능 점수

가 아닙니다.

19장. CF는 실제 범위 크기와 함께 봐야 한다#

예:

CF 낮음

반환 행
10건

과:

CF 낮음

반환 행
500,000건

은 완전히 다른 작업입니다.

항상:

행 수

테이블 블록 수

CF

조회 범위

추가 테이블 접근

을 함께 봅니다.

20장. INDEX FULL SCAN — 인덱스를 키 순서대로 전체 읽기#

다음 인덱스:

(dept_id, salary)

가 있습니다.

쿼리:

SELECT
    dept_id,
    salary
FROM employee
ORDER BY
    dept_id,
    salary;

Oracle이 상황에 따라 INDEX FULL SCAN을 선택할 수 있습니다.

21장. INDEX FULL SCAN은 인덱스 키 순서를 활용할 수 있다#

인덱스를 정렬 순서대로 읽기 때문에:

dept_id

salary

순서가 필요한 경우 별도 정렬을 줄일 가능성이 있습니다.

하지만 실제로 정렬 제거가 가능한지는 전체 계획과 정렬 방향을 확인해야 합니다.

22장. 전체 인덱스를 읽지만 테이블 전체보다 작을 수 있다#

테이블에:

50개 컬럼

이 있고 인덱스에는:

2개 컬럼

만 있다고 하겠습니다.

전체 행을 처리하더라도 인덱스 구조가 더 작기 때문에 인덱스를 읽는 것이 유리할 수 있습니다.

23장. INDEX FAST FULL SCAN — 인덱스를 순서 없이 넓게 읽기#

다음 쿼리를 생각해 보겠습니다.

SELECT COUNT(*)
FROM employee;

적절한 조건에서 인덱스만으로 필요한 정보를 얻을 수 있다면:

INDEX FAST FULL SCAN

이 선택될 수 있습니다.

24장. FAST FULL SCAN과 FULL SCAN은 이름이 비슷하지만 다르다#

INDEX FULL SCAN:

인덱스 키 순서대로 읽음

INDEX FAST FULL SCAN:

인덱스 전체를 순서 보장 없이 넓게 읽음

으로 이해할 수 있습니다.

25장. FAST FULL SCAN은 ORDER BY를 자동으로 해결하지 않는다#

다음 SQL:

SELECT
    dept_id,
    salary
FROM employee
ORDER BY
    dept_id,
    salary;

에 INDEX FAST FULL SCAN이 나온다고 해서:

이미 정렬되어 있으니 SORT 없음

이라고 생각하면 안 됩니다.

FAST FULL SCAN은 인덱스 키 순서를 결과 순서로 보장하는 접근이 아닙니다.

26장. FAST FULL SCAN은 멀티블록 I/O와 병렬 처리에 유리할 수 있다#

순서가 중요하지 않은 전체 인덱스 읽기에서는 Oracle이 효율적으로 많은 블록을 처리할 수 있습니다.

따라서:

인덱스 전체를 읽는다

는 사실만으로 나쁜 계획이라고 판단하면 안 됩니다.

27장. INDEX SKIP SCAN — 선두 컬럼 조건이 없어도 인덱스를 활용할 수 있다#

복합 인덱스:

(dept_id, salary)

가 있습니다.

쿼리:

SELECT
    emp_id
FROM employee
WHERE salary > 500;

선두 컬럼:

dept_id

조건이 없습니다.

예전 규칙만 외우면:

선두 컬럼이 없으니 인덱스 사용 불가

라고 생각하기 쉽습니다.

하지만 Oracle은 경우에 따라 INDEX SKIP SCAN을 사용할 수 있습니다.

28장. SKIP SCAN은 선두 키 값을 여러 그룹처럼 탐색한다#

부서가:

10

20

30

세 종류뿐이라고 하겠습니다.

개념적으로:

dept 10 범위에서 salary > 500 탐색

dept 20 범위에서 salary > 500 탐색

dept 30 범위에서 salary > 500 탐색

처럼 접근할 수 있습니다.

29장. 선두 컬럼의 값 종류가 적을수록 후보가 될 수 있다#

선두 컬럼 종류:

3개

와:

100,000개

는 큰 차이가 있습니다.

10만 그룹을 반복 탐색해야 한다면 SKIP SCAN 비용이 커질 수 있습니다.

따라서:

선두 컬럼 NDV

가 중요한 단서가 됩니다.

30장. SKIP SCAN이 가능하다는 것과 좋은 선택이라는 것은 다르다#

급여 500 초과 직원이:

전체의 80%

라면 여러 부서 범위를 탐색한 뒤 결국 대부분의 인덱스 엔트리를 읽을 수 있습니다.

또 필요한 컬럼이 인덱스 밖에 있다면 테이블 접근도 추가됩니다.

따라서:

SKIP SCAN 사용
=
성능 개선 성공

이 아닙니다.

31장. “선두 컬럼 없으면 인덱스를 못 쓴다”는 규칙은 지나치게 단순하다#

다음 접근들이 존재할 수 있습니다.

INDEX SKIP SCAN

INDEX FULL SCAN

INDEX FAST FULL SCAN

따라서 정확한 표현은:

선두 컬럼 조건이 없으면 일반적인 범위 탐색 효율이 떨어질 수 있지만, Oracle이 다른 인덱스 접근 방식을 선택할 수도 있다.

입니다.

32장. `!=` 조건을 두 범위로 나누면 항상 빨라질까#

다음 조건:

WHERE dept_id <> 10

을:

WHERE dept_id < 10

UNION ALL

SELECT ...
WHERE dept_id > 10

처럼 바꾸는 아이디어가 있습니다.

논리적으로 두 범위는 겹치지 않습니다.

하지만 성능이 항상 좋아지는 것은 아닙니다.

33장. 결과가 대부분이라면 전체 스캔이 더 저렴할 수 있다#

부서 10 직원:

10%

라면:

dept_id <> 10

은:

90%

를 반환합니다.

90%를 두 번의 인덱스 범위로 읽는 것보다 테이블 전체를 한 번 읽는 것이 더 저렴할 수 있습니다.

34장. NULL 의미도 확인해야 한다#

SQL에서:

dept_id <> 10

은 dept_id IS NULL인 행을 참으로 판단하지 않습니다.

다음 두 조건:

dept_id < 10

dept_id > 10

역시 NULL을 포함하지 않습니다.

이번 경우 결과 집합은 맞을 수 있지만, 다른 변환에서는 NULL 처리 여부를 반드시 확인해야 합니다.

35장. 실행 계획은 추정치와 실제 실행을 구분해야 한다#

다음 명령:

EXPLAIN PLAN FOR
SELECT ...

후:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY
);

를 실행하면 기본적으로 추정 계획을 확인합니다.

실제 실행 행 수를 측정한 결과와는 다릅니다.

36장. E-Rows는 옵티마이저의 예상이다#

예:

E-Rows
50

은:

실제로 50행이 나왔다.

라는 뜻이 아닙니다.

옵티마이저가 계획을 세울 때:

약 50행이 나올 것

이라고 예상했다는 의미입니다.

37장. 실제 행 수는 실제 실행 통계에서 확인한다#

권한 있는 테스트 환경에서 예를 들어 다음처럼 실행할 수 있습니다.

SELECT /*+ gather_plan_statistics */
       COUNT(*)
FROM employee
WHERE dept_id = 10
  AND salary > 500;

그다음:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST'
    )
);

등을 통해 실제 커서 통계를 확인할 수 있습니다.

38장. 반드시 의도한 커서인지 확인해야 한다#

중간에 다른 SQL을 실행했다면:

NULL, NULL

로 최근 커서를 보는 방식이 원하는 SQL이 아닐 수도 있습니다.

필요하면:

SQL_ID

child cursor

를 명시해 확인합니다.

39장. E-Rows와 A-Rows 차이는 중요한 단서다#

가상의 계획:

Operation                     E-Rows   A-Rows

INDEX RANGE SCAN                  50    50,000

이라고 하겠습니다.

예상:

50행

실제:

50,000행

입니다.

차이:

1000배

입니다.

40장. 예상 행 수가 크게 틀리면 계획 선택도 흔들릴 수 있다#

옵티마이저는 예상 행 수를 기반으로:

조인 방식

조인 순서

인덱스 사용

정렬

메모리

등을 결정합니다.

예상이 크게 빗나가면 실제 데이터에 비효율적인 계획이 선택될 수 있습니다.

41장. 추정 오류가 크다면 먼저 통계를 의심할 수 있다#

가능한 원인:

오래된 통계

히스토그램 부족

데이터 편향

컬럼 상관관계

바인드 값 차이

등을 확인할 수 있습니다.

42장. 두 조건이 서로 독립적이지 않을 수도 있다#

예:

dept_id = 10

salary > 500

이라고 하겠습니다.

회사 전체에서는 급여 500 초과가 5%입니다.

하지만 부서 10에서는:

80%

일 수 있습니다.

옵티마이저가 두 조건을 단순 독립적으로 추정하면 실제와 크게 어긋날 수 있습니다.

43장. A-Rows와 Starts를 함께 읽어야 한다#

다음 연산자가 있다고 하겠습니다.

E-Rows
2

A-Rows
200

Starts
100

단순히:

200 / 2
=
100배 오차

라고 해석하면 틀릴 수 있습니다.

44장. 한 번 시작할 때 실제 행 수를 계산해 보자#

누적 A-Rows:

200

Starts:

100

이므로 시작 한 번당 실제 평균:

200 / 100
=
2행

입니다.

E-Rows:

2

와 일치합니다.

즉 예상은 상당히 정확합니다.

45장. 두 번째 사례#

E-Rows
2

A-Rows
2000

Starts
100

이면:

2000 / 100
=
20행

입니다.

예상:

2

실제 평균:

20

즉 약 10배 차이입니다.

46장. Starts는 중첩 루프에서 특히 중요하다#

Nested Loops에서 안쪽 접근이:

바깥 행마다 반복

될 수 있습니다.

예:

바깥 행
100,000

안쪽 인덱스 탐색
100,000회

입니다.

한 번의 인덱스 탐색이 0.05ms라 해도 반복 횟수가 많으면 총비용은 커집니다.

47장. 실행 계획의 들여쓰기를 단순 실행 순서표로 외우지 말자#

계획을:

가장 안쪽부터
한 번씩 위로 실행

된다고 이해하면 반복 구조를 놓칠 수 있습니다.

특히:

Nested Loops

Starts

가 있는 계획에서는 안쪽 연산이 여러 번 실행될 수 있습니다.

48장. BUFFER 작업량도 같이 본다#

실행 시간:

100ms

라는 숫자 하나는 캐시와 시스템 부하 영향을 크게 받습니다.

따라서 실행 통계에서:

Buffers

logical reads

physical reads

등 실제 작업량도 함께 봅니다.

49장. 같은 결과를 적은 버퍼로 만들었다면 의미 있는 개선일 수 있다#

개선 전:

결과
100행

버퍼
50,000

개선 후:

결과
100행

버퍼
500

이라면 작업량이 크게 줄었습니다.

실행 시간도 줄어들 가능성이 높습니다.

50장. 실행 시간이 줄었는데 버퍼는 같다면 다른 요인일 수도 있다#

첫 실행:

300ms

두 번째 실행:

20ms

버퍼 작업량은 거의 같습니다.

가능한 원인:

캐시 워밍

스토리지 상태

동시 부하 감소

일 수 있습니다.

따라서 한 번의 시간 측정만으로 튜닝 효과를 판단하지 않습니다.

51장. 커버되는 조회와 테이블 방문 조회를 비교하자#

인덱스:

(dept_id, salary)

가 있습니다.

쿼리 A:

SELECT
    dept_id,
    salary
FROM employee
WHERE dept_id = 10
  AND salary > 500;

필요한 컬럼이 모두 인덱스 안에 있습니다.

52장. 쿼리 B는 이름까지 필요하다#

SELECT
    dept_id,
    salary,
    emp_name
FROM employee
WHERE dept_id = 10
  AND salary > 500;

emp_name은 인덱스에 없습니다.

따라서 테이블 접근이 추가될 수 있습니다.

53장. 같은 RANGE SCAN이어도 비용은 다를 수 있다#

두 쿼리 모두 실행 계획에:

INDEX RANGE SCAN

이 있을 수 있습니다.

하지만 쿼리 B는:

TABLE ACCESS BY INDEX ROWID

를 통해 많은 테이블 블록을 추가로 읽을 수 있습니다.

그래서 연산자 이름 하나만 비교하면 안 됩니다.

54장. 커버링을 위해 인덱스에 컬럼을 계속 추가하면 대가가 생긴다#

인덱스:

(dept_id, salary, emp_name, hire_date, ...)

처럼 계속 커지면:

인덱스 크기 증가

INSERT 비용 증가

UPDATE 비용 증가

캐시 효율 저하

가 생길 수 있습니다.

읽기 한 쿼리만 보고 인덱스를 비대하게 만들면 안 됩니다.

55장. 클러스터링 팩터를 예제로 계산해 보자#

인덱스 순서로 다음 10개 행을 읽는다고 하겠습니다.

배치 A#

ROW 1
블록 1

ROW 2
블록 1

ROW 3
블록 1

ROW 4
블록 2

ROW 5
블록 2

같은 블록에 연속적으로 모여 있습니다.

56장. 배치 B#

ROW 1
블록 1

ROW 2
블록 8

ROW 3
블록 2

ROW 4
블록 9

ROW 5
블록 3

계속 다른 블록으로 이동합니다.

이 경우 B가 더 높은 CF 방향으로 나타날 수 있습니다.

57장. 낮은 CF는 범위 검색에서 테이블 접근에 유리한 경향을 보여준다#

인덱스에서 연속된 1000개 키를 읽었을 때 테이블 데이터도 가까운 블록에 있다면 재방문해야 하는 블록 수를 줄일 수 있습니다.

그래서 범위 스캔 비용 추정에 클러스터링 팩터가 사용됩니다.

58장. 하지만 FAST FULL SCAN처럼 테이블을 방문하지 않는 쿼리에는 의미가 다르다#

인덱스 자체만 읽고 쿼리를 해결한다면:

인덱스 순서
→ 테이블 ROWID 방문

과정이 없습니다.

따라서 클러스터링 팩터의 영향이 같은 방식으로 나타나지 않습니다.

59장. TABLE ACCESS FULL이 오히려 합리적인 경우#

100만 행 중:

700,000행

을 반환해야 합니다.

인덱스 접근:

인덱스 읽기
+
70만 ROWID 테이블 접근

보다:

테이블 전체를 순차적으로 한 번 읽기

가 더 저렴할 수 있습니다.

60장. 대량 조회에서는 멀티블록 읽기의 장점이 있다#

전체 스캔은 연속된 블록을 넓게 읽을 수 있습니다.

반면 인덱스 ROWID 접근은 데이터가 흩어져 있으면 많은 블록을 반복 방문할 수 있습니다.

따라서:

Full Scan
=
나쁜 계획

은 잘못된 규칙입니다.

61장. OLTP와 분석 쿼리의 좋은 계획은 다를 수 있다#

고객 한 명의 주문:

10건

을 찾는 OLTP 쿼리에서는 선택적인 인덱스 접근이 유리할 수 있습니다.

반면 월말 전체 매출 집계에서는:

수천만 행

을 읽어야 하므로 전체 스캔과 병렬 처리가 더 적합할 수 있습니다.

62장. 실행 계획에 인덱스가 보였다는 이유로 튜닝을 끝내지 말자#

다음 계획:

INDEX RANGE SCAN

을 보고:

튜닝 완료

라고 하면 안 됩니다.

추가 확인:

A-Rows

Starts

Buffers

TABLE ACCESS

Elapsed Time

가 필요합니다.

63장. 실제 반환 행보다 후보 행이 훨씬 많을 수도 있다#

예:

INDEX RANGE SCAN
100,000건 후보

추가 필터
99,900건 제거

최종 결과
100건

이라면 인덱스 조건 자체를 더 선택적으로 만들 수 있는지 검토할 수 있습니다.

64장. Predicate Information을 확인하자#

Oracle 실행 계획에서는 조건이:

access predicate

인지:

filter predicate

인지 확인할 수 있습니다.

인덱스 탐색 자체를 좁히는 조건과 읽은 뒤 걸러내는 조건은 작업량에 차이를 만듭니다.

65장. 통계가 낡으면 인덱스 선택도 달라질 수 있다#

테이블이 처음에는:

10만 행

이었습니다.

현재는:

1억 행

인데 통계가 오래되었습니다.

옵티마이저가 과거 분포를 기준으로 계획을 선택할 수 있습니다.

66장. 하지만 통계를 무조건 다시 수집하는 것도 답은 아니다#

운영 중 통계 갱신은 계획 변화를 일으킬 수 있습니다.

따라서:

현재 문제 SQL

통계 상태

데이터 변화

계획 변화 가능성

을 확인하고 수행합니다.

67장. 바인드 변수에서도 실제 값의 분포가 중요하다#

SQL:

WHERE dept_id = :dept_id

입니다.

부서별 데이터:

10
100건

20
700,000건

30
500건

이라고 하겠습니다.

같은 SQL 구조라도 입력값에 따라 최적 접근 경로가 달라질 수 있습니다.

68장. 특정 값 하나의 계획을 전체 값에 일반화하면 안 된다#

테스트:

dept_id = 10

에서는 인덱스가 매우 좋았습니다.

운영:

dept_id = 20

에서는 70만 행을 반환합니다.

같은 계획이 항상 최적이라고 볼 수 없습니다.

69장. FULL SCAN과 FAST FULL SCAN의 차이를 다시 정리하자#

INDEX FULL SCAN#

인덱스 전체 읽기

키 순서 유지 가능

정렬 회피 가능성

INDEX FAST FULL SCAN#

인덱스 전체 읽기

키 순서 보장 없음

넓은 읽기·병렬 처리 가능성

이름에 FULL이 공통으로 들어가지만 목적과 동작이 다릅니다.

70장. UNIQUE와 RANGE의 차이도 단순 행 수 차이만은 아니다#

UNIQUE SCAN:

유일성이 보장된 검색 키

RANGE SCAN:

하나 이상의 인덱스 엔트리 범위

입니다.

다음 조건:

emp_id = 1001

이라도 emp_id가 UNIQUE가 아니라면 RANGE SCAN이 나올 수 있습니다.

71장. 인덱스의 제약 속성도 접근 방식에 영향을 준다#

UNIQUE INDEX

와:

NONUNIQUE INDEX

는 옵티마이저가 알고 있는 유일성 정보가 다릅니다.

유일성은 단순 성능 정보뿐 아니라 데이터 무결성 정보이기도 합니다.

72장. 실제 계획을 비교할 때 같은 결과를 유지해야 한다#

튜닝 전 SQL:

100행

튜닝 후 SQL:

95행

이라면 빨라졌어도 같은 쿼리가 아닙니다.

먼저:

행 수

주요 값

NULL 처리

가 동일한지 확인해야 합니다.

73장. WHERE 조건을 변형하면 NULL 결과가 달라질 수 있다#

예:

dept_id <> 10

을 다른 형태로 변경할 때 NULL 행 처리도 검증해야 합니다.

성능 개선 전에 결과 동등성을 확인해야 합니다.

74장. 인덱스 스캔 비교 실습 체크리스트#

  1. 쿼리가 실제 몇 행을 반환하는가?
  2. 전체 테이블 대비 비율은 얼마인가?
  3. 인덱스의 선두 컬럼 조건이 있는가?
  4. 유일 조건인가 범위 조건인가?
  5. SKIP SCAN 가능성이 있는가?
  6. 필요한 컬럼이 모두 인덱스에 있는가?
  7. 추가 TABLE ACCESS가 발생하는가?
  8. 테이블 행이 물리적으로 얼마나 흩어져 있는가?
  9. 클러스터링 팩터는 블록 수·행 수 대비 어느 위치인가?
  10. 실제 버퍼 읽기는 얼마인가?

75장. 실행 계획 검증 체크리스트#

  1. EXPLAIN PLAN인지 실제 커서 통계인지 구분했는가?
  2. E-Rows와 A-Rows를 비교했는가?
  3. Starts가 1보다 큰 노드를 확인했는가?
  4. A-Rows가 누적값인지 고려했는가?
  5. 실행 계획의 SQL_ID가 대상 SQL과 같은가?
  6. child cursor를 확인해야 하는가?
  7. 버퍼 접근량을 확인했는가?
  8. 물리 읽기가 발생했는가?
  9. access와 filter predicate를 구분했는가?
  10. 최종 반환 행과 중간 작업량을 구분했는가?

76장. 클러스터링 팩터 체크리스트#

  1. CF의 절댓값만 보고 있지 않은가?
  2. 테이블 블록 수는 얼마인가?
  3. 테이블 행 수는 얼마인가?
  4. 범위 조회가 실제 몇 행인가?
  5. 테이블 재접근이 발생하는가?
  6. 필요한 컬럼이 인덱스에 모두 있는가?
  7. 해당 데이터가 캐시에 있을 가능성이 높은가?
  8. 다른 인덱스와 비교했는가?
  9. 실제 버퍼 수가 예상과 맞는가?
  10. CF 하나만으로 인덱스 품질을 평가하고 있지 않은가?

77장. 가장 흔한 오해 1 — 인덱스를 사용하면 무조건 빠르다#

다음은 틀린 규칙입니다.

Index Scan
=
빠름

더 정확한 질문은:

몇 개의 인덱스 엔트리를 읽었는가?

몇 개의 테이블 블록을 방문했는가?

몇 행을 반환했는가?

입니다.

78장. 가장 흔한 오해 2 — Full Scan은 항상 나쁘다#

대량 분석에서는:

TABLE ACCESS FULL

이 가장 효율적인 경로일 수 있습니다.

특히 대부분의 데이터를 읽는 쿼리에서는 인덱스 랜덤 접근보다 유리할 수 있습니다.

79장. 가장 흔한 오해 3 — 선두 컬럼이 없으면 인덱스는 절대 못 쓴다#

Oracle에는:

INDEX SKIP SCAN

이 있습니다.

또:

INDEX FULL SCAN

INDEX FAST FULL SCAN

으로 인덱스를 사용할 수도 있습니다.

80장. 가장 흔한 오해 4 — FAST FULL SCAN이면 정렬도 해결된다#

아닙니다.

INDEX FAST FULL SCAN은 인덱스 키 순서를 보장하는 방식으로 읽는 연산이 아닙니다.

ORDER BY 제거 여부는 실제 계획을 확인해야 합니다.

81장. 가장 흔한 오해 5 — CF가 낮으면 좋은 인덱스다#

CF는 인덱스 순서와 테이블 행 배치의 관계를 설명하는 하나의 통계입니다.

검색 유형·반환량·커버링 여부에 따라 중요도가 달라집니다.

따라서:

CF 낮음
=
항상 우수

라고 평가하면 안 됩니다.

82장. 가장 흔한 오해 6 — E-Rows와 A-Rows만 바로 나누면 된다#

Starts가 여러 번인 노드에서는 A-Rows가 누적된 결과일 수 있습니다.

먼저:

A-Rows / Starts

관점에서 한 번의 실행당 결과를 비교해야 할 수 있습니다.

83장. 튜닝은 연산자 이름을 바꾸는 작업이 아니다#

예:

TABLE ACCESS FULL

을:

INDEX RANGE SCAN

으로 바꿨다고 하겠습니다.

하지만:

버퍼
10,000
→
50,000

으로 증가했습니다.

실행 시간도 느려졌습니다.

이 경우 연산자 이름은 더 그럴듯해 보여도 실제 작업은 악화된 것입니다.

84장. 성능 개선의 기준은 같은 결과를 더 적은 작업으로 만드는 것이다#

이상적인 비교:

결과
동일

버퍼
감소

실행 시간
감소

CPU
감소

입니다.

모든 지표가 항상 동시에 줄어드는 것은 아니지만 실제 작업량의 변화가 설명되어야 합니다.

85장. 인덱스 추가 전 확인할 질문#

새 인덱스를 만들기 전에 다음을 묻습니다.

현재 SQL의 반환 행은 몇 개인가?

기존 인덱스로 후보를 얼마나 줄이는가?

TABLE ACCESS가 얼마나 발생하는가?

쿼리 실행 빈도는 얼마인가?

INSERT·UPDATE 빈도는 얼마인가?

새 인덱스 크기는 얼마나 되는가?

인덱스는 조회 성능과 쓰기 비용을 교환하는 구조입니다.

86장. 특정 SQL 하나만 보고 인덱스를 만들면 중복 인덱스가 늘어날 수 있다#

예:

(dept_id)

(dept_id, salary)

(dept_id, salary, emp_name)

(dept_id, salary, emp_name, hire_date)

같은 인덱스가 계속 추가될 수 있습니다.

전체 워크로드에서 역할이 겹치는지 확인해야 합니다.

87장. Oracle 실행 계획을 읽는 실전 순서#

첫째:

최종 결과 행 수

를 확인합니다.

둘째:

E-Rows
vs
A-Rows

를 비교합니다.

셋째:

Starts

를 확인합니다.

넷째:

인덱스 뒤 TABLE ACCESS

가 있는지 봅니다.

다섯째:

Buffers

를 확인합니다.

여섯째:

Predicate Information

을 확인합니다.

88장. 그다음 클러스터링 팩터와 통계를 본다#

범위 스캔 뒤 테이블 접근이 과도하다면:

CF

테이블 블록 수

행 수

조건 선택도

를 봅니다.

예상 행 수가 크게 틀린다면:

통계

히스토그램

데이터 분포

컬럼 상관관계

를 조사합니다.

89장. 작은 예제로 계획을 해석해 보자#

가상 결과:

Operation                         E-Rows   A-Rows   Starts

TABLE ACCESS BY INDEX ROWID        100     50000      1
  INDEX RANGE SCAN                 100     50000      1

입니다.

이 계획에서 먼저 보이는 것은:

예상 100

실제 50,000

입니다.

90장. 이 계획의 첫 질문은 “왜 RANGE SCAN인가”가 아니다#

더 중요한 질문:

왜 옵티마이저는 100행이라고 예상했는데 실제로는 5만 행이었는가?

입니다.

통계가 틀렸다면 인덱스 연산자를 다른 것으로 강제로 바꾸는 것보다 통계 문제를 먼저 해결해야 할 수 있습니다.

91장. 두 번째 질문은 테이블 블록을 얼마나 읽었는가다#

5만 ROWID가:

500블록

에 모여 있다면 상황이 다릅니다.

반대로:

45,000블록

을 방문했다면 비용이 훨씬 큽니다.

클러스터링 팩터와 버퍼 통계가 이 판단을 돕습니다.

92장. Oracle 인덱스 스캔의 핵심 비교#

방식 특징 순서 활용 대표 상황
UNIQUE SCAN 유일 키 탐색 키 위치 탐색 PK·UNIQUE 등가 조건
RANGE SCAN 인덱스 일부 범위 가능 범위·부분 키 검색
FULL SCAN 인덱스 전체를 키 순서로 가능 정렬 활용·전체 인덱스
FAST FULL SCAN 인덱스 전체를 순서 없이 보장 안 함 인덱스만으로 대량 처리
SKIP SCAN 선두 키 그룹을 건너 탐색 제한적 선두 컬럼 조건 부재
TABLE FULL SCAN 테이블 전체 읽기 없음 대량 반환·분석

93장. 최종 점검 질문#

실행 계획에 INDEX라는 단어가 보이면 다음을 묻습니다.

몇 행을 찾았는가?

몇 번 반복했는가?

테이블을 다시 읽었는가?

몇 블록을 읽었는가?

예상과 실제가 얼마나 다른가?

결과의 몇 %를 반환하는가?

정렬까지 해결했는가?

쓰기 비용을 감수할 가치가 있는가?

이 질문에 답해야 실제 성능을 설명할 수 있습니다.

94장. 핵심 정리#

Oracle 인덱스 스캔을 이해할 때 가장 먼저 버려야 할 생각은:

INDEX
=
빠름

이라는 단순 공식입니다.

INDEX UNIQUE SCAN은 유일한 키 한 건을 찾는 데 적합할 수 있습니다.

INDEX RANGE SCAN은 연속된 키 범위를 탐색합니다.

하지만 100만 행 가운데 70만 행을 반환해야 한다면 범위 스캔 뒤 엄청난 테이블 접근이 발생할 수 있습니다.

INDEX FULL SCAN과 INDEX FAST FULL SCAN도 구분해야 합니다.

FULL SCAN
→ 인덱스 키 순서 활용 가능

FAST FULL SCAN
→ 인덱스를 넓게 읽지만 키 순서 보장 없음

입니다.

따라서 FAST FULL SCAN이 보인다고 ORDER BY가 자동으로 해결된다고 판단하면 안 됩니다.

복합 인덱스에서 선두 컬럼 조건이 없더라도:

INDEX SKIP SCAN

이 선택될 수 있습니다.

하지만 선두 컬럼 종류가 많거나 반환량이 크다면 비용이 커질 수 있습니다.

실행 계획을 읽을 때는 반드시 추정과 실제를 구분해야 합니다.

E-Rows
→ 예상 행 수

A-Rows
→ 실제 관찰 행 수

Starts
→ 해당 연산이 시작된 횟수

입니다.

특히 Starts가 여러 번인 연산은:

A-Rows / Starts

를 통해 한 번당 실제 행 수를 비교해야 할 수 있습니다.

클러스터링 팩터도 마찬가지입니다.

낮은 값은 일반적으로 인덱스 키 순서와 테이블 블록 배치가 더 잘 맞는 방향을 의미하지만:

CF 하나

만으로 성능을 판단할 수 없습니다.

함께 봐야 할 것은:

테이블 행 수

테이블 블록 수

조회 범위

반환 행 수

테이블 재접근

실제 버퍼

입니다.

결국 Oracle 실행 계획을 제대로 읽는 방법은 특정 연산자를 외우는 것이 아닙니다.

이 연산자가 실제로 몇 번 실행됐고, 몇 행을 만들었으며, 그 결과를 얻기 위해 몇 개의 인덱스와 테이블 블록을 읽었는지를 연결해서 해석하는 것

입니다.

좋은 실행 계획은 INDEX RANGE SCAN이라는 이름을 가진 계획이 아닙니다.

동일한 업무 결과를 더 적은 행 탐색·더 적은 반복·더 적은 블록 접근으로 만들어 내는 계획입니다.

이 페이지의 목차