인덱스 구조와 결합 인덱스
B-tree 탐색, 결합 키, BLEVEL, ROWID와 학생명부 실행 통계, 브랜치 포인터 퀴즈 답과 고객 인덱스 비교 출력은 확인 불가
#핵심 요약
B*Tree 인덱스는 루트, 브랜치, 리프 블록으로 이루어짐
리프 블록에는 인덱스 키 값과 테이블 행 위치(ROWID)가 저장되고, 리프 블록끼리는 좌우로 연결되어 범위 스캔이 가능함
루트에서 브랜치를 거쳐 리프까지 내려가는 것이 수직적 탐색, 리프에서 좌우로 읽어 가는 것이 수평적 탐색임
브랜치 블록은 검색 값과 자신이 가진 키 범위를 비교해 내려갈 리프 블록을 정함
인덱스 튜닝의 두 축은 인덱스 스캔 효율과 테이블 랜덤 액세스이며, 실습에서는 실무 영향이 더 큰 쪽이 테이블 랜덤 액세스라고 정리했음
결합 인덱스의 모든 컬럼을 = 조건으로 찾으면 컬럼 순서가 달라도 읽는 블록 양은 사실상 같음
컬럼 순서는 범위 조건이 섞일 때 차이를 만듦
#개념과 동작 원리
| 구성 요소 | 설명 |
|---|---|
| 루트 블록 | 탐색의 시작점 |
| 브랜치 블록 | 하위 블록의 키 범위를 갖고 있어 내려갈 방향을 정함 퀴즈는 첫 번째 키보다 작은 값을 가진 하위 블록을 가리키는 포인터를 따로 묻음(답 확인 불가) |
| 리프 블록 | 인덱스 키와 ROWID 양방향으로 연결되어 수평 스캔 가능 |
BLEVEL |
루트에서 리프까지 내려가는 단계 수 1이면 루트 바로 아래가 리프, 2면 루트, 브랜치, 리프의 3단계 실습에서는 보통 2~3 수준이라고 적었음 |
실습 학생명부 예: 이름처럼 값이 잘 갈리는 컬럼이 먼저 정렬되어 있으면 필요한 구간만 조금 읽으면 되지만, 시력처럼 중복값이 많은 컬럼이 먼저 정렬되어 있으면 그 안에서 이름을 찾으려고 넓은 범위를 읽고 버려야 함
실습 결합 인덱스 예: 성별 + 고객명과 고객명 + 성별 두 인덱스로 성별 = '남' AND 고객명 = '<NAME>'를 찾으면, 두 컬럼 모두 = 조건이라 어느 쪽이든 조건에 맞는 리프 위치로 바로 내려가므로 인덱스 블록 I/O 양이 같아짐
실습에서는 결합 인덱스 효율을 따질 때 비교 횟수가 아니라 블록 I/O 관점으로 봐야 한다고 정리했음
실습 학생명부 실습3에서도 두 인덱스 결과가 같았고, 설명은 "데이터가 많았다면 첫 번째 인덱스가 더 좋다"였음
B-tree 인덱스를 읽는 경로별 차이는 인덱스 스캔 방식에서 다룸
#주요 용어와 구성 요소
| 구문 | 의미 |
|---|---|
| 균형 트리 | 모든 리프가 같은 깊이에 놓이는 구조임 |
| 선두 컬럼 | 결합 키 정렬의 앞부분임 선두 조건이 없더라도 비용에 따라 Skip Scan 등이 선택될 수 있음 |
| NULL 키 | 일반 B-tree는 모든 키 컬럼이 NULL인 행의 키를 저장하지 않음 |
| 물리 ROWID | 힙 테이블 행 위치를 표현함 IOT의 논리 ROWID와 구분함 |
#필수 명령어 또는 SQL
| 항목 | 실행 조건 |
|---|---|
| 작업 대상 | 소유 인덱스 통계, 힙 테이블 ROWID, 두 결합 인덱스 조회 경로 |
| 필요 권한 | 대상 테이블 SELECT 권한과 소유 테이블 인덱스 생성 권한, 공간 할당 필요 |
| 바꿀 입력값 | <TABLE_NAME>, <INDEX_NAME>, <NAME>과 고객 테이블의 실제 컬럼명 |
| 결과 해석 | 통계 NULL은 높이 0과 다름 두 조회의 결과 집합과 실제 Buffers를 함께 비교함 학생명부의 비교 통계는 본문 참조 고객 인덱스 두 개의 비교 출력은 기재 없음 |
#인덱스 트리 높이와 리프 블록 수
SELECT index_name,
blevel AS 트리레벨,
leaf_blocks AS 리프블록수,
num_rows AS 인덱스_행수,
distinct_keys AS 고유값수,
ROUND(num_rows / NULLIF(distinct_keys, 0), 1) AS 키당_평균행수
FROM user_indexes
WHERE index_name = '<INDEX_NAME>';결과 해석: 통계가 수집되어 있어야 값이 채워짐키당_평균행수가 크면 같은 키에 행이 많아 인덱스로 거르는 효과가 작음(컬럼 구성에서 해석)
#ROWID 분해
SELECT ROWID,
DBMS_ROWID.ROWID_OBJECT(ROWID) AS 오브젝트번호,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS 데이터파일번호,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS 블록번호,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS 로우번호
FROM <TABLE_NAME>;결과 해석: 행이 어느 오브젝트, 데이터 파일, 블록, 블록 안 몇 번째 행에 있는지 보여 줌
실습에서는 조회 컬럼으로 고객명을 함께 냈음
#결합 인덱스 두 개 비교
실습에서는 두 결합 인덱스를 만든 뒤 힌트로 각각을 써서 성능 차이가 없는지 확인하게 했음
아래 인덱스 이름은 설명을 위해 붙인 것임
CREATE INDEX 고객_성별_고객명 ON 고객 (성별, 고객명);
CREATE INDEX 고객_고객명_성별 ON 고객 (고객명, 성별);SELECT /*+ index(고객 고객_성별_고객명) */ *
FROM 고객
WHERE 성별 = '남'
AND 고객명 = '<NAME>';
SELECT /*+ index(고객 고객_고객명_성별) */ *
FROM 고객
WHERE 성별 = '남'
AND 고객명 = '<NAME>';결과 해석:
연습 문제 SQL은 selecct로 적혀 있어 그대로는 실행되지 않음
#학생명부의 이름 인덱스 생성 전후
이름 인덱스 생성 전의 실습 기록
Trace Version : 19.0.0.0.0
Trace File : <TRACE_FILE>
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 8 0 23
Total 4 0.000 0.000 0 8 0 23
Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=107)
Rows Row Source Operation
23 TABLE ACCESS FULL 학생명부 (cr=8 pr=0 pw=0 str=1 time=81 us cost=3 size=1472 card=23)결과 해석: 전체 스캔의 Query는 8이며 반환 행 수는 23임
이름 인덱스 생성 후의 실습 기록
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 4 0 23
Total 4 0.000 0.000 0 4 0 23
Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=107)
Rows Row Source Operation
23 TABLE ACCESS BY INDEX ROWID BATCHED 학생명부 (cr=4 pr=0 pw=0 str=1 time=44 us cost=2 size=1472 card=23)
23 INDEX RANGE SCAN 학생명부_이름_IDX (cr=2 pr=0 pw=0 str=1 time=26 us cost=1 size=0 card=23)결과 해석: 인덱스 범위 스캔과 테이블 접근으로 바뀌었으며 Query는 4, 반환 행 수는 23임
#학생명부의 결합 인덱스 컬럼 순서 비교
| 인덱스 | 컬럼 순서 |
|---|---|
| 학생명부_이름시력_IDX | 이름, 시력 |
| 학생명부_시력이름_IDX | 시력, 이름 |
이름, 시력 순서의 실습 기록
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 4 0 16
Total 4 0.000 0.000 0 4 0 16
Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=107)
Rows Row Source Operation
16 TABLE ACCESS BY INDEX ROWID BATCHED 학생명부 (cr=4 pr=0 pw=0 str=1 time=33 us cost=2 size=253 card=11)
16 INDEX RANGE SCAN 학생명부_이름시력_IDX (cr=2 pr=0 pw=0 str=1 time=16 us cost=1 size=0 card=11)결과 해석: Query는 4, 반환 행 수는 16이며 두 연산의 card는 11로 표시됨
시력, 이름 순서의 실습 기록
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 4 0 16
Total 4 0.000 0.000 0 4 0 16
Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=107)
Rows Row Source Operation
16 TABLE ACCESS BY INDEX ROWID BATCHED 학생명부 (cr=4 pr=0 pw=0 str=1 time=56 us cost=2 size=253 card=11)
16 INDEX RANGE SCAN 학생명부_시력이름_IDX (cr=2 pr=0 pw=0 str=1 time=38 us cost=1 size=0 card=11)결과 해석: 이 실습에서는 두 컬럼 순서의 Query가 모두 4, 반환 행 수가 모두 16임
고객 테이블의 두 등치 조건 비교와는 별도의 실습임
#구문과 옵션 설명
USER_INDEXES의 BLEVEL = 0은 루트 자체가 리프라는 뜻임BLEVEL, LEAF_BLOCKS, NUM_ROWS, DISTINCT_KEYS는 수집된 통계이므로 현재 블록 상태와 다를 수 있음
DBMS_ROWID의 물리 위치 분해 함수는 일반 ROWID를 대상으로 하며 UROWID에 그대로 적용하지 않음
#사용 조건과 주의사항
모든 결합 키에 등치 조건이 있어도 두 인덱스의 실제 I/O는 다를 수 있음
본문 수치는 해당 실습의 비교 결과임
인덱스는 조회 경로를 제공하지만 INSERT, DELETE와 키 변경 시 유지 작업이 추가됨
같은 목적의 결합 인덱스를 여러 개 유지할지는 전체 SQL의 조건과 DML 부담을 함께 비교함