(편집이 잘 안될 수도 있어 파일 첨부했습니다.)

 

4장 인덱스 수립전략

(무엇을 배우는가?)

- 최소의 인덱스로 최대의 액세스 형태를 만족시켜야 한다.

- 인덱스 구성을 위한 종합적인 전략을 수립하는 절차를 설명한다.

4.1. 인덱스의 선정 기준

4.1.1. 테이블 형태별 적용기준

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

- 적은 데이터란 DB_FILE_MULTIBLOCK_READ_COUNT 값 보다 크기가 작은 블럭을 가진 테이블이라 가정하자.

- 인덱스 유무가 성능에 영향을 미치지 않으므로 인덱스를 안 만들 수도 있다

  (인덱스를 생성하는 것이 나쁘다는 애기는 아니다.)

- 기본키에는 인덱스를 생성하는 것을 권장한다.

- 인덱스 존재 여부는 조인의 실행 계획이나 무결성에 영향을 미친다.

- 인덱스 일체형 테이블을 구성하는 것도 좋은 방법이다.(IOT)

- 대량의 반복이 발생할 때는 인덱스가 조금이라도 효과가 있을 수 있다.

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

- 고객테이블 같이 참조 위주 테이블, 데이터는 많고 액세스 조건이 일정하며, 랜덤액세스되며 데이터증감이 적다.

- 좁은 범위만 필요하거나 조인때 내측루프에 기본키에 의해 연결

- 인덱스를 많이 만들어 두는 것이 효과적이며, PCTFREE를 0으로 만들어도 좋음.

- 간혹 인덱스 재생성하면 된다.

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

- 매출정보테이블 같이 액세스 조건이 다양하고, 데이터가 꾸준히 증가한다.

- B-tree 인덱스가 유리하고 결합인덱스가 많이 사용되며 결합인덱스 컬럼 구성에 따라 영향을 받는다.

- 인덱스 전략 수립에 신중해야 한다. 액세스 유형을 잘 수집하고 인덱스 조합을 잘 찾는다.

- 인덱스 추가시 데이터 증감에 따른 속도 저하도 가능하므로 주의한다.

- 비트맵인덱스를 고려해볼 수도 있다.(배치처리로 다량의 데이터가 추가되고, 다양한 액세스가 발생한다면) ?

라) 저장용 대형 테이블

- 로그기록처럼 대량의 테이블에 지속적으로 데이터가 입력된다. 저장이 목적이며 액세스 형태가 다양하지 않다.

- 삭제나 갱신이 거의 없기 때문에 PCTFREE를 0으로 둘 수 있다.

- PRIMARY KEY를 두지 않을 수 있으며 필요시 UNIQUE INDEX를 생성해 준다.

- 파티션을 고려할 수 있다.(오래된 데이터는 참조 가능성이 줄어듦으로 최근의 파티션에 많은 인덱스를 생성하고 그렇지 않은 경우는 최소한의 인덱스를 생성한다.

- 특정파티션에 인덱스를 만들기 위해 USQBLE/UNUSABLE 기능을 사용한다.

  (UNUSABLE 파티션에 질의를 하면 오류이므로 FROM 절에 특정 파티션을 지정한다.

4.1.2. 분포도와 손익분기점

- 인덱스가 처리범위를 줄이는데 얼마나 효율적인가(충실한가는) 분포도와 관련있다.

- 보통은 컬럼의 분포도가 10-15%를 넘지 않아야 효과가 있다(왜?)

  (넘으면 전체 테이블 스캔이 더 유리하다)

   비교 : 전체테이블 100 블록 vs 인덱스 1블록 + 0.1 * ...

- 각 컬럼의 분포도는 높아도 결합인덱스의 분포도는 낮아진다.

- 클러스터링을 할 경우 클러스터링 팩터의 향상은 인덱스 액세스 비용을 낮춘다.

- 손익분기점을 넘는 경우라도 부분범위를 액세스할 경우 손익이 생긴다.

  (예, 30% 손익분기점이라도 1/100만 액세스했을 경우 이익이다)

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

- 검색조건이 한개의 테이블에 2개 속성인 경우 비교

(인덱스머지)

- 인덱스머지는 INDEX1과 INDEX2에서 검색하여 인덱스에 저장된 ROWID를 비교하여 검색(테이블 액세스하기 전에 ROWID를 모두 알아낸다)

- 그러나, 두 인덱스가 비슷한 분포도를 가질때 유리, 많이 차이나면 적은쪽 인덱스만 이용하여 테이블을 직접액세스하는 것이 유리(한쪽이 = 조건이 아닐 경우)

    x1 + x2 + selected blocks(c1,c2) > x1 + selected blocks(c1) 

  예를들면 : 1 + 3 + 1 > 1 + 2 (?)

(결합인덱스)

- 결합인덱스로부터 ROWID를 모두 찾아낸다)

4.1.4 결합인덱스의 특징

- 결합인덱스의 단점

   컬럼 중 일부만 조건을 받거나 = 아닌 연산자가 많을 경우

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

- 인덱스 결합시 분포도가 좋은 컬럼이 앞에 있는 것이 좋은가?

  =로 비교되면 순서는 별 영향이 없다. B-tree 검색시 영향을 받지 않는다.

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

- 첫번째 조건이 = 이면 첫번째 조건을 만족하는 것들 중 두번째 조건의 범위를 액세스 한다.

- 첫번째 컬럼이 = 이 아닌 범위 조건이 오면 첫번째 조건의 범위를 모두 검색하여 두번째 조건을 만족하는 것을 찾는다.

- 결론적으로 결합인덱스에서는 = 조건인 컬럼이 먼저 있어야하므로 분포도 보다 컬럼의 순서가 중요하다.

다) IN 연산자를 이용한 징검다리 조건

- 속성1이 = 검색이 주이고 속성2가 =이 아닌 경우가 많다면 (속성2, 속성1) 결합인덱스를 생성한다.

- 그러나 가끔씩 속성1이 LIKE나 BETWEEN을 사용하고 속성2가 =을 사용한다면 결합인덱스 (속성2, 속성1)을 또 만든다.(?)

- 결합인덱스에서 IN과 BETWEEN 연산자 차이 -> 결합인덱스에서 필요한 부분만 검색

- 결합인덱스에서 IN 연산자의 징검다리 효과(그림 1-4-5)

   WHERE col2 IN (111,112) AND col1 ='A'

    => WHERE (col2=111 AND col1='A') OR (col2=112 AND col1='A')

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

- = 비교가 아니지만 결합인덱스에 포함을 시킬지 여부

- = 비교가 아니어도 결합인덱스에 있으면 인덱스에서 검색을 시도하므로 ROWID를 직접 찾을 수 있다.

- SELECT 리스트에만 사용되는 컬럼도 경우에 따라서는 필요하다 - 인덱스조인을 유도(?)

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

- 결합인덱스 컬럼 순서 결정 우선순위

        1단계 : 항상 사용하는가?

        2단계 : 항상 = 로 사용하는가?

        3단계 : 어느 것이 더 좋은 분포도를 갖는가?

        4단계 : 자주 정렬되는 순서는 무엇인가?

        5단계 : 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

- 예) 다음 예에서 1개의 인덱스를 생성한다면?

        ① c1(=), c2(between), c3(=)

        ② c2(=), c3(between), c4(>)

        ③ c1(=), c2(=), c3(like)

        ④ c1(=), c2(=), c4(>)

        

        1단계 : 항상 사용하는가?

        => c2(4번), c1(3번), c3(2번), c4(2번)

           c2가 가정 먼저.

          그렇지만 c2이 카디널리티가 높지 않아 인덱스스킵스캔으로한다면 ②번은 c1(=)이 있다고 본다. 정답은 c1이 가장 먼저.

          아니면 ② 번을 따로 만족하는 인덱스를 만든다면 c1=c2=3번이 된다.

        2단계 : 항상 = 로 사용하는가?

        => c1=c2=3회라고 가정하면 c1은 항상 =를 사용하므로 정답은 c1+c2

        3단계 : 어느 것이 더 좋은 분포도를 갖는가?

        => ①번의 c2를 BETWEEN을 IN으로 바꾸면 = 처리가 가능하므로 c1+c2, c2+c1 2개의 정답이고, c1과 c2 중 분포도가 좋은 것을 앞으로한다.

주의 : 액세스 형태가 바뀌면 인덱스도 바뀌어야하므로 액세스형태를 예측하여 바꾼다. 예를 들면 =가 아닌 다른 연산자 사용 가능성을 검토한다.

        4단계 : 자주 정렬되는 순서는 무엇인가?

        => 정렬이 필요한 속성을 감안한다. 정렬이 필요한 속성을 인덱스로 했을 경우 부분범위처리에도 유리하다. 

        5단계 : 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

        => c3과 c4는 체크기능을 담당한다. c3가 앞에오면 유리한 경우는 ①③번, c4가 앞에오면 유리한 경우는 ④번, 같은 경우는 ②번 경우이므로 c1+c2+c3+c4로 생성.

        만약 ④가 빈번하게 사용된다면 c1+c2+c4로 생성.