이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

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

2009.08.26 17:29

balto 조회 수:93302

(편집에 문제 있을 수 있으니 첨부파일 참조 바랍니다.) 

 

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

- 예제 : 어느 테이블을 클러스터링하는 것이 효율적인지..

 TAB1(COL1, otherkeys)

 TAB2(COL1, COL2, oterkeys)

 TAB3(COL1, COL2, COL3, otherkeys) : 대용량테이블, TAB1, TAB2와 자주조인

 TAB4(COL1, COL2, COL4, otherkeys) : 대용량테이블


4.2.1. 포괄적인 클러스터링

- TAB3 + TAB4를 클러스터링라면 -> M:M의 관계이므로 좋지 않다.

- TAB1 + TAB2 + TAB3 을 클러스터링할 경우 -> 1개의 TAB1로우, 여러 개의 TAB2로우, 다수의 TAB3로우로 클러스터링 체인이 많이 발생하여 비효율적. TAB1과 TAB2는 독립적으로 액세스해도 문제 없음. 결속력은 강화되지만 각각의 테이블 독립성은 떨어져서 TAB1이나 TAB2를 독립적으로 액세스하는 효율 저하.


4.2.2. 부분적인 클러스터링

- TAB1 + TAB2가 결합도가 높고 자주 조인한다면 클러스터링 해볼 만하다.


4.2.3. 단일테이블 클러스터링

- 조인의 효율성 향상을 위한 다중 클러스터링은 특정한 경우를 제외하고는 피한다.

- 단일테이블 클러스터링은 넓은 범위의 처리를 스캔방식으로 유도하는 좋은 방법이다.


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

- 단위클러스터의 크기를 어느 정도로 하면 좋은가?

- 예 :

 A형 : 클러스터 길이가 길고 로우의 길이가 짧다.

 B형 : 클러스터 길이가 짧고 로우의 길이가 길다.

 C형 : 클러스터 길이가 보통 로우의 길이가 짧다.

 D형 : 클러스터 길이가 짧고 로우의 길이가 짧다.

- 클러스터를 하는 테이블은 로우가 길지 않을수록 좋고(길면 클러스터에 로우가 몇 개 못 들어간다.), 단위클러스터가 지나치게 크지 않는 것이 좋다(크면 기억장소 효율성이 떨어진다). 단위 클러스터에 20-100개가 좋다.

- 단위클러스터는 32K까지 허용한다. 32K보다 작은 클러스터는 작은 크기만 배정된다.

- 8K블록에 2K로 단위클러스터가 지장되어 있으면 오버헤드 장소를 빼고 약 3개정도의 클러스터가 저장된다. 같은 키를 가진 클러스터가 많으면 블록에 저장되는 클러스터는 더 줄어든다.

- 단위클러스터에 저장될 데이터가 많다면 체인블록에 저장된다. 체인블록은 빈 블록 혹은 다른 클러스터가 사용하다 남는 블록이 된다.


[단위클러스터 계산 방법]

- 입력 인자들

 ① 블록당 유휴 저장공간 = (블록크기-헤더크기)*(100-PCTFREE)/100

  (예) 저장가능 공간 = (8,000Byte-100Byte)*(100-10)*100=7,110Byte

 ② 로우의 평균길이

  (평균길이 아는방법)

  ANALYZE TABLE <table_name> COMPUTE STATISTICS

  SELECT avg_row_len FROM user_tables WHERE table_name = <table_name>; 

  (예) SQL 실행결과 100Byte 가정하면 약 71 로우 저장 가능

 ③ 클러스터 키별 로우의 수

  (평균 로우수 알아내는 방법)

  SELECT AVG(CASE WHEN row_cnt > 71 THEN

    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);

 ④ 단위 클러스터 크기 산정 = AVG_ROW_LEN * average_row_count)

  (예) 클러스터키별 평균로우의 수가 약 20이라면 클러스터 크기 = 20*100Byte = 2,000Byte, 7,110/2,000=3.5이므로 블록에 약 3개까지 단위클러스터 저장 가능


[클러스터 생성 절차의 예]

 ① 클러스터 생성

  CREATE CLUSTER sales_cluster (sale_date varchar2(8))

   STORAGE (storage_clause...)

   PCTFREE 10 PCRUSED 60

   SIZE 2000

 ② 클러스터 인덱스 생성

  CREATE INDEX sales_cluster_index ON CLUSTER sales_cluster

   PCTFREE 2   STORAGE (INITIAL 20K NEXR 10K)

 ③ 기존 테이블의 명칭을 다른 이름으로 바꾼다.

  RENAME sales To sales_copy

 ④ 클러스터 내에 테이블을 생성한다.

  CREATE TABLE sales

  ( salesno varchar2(6) not null,

    sale_dept  char(4),

    sale_date varchar2(8) not null,

   ... )

  CLUSTER sales_cluster (sale_date);

 ⑤ 생성된 테이블 내에 데이터를 저장시킨다.

  INSERT INTO sales

   SELECT * FROM sales_copy

   WHERE sale_date <= '20010101';

 ⑥ 기존 테이블을 삭제시키고 새롭게 생성한 테이블에 다른 인덱스를 추가시킨다.

  DROP TABLE sales_copy;

  CREATE INDEX ...;


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

- 넓은 범위 처리의해결을 목적으로 생성하는 단일테이블 클러스터링에 주의할 사항

   (1) 클러스터 키 컬럼을 첫 번째로 하는 인덱스를 생성하지 말 것.

       클러스터 인덱스가 있음에도 클러스터 키 컬럼을 첫 번째로 하는 결합인덱스가 존재하면 클러스터를 사용하지 않고 결합인덱스를 사용하는 경우가 있을 수 있다.

 예) (sale_date + sale_dept) 결합인덱스가 있다면 아래 질의는 결합인덱스를 사용한다.

  SELECT SUM(count(*))

  FROM sales

  WHERE sale_date BETWEEN '20051201' AND '20051225'

   AND sale_dept like '110%';

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

 예) 다음 SQL 문을 클러스터를 사용하는 것이 유리하다고 생각되면 힌트를 추가한다.

 SELECT /*+CLUSTER(sales)*/ SUM(count(*))

 FROM sales

 WHERE sale_dept BETWEEN '20051201' AND '20051225'

    AND sale_dept='11200';