4.1.6 인덱스 선정절차

가) 테이블의 엑세스 형태를 최대한 수집

▶ 개발 단계에서의 엑세스 형태 수집

1) 반복 수행되는 엑세스 형태를 찾는다.

- 반복 수행되는 엑세스는 자신의 수행속도에 반복횟수를 곱한 만큼의 부하를 가져오므로 수행속도에 미치는 영향이 아주 크다.

- Nested Loop Join, Sub-Query, Fetch후 루프 내에서 반복되는 쿼리

2) 분포도가 아주 양호한 컬럼들을 발췌하여 엑세스 유형을 조사한다.

- 테이블에는 아주 양호한 분포도를 가진 컬럼이 있기 마련이다.

- 인덱스 컬럼 순서를 정할 때등 여러 경우에서 크게 활용될수 있음

3) 자주 넒은 조건이 부여되는 경우를 찾는다.

- 인덱스는 넓은 범위를 처리하게 될 때 많은 부담을 주게 된다.

- 전체 범위 처리 시, 넓은 범위 처리는 인덱스 사용 때 수행속도가 좋지 않을 수 있음.

- 처리 범위의 최대 크기와 평균 예상 범위, 자주 사용되는 정렬의 순서, 처리 유형을 수집하면 유용한 자료가 될 것임

4) 조건에 자주 사용되는 주요 컬럼들을 추출하여 엑세스 유형을 조사한다.

- 빈번하게 사용된다는 것은 시스템 전반에 미치는 영향이 그 만큼 크기 때문에 아주 중요하다.

5) 자주 결합되어 사용되는 컬럼들의 조합 및 정렬되는 순서를 조사한다.

- 업무적인 측면에서 각 컬럼들간의 상호관계를 잘 파악해 보면 특정 컬럼들끼리 자주 조합하여 사용되는 경합형태를 찾을 수 있다.

- 중심이 되는 컬럼을 찾아 이와 연관되는 컬럼들을 검토

6) 역순으로 정렬되어 있는 경우를 찾는다.

- End User는 조건의 범위는 넓게 부여하면서 최근의 데이터만 보려고 하는 경우가 많음. 빠른 응답까지 요구함

7) 통계 자료 추출을 위한 엑세스 유형을 조사한다.

- 통계자료를 추출하는 경우는 대개 범위가 넓다.

- 넓은 범위 처리가 대부분 임. 클러스터나 잘 조합된 결합 인덱스 이용하는 것이 좋음.

▶ 운영 단계에서의 엑세스 형태 수집

시점: Test 단계 또는 정상 가동 중인 시스템의 인덱스 교정 시점

1) 어플리케이션 소스 코드에서 SQL을 추출하여 분석용 테이블에 보관한다.

- 기능: 어플리케이션을 분석하여 SQL 문장을 Repository 에 저장. 손쉽게 SQL 문장을 가져올 수 있음.

- 한계: 동적 SQL 과 같은 쿼리는 가져오는데 한계가 있으며, 수행 횟수와 같은 자세한 사항은 알 수 없음

2) SQL-Trace 파일을 파싱하여 SQL 문장 뿐만 아니라

실행계획, 현재 적용되고 있는 인덱스 , 실행 횟수, 처리 범위등의 매우 상세한 정보 획득할 수 있다.

- 기능: 전체 커다란 trace 결과에 대해 하나의 상세한 레포트도 얻을 수 있으며 상용 SW에 따라 Advice 역할 까지 해주기도 함.

- 한계: 시스템에 큰 부하를 줌. 그러므로 trace 기간은 제한 되어야 하며 그 기간내의 분석 밖에 행할수 없음

3) 공유 SQL 영역에서 직접 SQL을 찾아오는 방법은 보다 SQL 수집을 간편하게 한다.

- 기능: 서버에 부담을 덜 주므로 장기간 수집하는 것이 가능하다.

- 단점: trace에 비해 정보가 빈약하다.

어떤 어플리케이션에서 수행되었는지와 같은 장기간 수집을 하더라도 여전히 모든 SQL 구문 수집하는 것이 어렵다.

▶ 수집된 SQL을 테이블 별로 출력하여 엑세스 형태 기록

- 유사한 형태의 SQL들이 정렬되도록 출력

- SQL문장 별로 정렬하는 것 보다 조건절에 사용된 컬럼별로 정렬하는 것이 보다 유리

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

1) 인덱스 대상 컬럼 선정 및 분포도 분석

- 인덱스 대상 컬럼 기준

- 엑세스 유형에 자주 등장하는 컬럼

- 인덱스의 앞부분에 지정해야 할 컬럼

- 기타 수행속도에 영향을 미칠 것으로 예상되는 컬럼

2) 현행 인덱스 존재 시, 조사한 엑세스 형태가 어떤 인덱스를 타는지 조사

사용 인덱스 이외에 해당 연산 수행 시 예상되는 범위 또한 조사

다) 특수한 엑세스 형태에 대한 인덱스 선정

반복해서 엑세스 되는 형태(Critical Access Path)를 찾아내는 것이 우선으로 해야할일임

라) 클러스터링 검토

넓은 범위 처리를 자주하는 컬럼에 대해 클러스터링 검토

클러스터링 = 슈퍼스타 영입

인덱스 : 클러스터링 = 안타 : 홈런

'단일테이블클러스터링'을 말함

-대량의 동일 컬럼값을 가진 로우들을 하나의 클러스터에 저장하여 클러스터링 팩터를 향상

-오로지 검색의 속도향상을 위한 클러스터링이므로 대량의 트랜잭션처리되는 경우는 부하발생

-SALE_DEPT + SALEDATE

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

앞서 설명한 결합 인덱스 순서 구성 원리에 따라 선정

항상 사용하는가?
항상 ‘=‘로 사용하는가?
어느 것이 더 좋은 분포도를 가지는가?
자주 정렬되는 순서는 무엇인가?
부가적으로 추가시킬 컬럼은?

바) 시험 생성 및 테스트

사) 수정이 필요한 애플리케이션 조사 및 선정

4.2 클러스터링 형태의 결정 기준

테이블 가정

- TAB3, TAB4는 대용량의 데이터를 가짐

- TAB3 는 매우 빈번하기 COL3의 대량을 처리범위에서 TAB2와 TAB1을 조인

- TAB4 는 COL4로 자주 넓은 범위를 처리

4.2.1 포괄적인 클러스터링

두 테이블이 아닌 그 이상을 결합하는 형태

지나치게 강한 결합은 독립성을 저하

다른 집합과의 유연성 저하

- TAB1 + TAB2 + TAB3

4.2.2 부분적인 클러스터링

결합도가 매우 강한 테이블만 클러스터링

원래 하나의 테이블이 정규화로 분할된 테이블

다른 집합과는 거의 조인하지 않는 경우

- TAB1 + TAB2(COL1)

- CLUSTER(TAB3)

4.2.3 단일테이블 클러스터링

주로 넓은 범위처리가 빈번하게 발생하는 경우

넓은 범위 처리를 스캔방식으로 유도하려는 경우에 적용

4.2.4 단위 클러스터의 크기 결정

1. 블록당 유효 저장공간 크기 = (블록크기 – 블록헤더 크기) * (100-PCTFREE)/100

- (8000-100)*0.9=7110

2. 로우의 평균 길이 산정

- 평균로우 길이 100바이트로 가정

3. 클러스터키 별 로우 수를 구한다.

SELECT AVG( CASE WHEN row_cnt > 71 THEN 71 ELSE row_cnt END)
       average_row_count
FROM ( SELECT cluster_key_columns, count(*) row_cnt
       FROM table_name
       WHERE sampling_conditions....
       GROUP BY  cluster_key_columns );

4. 단위 클러스터의 크기를 계산하고 결정한다.

- AVG_ROW_LEN * average_row_count

- 7110/2000 =3.5

- 블록에 3개까지 단위클러스터가 존재.

클러스터링 생성절차 예 ( SALES TABLE )

1. 클러스터 생성

     CREATE CLUSTER sales_cluster (sale_date varchar2(8)) 
     STORAGE ( storage_clause……)
     PCTFREE 10 PCTUSED 60
     SIZE  2000;

2. 클러스터 인덱스 생성

    CREATE INDEX sales_cluster_idx 
    ON CLUSTER sales_cluster
    PCTFREE 2
    STORAGE (INITIAL 20K NEXT 10K);

3. 기존테이블 명칭 변경

    RENAME  sales TO  sales_copy ;

4. 클러스터내에 테이블 생성

   CREATE TABLE sales
   ( saleno     varchar2(6) not null,
     sale_dept  char(4),
     sale_date  varchar2(8) not null,
     …………………………………………………………… )
   CLUSTER  sales_cluster  (sale_date) ;

5. 테이블 내에 데이터 저장

   INSERT INTO sales
   SELECT * FROM sales_copy 
   WHERE sale_date >= '20010101';

6. 기존 테이블 삭제, 새로운 인덱스 생성

   DROP TABLE sales_copy  ;
   CREATE INDEX  …………………………………………………;

4.2.5 클러스터 사용을 위한 조치

- 클러스터 키 컬럼을 첫번째로 하는 결합 인덱스가 존재하면

경우에 따라 클러스터를 사용하지 않고 결합 인덱스를 사용하는 실행계획을 수립

- 클러스터가 반드시 사용되어지기를 원한다면 액세스 경로를 고정시킨다.

   SELECT SUM(count(*)) 
   FROM   sales
   WHERE sale_date between ‘20051201’ and ‘20051225’
   AND sael_dept = ‘11200

- 클러스터 사용이 유리할 경우 힌트 사용하여 실행계획 고정

  SELECT /*+ CLUSTER(sales) */
         SUM(count(*))
  FROM   sales
  WHERE sale_date between ‘20051201’ and ‘20051225’
  AND sael_dept = ‘11200’

목차