새로쓴 대용량 데이터베이스 솔루션 - 제 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)를 채워서 검색

 

다. 가공처리 결과의 검색

 - 복잡한 계산 결과의 검색

 - 말일, 단가, 율의 검색

 - 기간, 컬럼 길이 검색

 

라. 오브젝트 타입의 인덱스 검색

 

마. 배타적 관계의 인덱스 검색

 - 배타적 관계의 유일성 보장

 - 배타적 관계의 결합 인덱스