2.1 B-Tree인덱스

관계형 데이터베이스에서 가장 일반적으로 사용되는 인덱스

2.1.1 B-Tree인덱스의 구조

나무구조(Tree)처럼 가지에 해당하는 브랜치 블럭과 잎사귀에 해당하는 리프 블럭이 있다.
Unique Scan일 경우
range Scan일 경우
- 인덱스는 구성컬럼과 ROWID로 정렬되어 있다. ROWID에는 로우가 저장된 블록의 주소, 로우의 슬롯번호가 기록되어 있다.
- 처리할 범위의 첫번째 인덱스로우를 브랜체블록을 통해 리프블록을 액세스한 다음 거기의 ROWID로 블록을 찾는다.
  SGA부터 찾아보고 없으면 디스크에 액세스해서 복사본을 SGA에 복사한다
- 새롭게 액세스한 데이터블록정보를 PGA로 가져와서 해당 SQL의 처리버퍼에 넣는다 (일명 원 버퍼)
- 인덱스블록을 액세스한 내용을 PGA버퍼에서 찾아 다른 조건까지 검증하여 성공한 로우만 운반단위로 보낸다 만약 클러스터링 팩터가 좋다면 여러건의 인덱스 스캔을 하나의 버퍼에서 처리할 수 있다.
- PGA버퍼에서 찾을 수 없다면 다시 다른 블록을 SGA에 액세스하여 새로운 블럭을 담고 위의 처리를 반복수행한다.
- 리프블록에서 액세스한 로우가 처리범위를 넘으면 처리를 종료한다.

ROWID의 구성
- rowid라는 가상 컬럼에 저장된다.
- Single row accescc를 위한 row 고유한 주소
- 10자리로 구성

2.1.2 B-Tree 인덱스의 조작(Operation)

인덱스 생성(Creation)

테이블을 액새스하여 정렬을 수행
정렬된 결과를 인덱스 세그먼트의 리프 블록에 기록하기 시작
리프블록이 PCTFREE에 도달하여 새로운 블록을 요구하게 되면 브랜치블록을 생성
리프블록이 증가함에 따라 브랜치블록도 채워져 가다 블랜치블록이 PCTFREE에 도달하면 새로운 리프블록과 새로운 브랜치블록을 생성할때 루트블록이 생성된다
새로운 브랜치블록이 만들어질 때마다 루트블록에 브랜치블록의 DBA정보를 기록한다.

위와 같은 방법으로 인덱스가 저장되기 때문에 인덱스 블럭에 많은 로우를 담게 될 경우 리프 블럭 감소, 브랜치 블럭 증가 둔화(블럭의 깊이 감소)
- 최대한 인덱스 컬럼의 수를 줄인다
- 큰 블럭 사이즈(DB_BLOCK_SIZE)를 지정
- PCTFREE를 최소로 정의
- 압축을 활용하는 방법


  CREATE INDEX ord_customer_idx
  ON orders (customer_id, sales_id)
  COMPRESS 1;

인덱스 블럭의 분할(SPILT)

인덱스 로우는 정렬이 되어 저장 되어야 하는 이유 때문에 이미 생성된 구조에 새로운 로우가 삽입되면 기존의 위치에 파고 들어가는 문제 발생
마지막 값이 삽입되는 경우 : 그림처럼 PCTFREE에 도달한 블럭일 경우엔 새로운 블럭에 등록된다.
중간 값이 삽입되는 경우 : PCTFREE가 초과하게 되므로 분할이 발생하는데 어차피 2개의 블럭으로 수정되므로 2/3만 채우도록 하면서 양쪽을 모두 재편하게 됨(또 다른 중간 값이 들어와 계속 분할 되는 것을 방지하기 위함)
하지만 분할이 일어난 블럭에 새로운 값이 들어오지 않으면 저장공간 낭비를 초래하게 되고 이를 해결하기 위해선 인덱스를 재생성하는 방법뿐이다.

데이터의 삭제 및 갱신

데이터를 삭제했을 경우 테이블의 로우는 제거되지만 인덱스의 로우는 삭제되었다는 표시(flag)만 추가된다.(저장공간낭비와 스캔시 액세스 블럭 증가)
한 리프 블럭이 모두 삭제 되었을 경우 브랜치 블럭에 해당 리프 블럭을 가리키는 로우도 삭제 표시가 된다.
갱신할 경우 삭제와 삽입이 발생.
이러한 이유로 수정이 빈번하지 않는 컬럼을 인덱스로 사용권장
데이터처리(DML)가 많이 수행되는 테이블은 정기적으로 재생성을할 필요가 있다.

인덱스를 경유한 검색

lmc는 브랜치 블럭의 첫 번째 로우의 값보다 적은 값을 갖는 하위의 블럭의 주소정보(dba:data block address)를 말한다.
1. 루트 블록을 찾는다.
2. 주어진 값보다 같거나 최소값을 찾는다.(찾는값 >= 인덱스값)
  -. 찾는 값 < 인덱스 값 (Lmc에 있는 dba로 이동)
  -. 찾는 값 = 인덱스 값 (해당 dba로 이동)
  -. 찾는 값 > 인덱스 값 (검색 후 찾는 값 = 인데스 값이면 해당 dba로 이동 그렇지 않으면, 찾는 값 < 인데스 값을 만족하는 인덱스 최소값)
3. 리프 블록을 찾을 때까지 ② 의 단계를 반복해서 수행
4. 리프 블록에서 주어진 값을 가진 키를 찾아 존재하면 ROWID를 이용해 테이블을 액세스하고, 그렇지 않으면 'No Data found'로 결과 반환 만약, col2의 조건을 'ACC'가 아닌 'AC%'로 바꾸면
5. Col1 = 'B'이면서 Col2 = 'AC'보다 같거나 큰 것에서 스캔을 시작, 'AD' 보다 작으면 테이블을 액세스하고, 그렇지 않으면 종료한다.

2.1.3 리버스 키 인덱스

어떤 컬럼값의 각 바이트의 위치를 역전시키는 것을 말한다.
리버스키 인덱스에서는 정렬이 크게 달라지므로 클러스터링 팩터가 나빠진다는 것을 의미한다.
'='로만 액세스하는 경우에라면 크게 문제되것이 없다

문서에 대하여

이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^