01 인덱스 구조
인덱스는 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 액세스할 목적으로 사용하는 오브젝트다.
(1) 범위 스캔
인덱스는 키 컬럼 순으로 정렬돼 있기 때문에 특정 위치에서 스캔을 시작해 검색 조건에 일치하지 않는 값을 만나는 순간 멈출 수 있다. -> 범위 스캔 (Range Scan)
(2) 인덱스 기본 구조
인덱스 중에서 가장 일반적으로 사용되는 B*tree 인덱스 구조는 루트를 포함한 브랜치 블록에 저장된 엔트리에는 DBA(Data Block Address) 정보를 갖고, 리프 블록에는 인덱스 키 컬럼과 함께 Rowid를 갖는다.
(DBA: 하위 노드 블록 정보, rowid: 테이블 레코드를 찾아가기 위한 주소정보)
리프 블록은 항상 키 컬럼 순으로 정렬돼 있기 때문에 '범위 스캔'이 가능하며 키 값이 같을 때는 rowid순으로 정렬된다.
- 인덱스와 테이블 레코드는 1:1 대응 관계 (값도 일치)
- 테이블 레코드 값 갱신 -> 인덱스 키 값도 같이 갱신
- 브랜치 노드상의 레코드 개수는 하위 레벨 블록 개수와 일치
- 브랜치 노드상의 키 값은 하위 노드가 갖는 값의 범위를 의미
(3) 인덱스 탐색
인덱스 탐색 과정 = 수직적 탐색 + 수평적 탐색
수직적 탐색: 수평적 탐색을 위한 시작 지점을 찾는 과정
수평적 탐색: 리프 블록을 인덱스 레코드 간 논리적 순서에 따라 좌->우, 우->좌 스캔
(4) ROWID 포맷
rowid에는 데이터파일 번호, 블록 번호, 로우 번호 같은 테이블 레코드의 물리적 위치 정보를 포함한다.
확장 rowid 포맷
- 데이터 오브젝트 번호 (6)
: 데이터베이스 세그먼트를 식별하기 위해 사용되는 데이터 오브젝트 번호 - 데이터파일 번호(3)
: 로우가 속한 데이터파일 번호(테이블스페이스 내에서의 상대적인 파일 번호) - 블록 번호(6)
: 해당 로우가 저장된 데이터 블록 번호 (데이터파일 내에서의 상대적 번호) - 로우 번호(3)
: 블록 내에서 각 로우에 붙여진 일련번호
02 인덱스 기본 원리
인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프 블록까지의 수직적 탐색 과정을 거쳐야 한다.
언제 정상적인 인덱스 사용이 가능, 불가능할까?
(1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
인덱스 컬럼을 조건절에서 가공하는 경우
select *
from 업체
where substr(업체명, 1, 2) = '대한'
where 직업 <> '학생'
where 부서코드 is not null-> 정상적인 인덱스 범위 스캔이 불가능, Index Full Scan은 가능
select *
from 사원
where 연락처 is null-> is null 조건만으로 검색하는 경우 인덱스 사용 불가능
but empno가 not null인 경우 range scan 가능
또한 is null 이외의 조건식이 하나라도 있으면 range scan 가능
(job, deptno)
where job is null and deptno = 20튜닝 사례1
[1]: 지수구분코드 + 지수업종코드 + 거래일자
[2]: 거래일자
where 지수구분코드 || 지수업종코드 in ('1001', '2003')-> 지수구분코드가 '1'이면서 지수업종코드가 '001'이거나, 지수구분코드가 '2'이면서 지수업종코드가 '003'인 레코드를 찾는 쿼리
where (지수구분코드, 지수업종코드) in (('1', '001'), ('2', '003'))(3) 묵시적 형변환
[1]: 대상연월
and y.대상연월(+) = substr(x.파트너지원요청일자, 1, 6) -1 두 컬럼의 데이터 타입은 varchar2 이다.
varchar2 컬럼에 숫자 값을 더하거나 빼는 연산을 가하면 내부적으로 숫자형으로 형변환이 일어난다. 그러고 나서 y쪽 대상연월 컬럼이 숫자형으로 형변환된다.
(숫자형과 문자형이 비교될 때는 숫자형이 우선시되기 때문)
-> 인덱스 컬럼을 가공한 셈!
add y.대상연월(+) = to_char(add_months(to_date(x.파트너지원요청일자, 'yyyymmdd'), -1), 'yyyymm')char vs Number -> Number로 형변환!
like로 비교 시 Char로!
decode(a,b,c,d)를 처리할 때 출력된 값의 데이터 타입은 세 번째 인자인 C에 의해 결졍된다.
03 다양한 인덱스 스캔 방식
(1) Index Range Scan
: 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식
Index (Range Scan) OF 'IDX' (INDEX)- 인덱스를 스캔하는 범위를 얼마만큼 줄일 수 있는가?
- 테이블로 액세스하는 횟수를 얼마만큼 줄일 수 있는가?
(2) Index Full Scan
: 수직적 탐색업이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
INDEX (FULL SCAN) OF 'IDX' (INDEX)효용성
: 인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려한다. 그런데 대용량 테이블이어서 부담이 크면 인덱스를 활용하는 방법을 생각,,, 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생한다면 전체 I/O 효율 측면에서 유리
(3) Index Unique Scan
: 수직적 탐색만으로 데이터를 찾는 스캔 방식, Unique 인덱스를 통해 '=' 조건으로 탐색하는 경우에 작동 (인덱스 키 컬럼을 모두 '=' 조건으로 검색)
INDEX (UNIQUE SCAN) OF 'IDX' (UNIQUE)(4) Index Skip Scan [index_ss, no_index_ss]
: 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용
리프 블록에 있는 정보만으로 다음에 방문해야 할 블록을 찾는 방법은 없다. 항상 그 위쪽에 있는 브랜치 블록을 재방문해서 다음 방문할 리프 블록에 대한 주소 정보를 얻어야 한다. 어떻게 상위 블록을 재방문할까? -> 버퍼 Pinning!
즉, 브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정을 반복한다.
/*+ INDEX_SS( IDX ) */
INDEX (SKIP SCAN) OF 'IDX' (INDEX (UNIQUE))- 최선두 컬럼은 입력, 중간 컬럼이 누락된 경우
- Distinct Value가 적은 두 개의 선두컬럼이 모두 누락된 경우
- 선두 컬럼이 범위검색 조건인 경우
(5) Index Fast Full Scan [index_ffs, no_index_ffs]
: 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔 / 물리적으로 디스크에 저장된 순서대로 인덱스 블록을 읽어들인다.
db file scattered read: Multiblock Read 방식으로 I/O 할 때 발생하는 대기 이벤트
- 세그먼트 전체를 스캔
- 결과집합 순서 보장 안 됨
- Multiblock I/O
- 병렬스캔 가능
- 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능
/*+ index_ffs(IDX) */
INDEX FAST FULL SCAN IDX(6) Index Range Scan Descending
Index Range Scan과 기본적으로 동일한 스캔 방식
인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
order by empono desc
INDEX (RANGE SCAN DESCENDING) OF IDX (INDEX (UNIQUE))(7) Index Combine
데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 Random Access를 줄이는 데에 목적이 있다.
select /*+ index_combine(e emp_deptno_idx emp_job_idx) */ *
from emp e
where 1=1
and deptno = 30 and job = 'SALESMAN'
TABLE ACCESS BY INDEX ROWID
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCAN
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCAN- 인덱스 스캔하면서 조건 만족하는 레코드의 rowid 목록을 얻는다.
- 해당 rowid 목록을 가지고 비트맵 인덱스 구조를 하나씩 만든다.
- 비트맵 인덱스에 대한 Bit-Wise 오퍼레이션을 수행한다.
- 수행한 결과가 'True'인 비트 값들을 rowid 값으로 환산해 최종적으로 방문할 테이블 rowid 목록을 얻는다.
- rowid를 이용해 테이블을 액세스한다.
(8) Index Join
한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들 때 사용하는 인덱스 스캔 방식
select /*+ index_join(e emp_deptno_idx emp_job_idx) */ *
from emp e
where 1=1
and deptno = 30 and job = 'SALESMAN'
SELECT STATEMENT
VIEW index$_join$001
HASH JOIN
INDEX RANGE SCAN
INDEX RANGE SCAN- 크기가 작은 쪽 인덱스에서 키 값과 rowid를 읽어 PGA 메모리에 해시 맵을 생성한다. (해시 키로 rowid 사용된다)
- 다른 쪽 인덱스를 스캔하면서 햅시 맵에 같은 rowid 값을 갖는 레코드가 있는지 탐색한다.
- rowid 끼리 조인에 성공한 레코드만 결과집합에 포함시킨다.
04 테이블 Random 액세스 부하
(1) 인덱스 ROWID에 의한 테이블 액세스
TABLE ACCESS (BY INDEX ROWID) OF 'TABLE' (TABLE)메인 메모리 DB와의 비교
메인 메모리 DB에서 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼 캐시로 로딩하고 이어서 인덱스를 실시간으로 만든다. 이때 인덱스는 메모리상의 주소정보, 즉 포인터를 담는다.
오라클은 테이블 블록이 수시로 버퍼 캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 직접 포인터로 연결할 수 없는 구조다. 대신 디스크 상의 블록 위치 정보, 즉 DBA를 해시 키 값으로 삼아 해싱 알고리즘을 통해 버퍼 블록을 찾는다. 매번 위치가 달라지더라도 캐싱되는 해시 버킷만큼은 고정적이다.
인덱스 rowid에 의한 테이블 액세스 구조
- 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.
- 각 해시 체인은 래치에 의해 보호되므로 해시 값이 가리키는 해시 체인에 대한 래치를 얻으려고 시도한다. 하나의 cache buffers chains 래치가 여러 개 해시 체인을 동시에 관리한다.
- 다른 프로세스가 래치를 잡고 있으면 래치가 풀렸는지 확인하는 작업을 일정 횟수 만큼 반복한다.
- 그러고도 실패하면 CPU를 OS에 반환하고 잠시 대기 상태에 빠진다. (latch free 대기 이벤트)
- 다시 래치 상태를 확인하고, 래치가 해체되었다면 획득하고 원하던 해시 체인으로 진입한다.
- 거기서 데이터 블록이 찾아지면 래치를 해제하고 바로 읽으면 되는데, 앞서 해당 블록을 액세스한 프로세스가 아직 일을 마치지 못해 버퍼 Lock을 쥔 상태라면 또 다시 대기해야 한다. (buffer busy waits 이벤트)
- 블록 읽기를 마치고 나면 버퍼 Lock을 해제해야 하므로 다시 해시 체인 래치를 얻으려고 시도한다. 이때 또 다시 경합이 발생할 수 있다.
근데 해시 체인을 스캔했는데 데이터 블록을 찾지 못한다면?
- 디스크로부터 블록을 퍼 올리려면 우선 Free 버퍼를 할당 받아야 하므로 LRU 리스트를 스캔한다. 이를 위해서는 cache buffers lru chain 래치를 얻어야 하는데, 래치 경합이 심할 때는 여기서도 (latch free 이벤트 발생)
- LRU 리스트를 정해진 임계치만큼 스캔했는데도 Free 상태의 버퍼를 찾지 못하면 DBWR에게 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보해 달라는 신호를 보낸다. 그런 후 해당 작업이 끝날 때까지 잠시 대기(free buffer waits 이벤트)
- Free 버퍼를 할당 받은 후에는 I/O 서브시스템에 I/O 요청을 하고 다시 대기 상태에 빠지는데, 이때 (db file sequential read 대기 이벤트)
- 마지막으로, 읽은 블록을 LRU 리스트 상에서 위치를 옮겨야 하기 때문에 다시 cache buffers lru chain 래치를 얻어야 하는데, 이 또한 원할하지 못할 때는 (latch free 이벤트)
인덱스 rowid는 테이블 레코드와 물리적으로 연결돼 있지 않기 때문에 인덱스를 통한 테이블 액세스는 고비용 구조다.
(2) 인덱스 클러스터링 팩터
군집성 계수(= 데이터가 모여 있는 정도)
클러스터링 팩터: 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
오라클의 clustering_factor 계산 로직
- counter 변수 선언
- 인덱스 리프 블록을 처음~끝 스캔하면서 인덱스 rowid로부터 블록 번호를 취한다.
- 현재 읽고 있는 인덱스 레코드의 블록 번호가 바로 직전에 읽은 레코드의 블록 번호와 다를 때마다 counter 변수 값을 1씩 증가시킨다.
- 스캔을 완료하고서, 최종 counter 변수 값을 clustering_factor로서 인덱스 통계에 저장한다.
- 이런 식으로 측정된 CF 값은 옵티마이저가 테이블 Full Scan, 인덱스 Range Scan 비용 평가하는데 사용
인덱스를 이용한 테이블 액세스 비용 계산
비용 = blevel + -- 인덱스 수직적 탐색 비용
(리프 블록 수 X 유효 인덱스 선택도) + -- 인덱스 수평적 탐색 비용
(클러스터링 팩터 X 유호 테이블 선택도) -- 테이블 Random 액세스 비용 - blevel: 리프 블록에 도달하기 전 읽게 될 브랜치 블록 개수
- 유효 인덱스 선택도: 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율
- 유효 테이블 선택도: 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율
버퍼 Pinning에 의한 논리적 I/O 감소 원리
방금 액세스한 버퍼에 대한 Pin을 즉각 해제하지 않고 데이터베이스 Call 내에서 계속 유지하는 기능이다. 따라서 연속된 인덱스 레코드가 같은 블록을 가리킨다면, 래치 획득 과정을 생략하고 버퍼를 Pin한 상태에서 읽기 때문에 논리적인 블록 읽기 횟수가 증가하지 않는다.
해당 그림을 보니 확실히 이해된다.
(3) 인덱스 손익분기점
Index Range Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 '손익 분기점' 이라고 부른다.
- 인덱스 rowid에 의힌 테이블 액세스는 Random 액세스인 반면, Full Table Scan은 Sequential 액세스 방식으로 이루어진다.
- 디스크 I/O 시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan은 Multiblock Read 방식을 사용한다.
손익분기점을 극복하기 위한 기능들
- IOT(Index-Organized Table)
- 클러스터 테이블(Clustered Table)
- 파티셔닝
05 테이블 Random 액세스 최소화 튜닝
(1) 인덱스 컬럼 추가
(2) PK 인덱스에 컬럼 추가 ??
(3) 컬럼 추가에 따른 클러스터링 팩터 변화
인덱스에 컬럼을 추가함으로써 테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지는 부작용을 초래할 수도 있다.
변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의해야 한다. (4) 인덱스만 읽고 처리
= 'Covered 쿼리'
테이블을 액세스하고 필터 조건에 의해 버려지는 레코드는 거의 없지만 테이블 Random 액세스 자체가 많다면? => 아예 테이블 엑세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함시키는 방법을 고려해보자! (select 절의 모든 컬럼!)
(5) 버퍼 Pinning 효과 활용
(6) 수동으로 클러스터링 팩터 높이기
인위적으로 CF를 높일 목적으로 테이블을 Reorg 할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 하며, 혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해 봐야 한다.
차세대 시스템 구축 시 주의사항
기존에 운영되던 시스템은 트랜잭션이 발생하는 순서대로 데이터가 입력되는 반면 데이터를 이관할 때는 병렬 쿼리를 많이 활용하기 때문에 데이터를 무작위로 흩어놓는 경향이 있다. -> 테이블 Random I/O 증가 -> 디스크 I/O 발생량, 경합 증가
06 IOT, 클러스터 테이블 활용
IOT(Index-Organized Table): 모든 행 데이터를 리프 블록에 저장
-> 인덱스 리프 블록이 곧 데이터 블록! / 정렬 상태를 유지하며 데이터를 삽입
create table heap_org_t (
a number primary key, b varchar(10)
) organization heap;
장점
- 같은 값을 가진 레코드들이 100% 정렬된 상태
- Sequential 방식으로 데이터를 액세스
- Random 액세스 발생 안함
단점
- 데이터 입력시 성능이 느리다
- 인덱스 구조이므로 인덱스 분할이 발생
- Direct Path Insert 작동 안함
IOT는 언제 사용?
- 크기가 작고 NL 조인으로 반복 룩업하는 테이블
- 폭이 좁고 긴 (로우 수가 많은) 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블
100 ~130 Page 패스 (추후)
07 인덱스 스캔효율
I/O 튜닝의 핵심 원리
- Sequential 액세스의 선택도를 높인다.
- Random 액세스 발생량을 줄인다.
Sequential 액세스 선택도: 인덱스를 스캔한 건수 중 결과로 선택되는 비율
(1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성
첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.
(2) 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 발생하는 비효율
인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.
(3) BETWEEN 조건을 IN-List로 바꾸었을 때 인덱스 스캔 효율
IN-List 개수만큼 Union all 브랜치가 생성되고, 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하기 때문에 선두 컬럼을 between 조건으로 비교할 때와 같은 비효율은 사라진다.
주의사항
-> IN-List 개수가 많지 않아야 한다.
-> 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다
(4) Index Skip Scan을 이용한 비효율 해소
인덱스 컬럼의 누락, 부등호, between, like 같은 범위검색 조건일 때도 유용
-> 선행컬럼의 distinct value가 적어야 함 -> 점프 횟수가 적어짐 (ex 성별, Y/N 플래그, 상태값 등)
-> 후행 컬럼 조건의 선택도가 높아야 함 -> 서브트리에서 빨리 멈추고 다음으로 넘어감 (ex, 사원명, 사번, 전화번호 등)
(5) 범위검색 조건을 남용할 때 발생하는 비효율
첫 번째 범위검색 조건에 의해 스캔 범위가 대부분 결정되며, 그 다음을 따르는 조건부터는 스캔 범위를 줄이는 데에 크게 기여하지 못하므로 성능상 불리해질 수 있다.
(6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의사항 ??
147~153 ??
(7) Between과 Like 스캔 범위 비교 [한번더 보자]
적어도 between이 like 보다 더 넓은 범위를 스캔하는 경우는 없으므로 가급적 between을 사용하자!
범위검색 조건의 경우 실제 테이블의 값으로 기준을 정하냐에 따라 후행 컬럼이 스캔 시작점을 결정하는데 도움을 줄 수 있다.
(8) 선분이력의 인덱스 스캔 효율
선분이력: 시작시점과 종료시점을 함께 관리하는 것
- 쿼리가 간단해진다!
- but 이력이 추가될 때마다 기존 최종 이력의 종료일자도 같이 변경해줘야된다
- 개체 무결성을 사용자가 직접 관리해 주어야 한다
and '20090505' between b.시작일 and b.종료일선분이력 기본 조회 패턴
and 고객번호 = '123'
and :dt between 시작일 and 종료일
현재 시점을 조회할 때는 '99991231' 상수 조건을 이용해 '=' 조건으로 검색하는 것이 성능상 유리
and 종료일 = '99991231'
:dt 값이 '200050131' 일때
= and 시작일 <= '20050131' and 종료일 >= '20050131'과 동일
-> 레코드는 언제나 한 건뿐인데도 이력 레코드가 많이 쌓인 고객일수록 많은 인덱스 레코드를 스캔해야 한다.
그럴 때 index_desc 힌트를 주고 rownum <=1 조건을 추가해 준다면, 인덱스를 거꾸로 한 건만 스캔하고서 원하는 이력 레코드를 빠르게 찾을 수 있다.
select */+ index_desc(a idx_x01)
from 고객별연체금액 a
where 고객번호 = '123'
and '20050131' between 시작일 and 종료일
and rownum <= 1선분이력 스캔 효율을 높이는 방법 요약 -> [다시 보자!]
- 선분이력처럼 between 검색 조건이 사용될 때는 어느 시점을 주로 조회하느냐에 따라 인덱스 구성 전략을 달리 가져가야 한다.
- 최근 데이터 조회: [종료일 + 시작일] -> rownum <=1 조건 추가
- 과거 데이터 조회: [시작일 + 종료일] -> index_desc, rownum <=1 조건 추가
- 현재 시점 조회: 종료일 = '99991231'
(9) Access Predicate와 Filter Predicate
인덱스를 경유해 테이블을 액세스할 때는 최대 3가지 Predicate 정보가 나타날 수 있다.
- 인덱스 단계에서의 Access Predicate
- 인덱스 단계에서의 Filter Predicate
- 테이블 단계에서의 Filter Predicate
-
인덱스 단계에서의 Access Predicate
: 인덱스 스캔 범위를 결정하는 데에 영향을 미치는 조건절 -
인덱스 단계에서의 Filter Predicate
: 테이블로의 액세스 여부를 결정짓는 조건절
: 첫 번째 나타나는 범위검색 조건부터 이후 모든 조건절 컬럼들이 여기에 포함되며, 조건절에서 누락된 컬럼 뒤쪽에 놓인 인덱스 컬럼들도 포함된다. -
테이블 액세스 단계에서의 Filter Predicate
: 테이블을 액세스하고 나서 최종 결과집합으로의 포함여부를 결정짓는 조건절
(10) Index Fragmentation
B*Tree 인덱스의 'B'는 'Balanced'의 약자로서, 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도 읽는 블록 수가 같음을 의미한다. 즉, 루트로부터 모든 리프 블록까지의 높이가 동일하다.
불균형은 생길 수 없지만 Index Fragmentation에 의한 Index Skew or Spare 현상이 생기는 경우는 종종 있고, 인덱스 스캔 효율에 나쁜 영향을 미칠 수 있다.
Index Skew
Index Skew는 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다.
예를 들어 , 대량의 delete 작업을 마치고 나면 인덱스 왼쪽에 있는 리프 블록들은 텅 비는 반면 오른쪽 블록들은 꽉 찬 상태가 된다.
텅 빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조 상에는 그대로 남는다. 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그 곳에 입력될 새로운 값이 들어오면 언제든 재사용될 수 있다.
레코드가 모두 삭제된 블록은 언제든 재사용 가능하지만, 문제는 다시 채워질 때까지 인덱스 스캔 효율이 낮다는 데에 있다.
위와 같은 Index Skew 때문에 성능이 나빠지는 경우는 대게 Index Full Scan할 때다. 대량의 데이터를 매일 지웠다가 새로 입력하는 통계성 테이블일 때는 Index Skew가 발생하지 않도록 트랜잭션 패턴에 신경써야 한다.
delete문 직후에 커밋을 수행하면 지워진 블록들이 곧바로 insert 과정에 재사용된다.
Index Skew를 두려워해 트랜잭션을 짧게 정의하는 것이 바람직하지는 않지만 야간에 배치 프로그램을 통한 통계성 테이블의 경우 무방하다고 생각 Index Sparse
Index Sparse는 인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상을 말한다.
총 레코드 건수가 일정한데도 인덱스 공간 사용량이 계속 커지는 것은 대게 이런 현상에 기인한다.
Index Rebuild
Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나쁠 때는 coalesce 명령을 수행해 주면 된다.
alter index t_idx coalesce;coalesce 명령을 수행하면 인덱스 분할과 반대의 작업이 일어난다. 즉, 여러 인덱스 블록을 하나로 병합하고, 그 결과로서 생긴 빈 블록들은 freelist에 반환한다.
해당 명령을 수행해도 인덱스 세그먼트에 할당된 미사용 공간은 반환되지 않는다.
Index Fragmentation을 해소하면서 공간까지 반환하려면 shrink 명령을 수행하면 된다. (ASSM에서만 작동)
alter index t_idx shrink space;coalesce나 shrink는 레코드를 건건이 지웠다가 다시 입력하는 방식을 사용하므로 작업량이 많을 때는 rebuild 명령을 사용하는 편이 나을 수 있다.
alter index t_idx rebuild;이렇게 인덱스 구조를 슬림화하면 저장 효율이나 스캔 효율은 좋아지지만 일반적으로 인덱스 블로에는 어느 정도 공간을 남겨두는 것이 좋다. 인덱스 블록에 공간이 전혀 없으면 인덱스 분할이 자주 발생해 DML 성능을 떨어뜨리기 때문이다.
아래와 같이 예상효과가 확실할 때만 인덱스 rebuild를 고려하는 것이 바람직하다.
- 인덱스 분할에 의한 경합이 현저히 높을 때
- 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때. 특히 NL 조인에서 반복 액세스 되는 인덱스 높이가 증가했을 때
- 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 시간이 소요될 때
- 총 레코드 수가 일정한대도 인덱스가 계속 커질 때
08 인덱스 설계
(1) 가장 중요한 두 가지 선택 기준
- 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
- '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다.
(2) 인덱스 설계는 공식이 아닌 전략과 선택의 문제
- 쿼리 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하
- 저장 공간
- 인덱스 관리 비용 등
(3) 결합 인덱스 컬럼 순서 결정 시, 선택도 이슈
선택도가 액세스 효율에 영향을 주지 않는 경우
'=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미 없는 것이거나 오히려 손해일 수 있다.
IDX01: 고객등급 + 고객번호 + 거래일자 + 거래유형 + 상품번호
고객등급, 고객번호, 거래일자는 항상 사용between 조건으로 사용되는 거래일자 뒤쪽에 놓인 거래유형과 상품번호는 어차피 인덱스 필터 조건으로 사용되므로 변별력이 좋고 나쁨을 따질 이유가 없다.
선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우
고객등급을 선두에 두면 나중에 이 컬럼이 조건절에서 누락되거나 범위검색 조건으로 조회되는 일이 생기더라도 Index Skip Scan을 효과적으로 활용할 수 있어 유리하다.
상황에 따라 유.불리가 바뀌는 경우
선택도가 높은 컬럼(고객등급)을 선두에 두면 나중에 범위검색 조건이 사용되거나 아예 조건절에서 누락되더라도 Index Skip Scan or IN-List를 활용할 수 있어 유리하다. 다만 Distinct Value 개수가 충분히 적은지가 관건이다.
선택도가 낮은 컬럼(고객번호)을 선두에 두면 이를 범위검색 조건으로 조회하는 일이 생겼을 때 불리하지만, 입력 값의 범위가 좁다면 비효율이 크지 않아 Index Skip Scan이나 IN-List를 활용하지 못하더라도 오히려 유리할 수 있다.
선택도가 '낮은 컬럼'을 앞쪽에 두는 것이 유리한 경우
범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다.
-> 변별력이 좋은 고객번호로 스캔 범위를 최소화!
결론적으로 결합 인덱스 컬럼 간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효옹성이 높은지 등이 더 중요한 판단기준이다.
(4) 소트 오퍼레이션을 생략하기 위한 컬럼 추가
인덱스는 항상 정렬 상태를 유지하므로 order by, group by를 위한 소트 연산을 생략할 수 있도록 해 준다. 따라서 조건절에 사용되지 않은 컬럼이더라도 소트 연산을 대체할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수가 있다.
인덱스를 이용해 소트 연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락 없이 (뒤쪽 컬럼은 상관x) order by 절에 기술해 주어야 한다.
단, 인덱스 구성 컬럼이 조건절에서 '=' 연산자로 비교된다면, 그 컬럼은 order by 절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관없다.
09 비트맵 인덱스
-> 생략
