새로쓴 대용량 데이터베이스 솔루션 - 제 1부 액세스 영향요소의 이해4-1

제 4장 인덱스 수립 전략

4.1 인덱스의 선정 기준

4.1.1 테이블 형태별 적용기준

가) 적은 데이터를 가진 소형 테이블

- DB_FILE_MULTIBLOCK_READ_COUNT 값 이하 블록 크기의 테이블

- 한번의 멀티블럭 I/O에 의해 인덱스 없이 전체 테이블 스캔 가능

- 다른 테이블들과 연결될 경우 인덱스 유무는 옵티마이저에 영향

- 특히 조인에서 내측루프로 수행되면 작은 테이블이라도 인덱스가 없으면 심각한 문제 발생 가능

 

나) 주로 참조되는(Referenced) 역할을 하는 중대형 테이블

- 트랜잭션 데이터의 행위 주체, 목적이 되는 개체들로 구성이 된 테이블(키 엔터티 집합, ‘고객’ 등)

- 데이터 증감이 적고 검색 위주의 액세스 발생, 검색 조건의 형태가 고정적

- 블록 내 최대한 조밀하게 인덱스를 저장하기 위해 PCTFREE를 0에 가깝게 부여

 

다) 업무의 구체적인 행위를 관리하는 중대형 테이블

- ‘매출정보’ 와 같이 업무의 구체적인 수행 내용을 담고 있는 트랜잭션 테이블

- 주로 B-TREE 결합 인덱스로 구성, 결합인덱스의 컬럼 구성 순서에 따라 효율성의 차이가 크게 나타남

- 최소 인덱스로 최대

라) 저장용 (Log성) 대형 테이블

- 로그성 데이터 관리 목적의 테이블

- 저장 우선, 갱신 거의 없으므로 PTCFREE 여유공간 불필요, PRIMARY KEY 제약조건 불필요

- 식별자 키 필요하면 UNIQUE INDEX 생성, 파티션 사용 고려

 

4.1.2 분포도와 손익분기점

- 인덱스 생성 목적 : 전체 집합에서 범위를 줄여 선별된 부분만 액세스 하고자 함

- 분포도 :전체 집합에서 해당 컬럼으로 조회 시 해당 조건이 차지하는 비율을 의미

- 손익 분기점 : 인덱스는 단일 블록 I/O를 유발하므로 분포도가 높은 경우에는 인덱스가 존재한다 해도 사용하지 않을 수 있음

인덱스 사용여부를 결정하는 분포도 값. 옵티마이저는 손익분기점 이상이면 사용하지 않고 이하가 되면 사용.

- 최선의 방법은 처리범위가 가장 적은 것을 처리주관 조건으로 사용하도록 하는 것

- 인덱스 분포도가 높으면 무조건 인덱스를 생성하지 않는 것이 유리한가?

: 반드시 그렇지는 않고 부분 범위 처리를 할 경우 실제 분포도는 아주 낮아질 수 있음

: 일반적으로 분포도 10~15% 이하면 칼럼은 인덱스 사용, 이상이면 전체 테이블 스캔이 유리

( 기준%는 전체테이블 액세스 하는 것과의 손익분기점 커트라인 개념과는 다름 )

 

4.1.3 인덱스 머지와 결합 인덱스 비교

- Index Merge : 여러 인덱스가 협력하여 같이 액세스 주관

장점 : 처리 범위가 넓은 경우, 테이블에 액세스 하여 Filter하는 부분이 생략됨. 비교하는 조건으로 인덱스만 사용

 

- 결합 인덱스 : 여러 컬럼을 모아 하나의 인덱스로 만드는 방식

장점 : 모든 조건이 인덱스 컬럼에 사용되는 경우 인덱스 머지에 비해 아주 효율적으로 수행 가능 (단, 선두 컬럼의 Equal 연산에만 적용)

결합 인덱스와 인덱스 머지 방식의 연산 차이

 

4.1.4 결합인덱스의 특징

- 인덱스의 첫 번째 컬럼이 조건절에 없다면 일반적으로 인덱스는 사용되지 않음(예외. Oracle 9i 이후 지원되는 Index Skip Scan의 경우는 가능)

- Equal 연산이 아닌 검색 조건이 들어오는 경우(범위 연산), 처리 범위가 크게 증가하여 효율이 크게 저하될 수 있음

 

가) 분포도와 결합 순서의 상관 관계

(구성)

- 테이블: TAB1, 컬럼: COL1, COL2

- 컬럼 COL1: 분포도가 좋지 않음

- 컬럼 COL2: 분포도가 좋음

- 사용된 SQL

SELECT * FROM TAB1
WHERE col1=’A’
AND col2 BETWEEN ‘113’ AND ‘115’;

 

- 액세스 방식에 따른 구분

- 분포도가 좋지 않은 col1 컬럼을 복합인덱스 선행 컬럼으로 설정한 경우 처리과정 (왼쪽)

1. col1=’A’ AND col2=’112’ 인 데이터를 B-Tree 방식으로 바로 찾음

2. ROWID를 이용하여 테이블 로우 액세스

3. 다음 로우를 탐색하여 조건에 만족하면 다시 테이블 로우를 액세스 하고 그렇지 않으면 스캔 종료(이 경우, 한번 랜덤, 2개의 인덱스 로우 스캔)

 

- 분포도가 좋은 col2 컬럼을 복합인덱스 선행 컬럼으로 설정한 경우 처리과정 (오른쪽)

1. col2=112 AND col1='A' 인 데이터를 B-Tree 방식으로 바로 찾음

2. ROWID를 이용하여 테이블 로우 액세스

3. 다음 로우를 탐색하여 조건에 만족하면 다시 테이블 로우를 액세스 하고 그렇지 않으면 스캔 종료(이 경우, 한번 랜덤, 2개의 인덱스 로우 스캔)

 

나) 이퀄(=)이 결합 순서에 미치는 영향

(구성)

- 테이블: TAB1, 컬럼: COL1, COL2

- 컬럼 COL1: 분포도가 좋지 않음

- 컬럼 COL2: 분포도가 좋음

- 사용된 SQL

SELECT * FROM TAB1
WHERE col1=’A’
AND col2 BETWEEN ‘113’ AND ‘115’;

 

- 액세스 방식에 따른 구분

- 분포도가 좋지 않은 COL1 컬럼을 복합인덱스의 선행 컬럼으로 설정한 경우 처리 과정(왼쪽)

1. col1='A' AND col2=113 인 첫번째 로우를 찾음

2. ROWID를 이용하여 테이블의 로우를 액세스

3. 다음 로우를 차례로 스캔하면서 col1 !='A' OR col2 > 115 일 때 까지 테이블의 로우를 엑세스 하고 그렇지 않으면 처리를 종료

: 이 경우, 첫 번째 컬럼이 Equal 연산이고 두 번째 컬럼에는 정렬이 되어 있으므로 between이 들어갔다 하더라도 필요한 액세스만 하게 됨

 

- 분포도가 좋은 COL2 컬럼을 복합인덱스의 선행 컬럼으로 설정한 경우 처리 과정(오른쪽)

1. col2=113 AND col1='A' 인 첫번째 로우를 찾음

2. ROWID를 이용하여 테이블의 로우를 액세스

3. col2 > 115 일 때 까지 계속해서 스캔한다. 스캔한 로우는 col1='A' 인지 체크하여 성공하면 ROWID를 이용하여 테이블을 액세스

4. col2 > 115 이면 처리를 종료

: 이 경우, col2 에 대해서는 체크만 하는 이유는 이 컬럼에 대해서는 정렬이 되어있지 않기 때문

체크로 인해 ROWID를 이용한 테이블 엑세스만 줄어듬

 

아래의 경우에서 col1 컬럼에 대해서만 인덱스가 있는 경우에서 차이를 볼 수 있음

11번의 ROWID 스캔에서 3번의 ROWID 스캔으로만 줄고, 실제 인덱스 내부 처리 범위 내에서는 효율 X

 

- 결론: 인덱스의 처리 범위를 고려해야 하며 컬럼의 분포도를 고려한 인덱스 컬럼 선정 방식은 맞지 않음

조회 시 사용되는 컬럼에 Equal를 많이 쓰는 경우 처리 범위를 크게 줄여주므로 해당 컬럼을 인덱스 선행 컬럼으로 두어야 함.

 

다) IN 연산자를 이용한 징검다리 효과

(적용 예)

1. 비효율적인 인덱스가 이미 큰 테이블에 대해 사용하고 있는 경우 (위 예에서 col2, col1 순으로 생성된 결합 인덱스)

2. 올바르게 컬럼 순서로 생성된 인덱스의 쿼리 유형에 상반되는 컬럼 순서의 쿼리가 가끔 사용되는 경우

  ( col1(=), col2(between) 순서로 들어오는 쿼리가 대부분이고 인덱스고 그 순서로 생성되어있는데, 가끔 col2(=), col1(between) 과 같은 쿼리도 수행되는 경우)

 : 이 경우, 새롭게 인덱스를 생성하는 것은 인덱스의 중복 투자이므로 올바르지 않음. IN 연산자를 통해 기존 인덱스 만으로도 효율적인 결과를 가져올 수 있음

사용 예제

 

- 기존의  BETWEEN 연산을 이용한 경우

 col2 컬럼 기준으로 넓은 범위 스캔. col1 컬럼의 필요 컬럼인 'A'만을 뽑아내지 못함.

  : 이 경우 넓은 범위에 해당되는 모든 인덱스를 읽으므로 선을 그은 것과 같다 하여 '선(line)연산' 이라고도 함

 

- IN 연산을 이용한 경우

 INLIST ITERATOR(111,112)

1. col2=value AND col1='A' 인 첫번쨰 로우를 찾음. 이 때, 인덱스 트리를 탐색하는 과정 포함

2. ROWID 탐색 후 테이블 데이터 추출

3. col2!=value OR col1!='A' 일 때 까지 테이블 로우를 액세스 하고 만족하지 않으면 다음의 Iterator 값을 뽑아 1번 과정을 반복. 만약 값이 없는 경우는 전체적인 탐색 종료

 : 위 연산은 IN이 가니는 연산의 ㄴ특징으로 col2, col1 컬럼의 equal 연산을 여러번 수행하여 불필요한 인덱스 값을 읽는 것을 방지. IN 리스트 내부의 데이터들로 인해 필요한 인덱스 값만 읽게 되는데 해당 인덱스에 점을 찍은 것과 같다 하여 '점(point)연산' 이라고도 함

 

- 위 IN 리스트를 사용한 쿼리는 다음의 OR를 사용한 쿼리와 거의 동일한 수행방식을 가짐

SELECT * FROM TAB1 WHERE (COL2=111 AND COL1='A') OR (COL2=112 AND COL1='A')

 

라) 처리 범위에 직접적인 영향을 주지 못하는 컬럼의 추가 기준

 

4.1.5 결합 인덱스의 컬럼 순서 결정 기준

 

4.1.6 인덱스 선정 절차

1) 테이블의 액세스 형태를 최대한 수집

2) 인덱스 대상 컬럼의 선정 및 분포도 조사

3) 특수한 액세스 형태에 대한 인덱스 선정

4) 클러스터링 검토

5) 결합인덱스 구성 및 순서 결정

6) 시험생성 및 테스트

7) 수정이 필요한 어플리케이션 조사 및 수정

8) 일괄 적용