새로쓴 대용량 데이터베이스 솔루션 - 제 1부 액세스 영향요소의 이해2
제 2장 인덱스의 유형과 특징
2.1. B-Tree index
2.1.1. B-Tree index의 구조
- Root/Branch/Leaf Block으로 나누어져 있음
- Rowid의 구조
* Data Obbject number : database segment 식별 정보
* Relative File number : tablespace에 상대적 datafile 번호
* Block number : Row를 포함하는 data block 번호
* Row number : block에서의 row의 slot
- 인덱스 블록의 감소 전략
* 최대한 인덱스 컬럼 수를 줄인다
* 큰 블록 사이즈(DB_BLOCK_SIZE)를 지정한다
* RCTFREE를 가능한 최소로 정의한다
* 키 압축(Key compression)을 활용 (특히, 유일 인덱스, 결합 인덱스)
- SQL server의 RID
* 8byte = Page Address(4) + file ID(2) + slot number(2)
2.1.2 B-Tree index의 조작
가) index Creation
➀ 데이터 정렬 후 인덱스 세그먼트의 리프 블록에 기록
➁ 리프블록이 PCTFREE에 도달하면 새로운 리프블록과 브랜치 블록 생성
➂ 리프 블록이 추가될 때마다 브랜치 블록에 로우를 추가하며 작업 계속
➃ 브랜치 블록이 PCTFREE에 도달하면 새로운 브랜치 블록과 루트 블록 생성
- PCTFREE의 2/3만 채운 후, 중간 값 삽입 =>
- 결합인덱스 구성시 => ‘발생일자+항목’ 으로 구성하는 것이 ‘항목+발생일자’ 보다 낫다.
즉, 인덱스 컬럼 순서에 따라 분할에 미치는 영향이 다름
- 결합인덱스의 주기적인 Rebuild가 필요하게 됨
나) 데이터의 삭제 및 갱신
- 삭제 시
* 삭제 Flag만 표시하므로 저장 공간 낭비, 범위 스캔 대상 증가
* 리프 블록의 모든 로우가 삭제되더라도 범위 스캔 시에는 액세스 될 수 있음
- 갱신 시
* 로우의 삭제와 삽입이 동시에 진행됨(삭제는 Flag만 표시)
* 저장 공간의 낭비와 트리 구조의 깊이가 증가
=> 인덱스의 재생성으로 삭제/갱신 문제 해결(DML 처리가 많이 수행되는 테이블은 정기적인 재생성 필요)
2.1.3 리버스 키 인덱스(Reverse Key Index)
- 어떤 컬럼값의 각 바이트 위치를 역전시킴
* 예) ‘12345’ -> ‘54321’
2.2 비트맵 인덱스
2.2.1 비트맵 인덱스 탄생배경
- B-Tree index에서는 컬럽값을 인덱스에도 보관해야 하므로 중복 발생
- B-Tree index에서는 컬럼값의 분포도가 좁아야 한다는 것. 분포도가 넓은 경우를 해결하기 위해 여러 컬럼들을 결합하여 인덱스 생성
- 결합 인덱스는 조건을 사용하지 않는 컬럼, ‘=’ 조건이 아닌 경우가 있으면 액세스 효율이 크게 저하 => 해결을 위해 중복된 인덱스 구성
- 데이터 웨어하우스에서처럼 카디널리티가 낮은 다수의 디맨전이 요구에 따라 다양한 결합을 하는 경우 엄청난 개수의 인덱스가 동원됨
- B-Tree index의 한계로 NULL값, NOT의 부정조건, 복잡한 OR조건을 포함한 경우 인덱스로서의 가치를 발휘할 수 없음
=> 위에 기술한 부분에 대한 탁월한 대안을 제시함
2.2.2 비트맵 인덱스의 구조와 특성
- 구조
* 루트/브랜치/리프 블록으로 구성된 것은 동일하나 마지막 리프 블록이 비트맵으로 구성되어 있음
* 선분형태(start rowid ~ end rowid)로 저장됨
* 조건에서 부여한 값을 찾을 때는 브랜치 블록에서 해당 조건의 리프 블록만 액세스
* 카디널리티가 높지 않고 동일 값의 반복 정도가 많으면 천만 건의 테이블의 비트맵이 10M를 넘지 않음
- 특성
* and, or, null 연산 용이
* null 값도 bit를 구성하고 있음
* like, between, >, <, <=,,> 연산은 용이하지 않음
* 잦은 수정이 발생하는 컬럼은 인덱스의 크기가 증가함
* Block level locking으로 많은 부하 유발 가능성
* 커디널리티가 높은 컬럼에 대해선 비트맵 인덱스 장점이 적어지므로 OLTP업무에는 적합하지 않음.
2.2.3 비트맵 인덱스의 액세스
- 비트맵 실행계획의 단계들
2.3 함수기반 인덱스(FBI, Function-Based Index)
2.3.1 함수기반 인덱스의 개념 및 구조
➀ 테이블의 가공한 논리적 컬럼을 인덱스로 생성한 것
➁ Index Column의 변형에 유현하게 사용할 수 있는 인덱스
- 함수나 수식 결과로 B-Tree OR Bitmap 인덱스 생성
- CBO에서만 사용 가능
- Index 생성 후 통계정보 생성 필수
➂ B-tree, bitmap 인덱스 모두 가능
- 사용 가능 함수 : 산술식, 사용자 지정 함수, Built-in 함수, 패키지 등
- SUM, AVG 등의 그룹함수는 사용 불가
➃ 옵티마이저가 쿼리를 파싱하면서 FBI의 사용 가능 여부를 판단
➄ 검색 효율 향승을 위해 효과적이나, FBI 구성 컬럼에 대한 빈번한 입력, 수정은 부하 가증
➅ SQL문에 사용된 Expression을 Parsing하여 일치하는 Exp를 찾고 Exp Value를 비교하며, Exp value에 대해 Case Senstive 함
➆ Dictionary View에서 Index Column 정보 확인 가능
- All(user)_indexs, All(user)_ind_columns, All(user)_ind_expressions
- 가공된 index Column은 system이 새로운 이름을 부여 : SYS_NCnnnn$
2.3.2 함수기반 인덱스의 제약사항
- 사용자 지정함수는 ‘DETERMINISTIC’로 선언
- QUERY_REWRITE_ENABELD = TRUE
- QUERY_REWRITE_INTEGRITY = TRUSTED
- 필수권한 : INDEX CREATE / ANY INDEX CREATE / QUERY REWRITE / GLOBAL QUERY REWRITE
- 함수나 수식의 결과가 NULL인 경우 사용하지 않음
- 사용자 지정 함수 재정의 시 -> Disabled(~9i), Disabled or 변경 전 함수 유지(10g)
- Owner의 Execute 권한이 Revoke 되면 사용불가
- Disabled된 인덱스를 사용하려 하면 SQL은 실패
* ALTER INDEX ... ENABLE / ALTER INDEX ... REBUILD
* ALTER INDEX ... UNUSABLE <- SKIP_UNUSABLE_INDEXES = TRUE 필수
- 스칼라 서브쿼리로 표현된 컬럼은 함수기반 인덱스를 생성 불가
- 가상 컬럼이 포함되면 함수기반 인덱스 생성 불가
- 파티션 키를 함수기반 인덱스에 사용할 수 없음
- 내부적으로 datatype 변경 발생
- NLS 파라메터는 현재 기준으로 적용(단, NLS-SORT와 NLS_COMP는 세션 레벨 정의와 상관 없음)
- WHERE 절의 교환법칙 적용 후 인덱스 사용가능
2.3.3 함수기반 인덱스의 활용
가. 테이블 설계상의 문제를 해결
- 컬럼의 중간 부분의 검색
* SUBSTR 함수를 사용하여 원자단위로 데이터 모델링을 하는 경우 자주 결합되어 사용되는 다른 컬럼과 결합하여 인덱스를 생성한다.
* CREATE INDEX from_loc_idx ON orders (SUBSTR(ship_id,5,3));
- 조인 연결고리 컬럼이 대응하지 않는 경우의 해결
- 일자 컬럼이 분할된 경우의 해결
- 데이터 타입이 상이한 조인 컬럼
- 조인 컬럼이 경우에 따라 달라지는 경우의 조인
- 부모 테이블의 컬럼과 결합한 인덱스 생성
나. 오류 데이터의 검색 문제를 해결
- 대,소문자나 공백이 혼재된 컬럼의 검색
- NULL값을 치환하여 검색
- 접두사(Prefix)를 채워서 검색
다. 가공처리 결과의 검색
- 복잡한 계산 결과의 검색
- 말일, 단가, 율의 검색
- 기간, 컬럼 길이 검색
라. 오브젝트 타입의 인덱스 검색
마. 배타적 관계의 인덱스 검색
- 배타적 관계의 유일성 보장
- 배타적 관계의 결합 인덱스