[Oracle] 테이블 액세스 최소화

테이블 액세스 최소화 전략

테이블 액세스는 인덱스를 타고 테이블로 향하는 비용(Cost)입니다. 액세스 최소화는 그 비용을 최대한 줄이게 만드는 것 입니다.

1. 클러스터링 팩터(Clustering Factor): 같은 데이터가 모여 있는 정도
2. 인덱스 손익분기점: Index Range Scan의 효율이 Table Full Scan보다 떨어지는 지점(CF값과 데이터량에 따라 결정)
3. 인덱스 컬럼 추가 & 인덱스만 읽기: 가장 많이 보편적으로 사용되는 SQL 튜닝 기법으로 조회에 필요한 모든 컬럼이 인덱스에 포함
4. 인덱스 구조 테이블(오라클IOT): 테이블을 아예 인덱스처럼 만든다면?
5. 클러스터 테이블(SQL Server): 인덱스 기준으로 테이블을 만든다면? / 해시 검색이 빨라서 해시 기준으로 테이블을 만들면?

---

 

클러스터링 팩터(Clustering Factor(CF))

Clustering Factor(CF)

 

클러스터링 팩터(Clustering Factor)의 원리

• 인덱스 범위를 스캔(Index Range Scan)할 때 리프 블록을 따라가며 ROWID를 읽어 아래와 같은 계산을 합니다.
  1. 인덱스 리프 블록의 첫번째 레코드부터 마지막까지 순차적으로 읽습니다.
  2. 현재 리프 블록 레코드에서 읽은 ROWID가 가리키는 테이블 블록 주소를 확인 합니다.
  3. 현재 리프 블록에서 확인한 테이블 주소가 직전에 읽었던 레코드의 테이블 블록 주소와 같은지 확인합니다.
  4. 테이블 블록 주소가 다르다면 CF 수치를 +1, 같은 주소면 수치를 올리지 않습니다.

 

좋은 CF vs 나쁜 CF

• Good Clustering Factor (군집도가 높음)
  • 상태: 인덱스 키 순서대로 테이블 데이터도 차곡차곡 쌓여 있는 경우입니다.
  • 수치(CF): 테이블의 총 블록 개수에 가깝습니다.
  • 효과: 인덱스 레코드를 10개 읽을 때, 테이블 블록은 1개만 읽어도 10개 데이터를 다 가져올 수 있어서 I/O 효율이 극대화됩니다.
• Bad Clustering Factor(군집도가 낮음)
  • 상태: 인덱스 키 순서와 상관없이 테이블 데이터가 여기저기 흩어져 있는 경우입니다. 인덱스는 정렬되어 있지만 테이블은 정렬되지 않은 상태에서 발생합니다.
  • 수치(CF): 테이블의 총 행(Rows) 개수에 가깝습니다.
  • 효과: 인덱스 레코드를 10개 읽을 때, 꼬여있으면 테이블 블록을 10번이나 새로 읽어야 해서 랜덤 액세스 부하가 심해집니다.

---

인덱스 Scan의 손익분기점

Table Full Scan과 Index Scan의 결정은 옵티마이저가 손익분기점을 가지고 판단합니다.

• 테이블에 10만 건의 데이터가 있을 때, 5천 건(5%)에서 2만 건(20%) 사이일 경우 옵티마이저는 좋은 클러스터링 팩터라고 판단하여 Index Scan을 결정합니다.
• SQL이 참가하는 데이터 총량이 늘어날수록 인덱스 손익분기점(%)은 점점 낮아지는 경향이 있어서 효율이 떨어집니다.
  • 한 번의 쿼리가 건드리는 데이터의 총량이 많다면(전체의 약 20% 이상) OLAP / TABLE FULL SCAN
  • 한 번의 쿼리가 건드리는 데이터의 총량이 적다면(전체의 약 5%~15% 미만) OLTP / INDEX SCAN

---

인덱스 컬럼 추가 & 인덱스만 읽기

테이블 액세스를 최소화하는 가장 보편적인 튜닝 기법으로 인덱스의 컬럼에 실제 데이터를 추가하는 방법

• 인덱스에 찾고자 하는 값이 포함된 컬럼을 추가하면 테이블 액세스가 생략
• 성능 차이가 발생하는 이유
  • 전 (Table Access 포함): 인덱스 탐색(I/O) + 테이블 랜덤 액세스(I/O), 1만 건 조회 시, 최악의 경우 1만 번의 Single Block I/O 발생.
  • 후 (Index Only Scan): 인덱스 탐색(I/O)만으로 종료.
• 주의사항: 무조건 많이 넣으면 안 되는 이유
  • DML 부하: INSERT, UPDATE, DELETE 발생 시 인덱스도 함께 수정해야 하므로, 컬럼이 많을수록 쓰기 성능이 떨어집니다.
  • 인덱스 크기: 인덱스 자체가 너무 커져서 오히려 인덱스 스캔 효율이 나빠질 수 있습니다.
  • 관리 비용: 인덱스가 비대해지면 저장 공간 부담이 커집니다.

---

인덱스 구조 테이블(IOT, Index Organized Table)

• 기본키(PK) 순서에 따라 데이터를 저장하여 데이터를 아예 인덱스(B-Tree) 구조 안에 정렬해서 저장
  • 일반 테이블: [인덱스 별도] + [데이터 별도(무질서)]
  • IOT: [인덱스 리프 블록 = 데이터 그 자체]
• 인덱스나 데이터를 위한 추가 공간 불필요

👇언제 IOT를 써야할까?

• 코드성 테이블: 데이터 양은 적고 조회가 빈번한 공통 코드 테이블
  • ex) IOT는 인덱스 자체가 테이블이라서 'D001'(코드)을 찾자마자 그 옆에 적힌 "인사팀, 3층" 정보를 바로 읽어 테이블 방문 비용을 줄입니다.
• PK 위주의 조회(부모/자식 관계의 자식 테이블): 거의 모든 쿼리가 PK나 PK의 앞부분 조건으로 들어오는 경우
  • ex) 오라클은 인덱스 트리(B-Tree)를 타고 '2026001'(PK)을 찾으러 내려갑니다. 리프 블록에 도착하는 순간, 그 옆에 이름, 연락처, 부서 정보가 한 ROW에 저장되어 있어서 바로 읽어 비용을 줄입니다.
• 대량의 범위 조회: 특정 날짜나 ID 범위로 데이터를 왕창 읽어야 하는 로그성 테이블(단, 입력 시 정렬 성능 감당 가능 시)
  • ex) IOT는 PK 순서대로 데이터를 물리적으로 정렬해서 저장합니다. 즉, '3월 1일 00시' 데이터부터 '3월 1일 23시' 데이터까지가 하드디스크 블록 상에 순서대로 붙어서 저장되어 인덱스에서 시작점만 찾으면 통째로(Multi-Block I/O) 긁어오기만 하면 됩니다.
• 기본키를 이용한 Between 조건이 자주 사용되는 테이블

---

클러스터 테이블(SQL Server)

• 조인(join)이 자주 이렁나는 두 테이블의 데이터를 물리적으로 같은 블록에 모아서 저장시키는 방법
• 데이터 조회 성능을 향상 시키지만 데이터 저장, 수정, 삭제 또는 한 테이블 전체 Scan의 성능은 감소
•  

👇언제 클러스터 테이블을 만들어야 할까?

1. 주로 조회가 자주 발생하고 수정이 거의 발생하지 않는 테이블
2. 컬럼안의 많은 중복 데이터를 가지는 테이블: 데이터들이 물리적으로 붙어있기 때문에 Single Block I/O 횟수 감소
3. 자주 JOIN 되는 테이블: JOIN 성능을 극대화 하여 Disk I/O 감소

 

클러스터 Key가 되기 좋은 컬럼

• 데이터 값의 범위가 큰 컬럼
• 테이블 간의 조인에 사용되는 컬럼

클러스터 Key가 되기 나쁜 컬럼

• 특정 데이터 값이 작은 컬럼
• 자주 데이터 수정이 발생하는 컬럼
• LONG, LONG RAW 컬럼은 포함할 수 없음

 

단일 클러스터(Single-Table Cluster)

말 그대로 하나의 테이블만 클러스터에 집어넣는 방식입니다.

• 같은 클러스터 키 값을 가진 행(Row)들을 물리적으로 같은 블록에 몰아서 저장. 즉, 같은 키를 가진 Row끼리 합방
• 단일 클러스터 키 값은 데이터를 뭉쳐서 저장하고 싶은 조회 조건 컬럼이 키가 됩니다.(예: 서비스ID)
• 대량의 범위 조회가 잦은 테이블에 사용

👇 예시

 

• 클러스터 키: 서비스ID
• 효과: 'A 서비스'의 로그가 100만 건이라도, 이 데이터들이 디스크 여기저기에 흩어지지 않고 물리적으로 옆집에 옹기종기 모여 살게 됩니다.
• 결과: WHERE 서비스ID = 'A'를 조회할 때, 헤드가 멀리 이동할 필요 없이 한 번에 슥 읽어오므로 I/O 효율이 극대화됩니다.

 

 

다중 테이블 클러스터(Multi-Table Cluster)

서로 다른 두 개 이상의 테이블을 하나의 클러스터에 합칩니다.

• 부모 데이터 바로 옆에 자식 데이터를 갖다 놓아서 저장. 즉, 부모와 자식 Row끼리 합방
• 다중 테이블 클러스터는 조인(Join) 컬럼이 클러스터 키가 됩니다.
• 조인이 자주 일어나는 관계에서 사용

👇 예시

[부서(Dept)와 사원(Emp) 테이블]

• 클러스터 키: 부서번호
• 효과: 10번 부서 정보 바로 뒤에 10번 부서 사원들이 저장되고, 그 뒤에 20번 부서 정보와 20번 사원들이 저장됩니다.
• 결과: 부서와 사원을 조인할 때, 오라클은 이미 같은 블록에 있는 데이터를 읽기만 하면 됩니다. "조인이 이미 물리적으로 완료된 상태"라고 봐도 무방합니다.

 

인덱스 클러스터(Index Cluster)

• 방식: 클러스터 키 값을 찾기 위해 별도의 클러스터 인덱스를 사용
• 특징: 데이터가 정렬되어 저장되며, 범위 조회(BETWEEN, LIKE)에 매우 강함

 

해시 클러스터(Hash Cluster)

• 방식: 인덱스 대신 해시 함수를 써서 데이터 위치를 바로 찾습니다.
• 특징: '=' 조건(Unique Scan)으로 찾을 때 I/O가 단 1회면 끝나는 특징으로 단건 조회 속도가 가장 빠름(단, 범위 조회는 불가)

 

---

인덱스 탐색

인덱스가 (A, B, C) 순서로 결합되어 있을 때, 각 컬럼의 위치에 따른 명칭

• 선두 컬럼: 인덱스 구성 중 가장 첫 번째 칸을 차지한 [A] 컬럼
• 선행 컬럼: 특정 컬럼을 기준으로 그보다 앞에 나열된 모든 컬럼
  • [B]의 선행 컬럼은? → [A]입니다.
  • [C]의 선행 컬럼은? → [A]와 [B]입니다.
  • [A]의 선행 컬럼은? → 없습니다 

인덱스에서 선두 컬럼이 없다면 어느 세션에 있는지 모르니 모든 세션을 다 돌아다녀야 하고

인덱스에서 선두 컬럼은 있는데 선행 컬럼이 없다면 세션까지는 빠르게 찾아가지만 데이터를 하나하나 확인 해야합니다.

• 인덱스가 (A, B, C) 순서로 만들어져 있는데, 정작 쿼리 조건문(WHERE)에 A가 없거나 B가 빠지면 인덱스를 제대로 타지 못하고 헛고생을 한다는 뜻입니다.

• 결과물(ROWS)은 똑같이 10건이지만, 그 10건을 찾기 위해 인덱스를 뒤지면서 메모리 블록(cr)을 7463번이나 읽었습니다.
• 사실상 인덱스를 처음부터 끝까지 거의 다 훑었다(Index Full Scan)는 뜻입니다.

 

인덱스 액세스 조건 / 필터 조건

인덱스를 스캔하는 단계에서 처리하는 조건절은 액세스 조건과 필터 조건으로 나뉨

 

인덱스 액세스 조건

• 인덱스 스캔의 시작점과 끝점을 결정하는 조건입니다. 
• 역할: B-Tree 인덱스의 루트(Root)에서 브랜치(Branch)를 거쳐 리프(Leaf) 블록까지 수직적으로 내려가는 경로를 어디서부터 읽기 시작해서 어디서 멈출 것인가를 결정
• 성능 지표: 실행 계획에서 access 단계의 비용(Cost)이 낮을수록 물리적으로 가벼워집니다.

인덱스 필터 조건

• 리프 블록에 도착한 후, 옆으로 이동하며 데이터를 읽는 수평적 탐색 과정에서 작동
• 역할: 인덱스 레코드를 메모리(Buffer Cache)로 퍼 올려서 CPU가 해당 레코드 값을 확인하여 조건에 맞지 않으면 버립니다.
• 성능 지표: ROWS 수치(결과 행)는 적은데 Consistent Gets(cr) 수치가 비정상적으로 높다면, 대부분 이 필터 조건에서 헛수고를 하고 있다는 것입니다.

구분	인덱스 액세스 조건	인덱스 필터 조건
작동 방향	수직적 (위에서 아래로)	수평적 (옆으로 이동하며 검사)
I/O 영향	읽어야 할 블록 수 자체를 줄임	읽은 블록 안에서 데이터를 걸러냄
최적화	인덱스 컬럼 순서가 큰 영향	컬럼 순서와 상관없이 작동

 

 

 

인덱스 선행 컬럼이 '=' 조건이 아닐때

• 선행 컬럼에 'BETWEEN', 'LIKE', '>', '<' 같은 범위 연산자가 사용되는 순간, 그 뒤에 오는 컬럼들은 액세스 조건으로서의 힘을 잃고 필터 조건으로 전락합니다.
• 인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치 조건(=)으로 사용할 때 가장 좋다
• 인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은 조건을 만족하는 레코드가 모두 인접한 위치에 모여 있기 때문이다.
• 인덱스 선행 컬럼에 조건에 해당하는 조건절이 없거나 '=' 아닌 연산자가 사용되면, 인덱스 스캔을 한다 해도 효율이 떨어진다.
• 인덱스 중간 컬럼이 조건절에서 빠지면, 그 뒤쪽 컬럼들은 아무리 '=' 조건이어도 필터로 작동하여 스캔 비효율

 

인덱스 스캔 효율을 높이는 방법

• 조건절에서 항상 '=' 으로 들어오는 컬럼을 앞쪽으로 배치하고, 범위 조건으로 쓰는 컬럼은 뒤쪽으로 배치
• BETWEEN을 IN-LIST로 전환하여 UNION ALL로 쪼개진 쿼리는 모두 '=' 조건으로 효율이 향상
  • IN-List 개수가 많으면 과도한 수직 탐색으로 비용(Cost)가 더 커질 수 있습니다.
  • 데이터가 물리적으로 뭉쳐져 있을 때는 BETWEEN의 비용이 더 쌀 수도 있습니다.
  • 오퍼레이션이 INList - Literator 형태로 PLAN이 풀려야 '='이 성립됩니다.
• INDEX SKIP SCAN 힌트 사용
  • 선행 컬럼의 종류(Value)가 성별 처럼 매우 적어야함 
  • 후행 컬럼 조건은 유니크할 때 좋음

 

---

인덱스 설계

Index 설계 공식

• WHERE 조건에서 많이 사용하는 컬럼들을 대상으로 설계
• 많이 사용되는 조건 중 '=' 연산자가 적용되는 컬럼을 최선행으로 배치
• 범위 조건 컬럼은 인덱스 후행으로 밀어내어 필터 단계의 부하를 최소
• 선두 조건은 수직 탐색을 최소화 할 수 있는 컬럼으로 선정

 

고려사항

• 인덱스 설계는 스캔 효율성을 가장 기초로 진행된다.
• 설계하는 인덱스가 얼마나 빈번하게 사용할 지 확인한다.
• 속도를 희생하더라도 업무적으로 중요한 요건인지를 파악한다.
• 데이터량(건 by 건 vs 대용량)에 따라 데이터에 적용되는 인덱스 설계 기법의 차이가 존재한다.(OLTP, DW)
• 클러스터링 팩터가 효율적으로 잡히는지 확인한다.
• DML 부하와 조회 성능 사이의 트레이드오프(Trade-off)를 고려하여, 불필요한 중복 인덱스를 제거하고 핵심 쿼리에 최적화된 최소한의 인덱스만 유지해야 한다.
• 인덱스가 많으면 수정이나, 컬럼 추가에 따른 기존 SQL 튜닝 등 다양한 관리 비용이 발생한다.

 

성능 향상을 위한 설계

• ORDER BY 연산에서 자주 사용하는 조합을 생각하여 인덱스 생성 또는 기존 인덱스 수정
• 결합 인덱스 생성 시 선택도를 고려하여 인덱스 생성
• '=' 조건을 빈번하게 쓰이는 컬럼을 선두 컬럼으로 배치하고 자주 사용되고, 종류가 많은 컬럼은 선행 컬럼으로 배치
• ORDER BY에 자주 쓰이는 컬럼은 뒤쪽에 배치

 

 

 

 

 

 

 

 

 

 

참조: 멋쟁이사자처럼 - 한 번에 합격라는 SQLP 과정