꿈꾸는 개발자, DBA 커뮤니티 구루비

I. Overview

1. Clustering factor

Fig 1. Clustering Factor

정의

데이터가 테이블 전체에 무작위로 분산된 정도. 즉 테이블 내 데이터의 흩어진 정도를 숫자로 표시
인덱스를 스캔하는 동안 방문(access)하는 테이블의 데이터 블록의 개수

좋은 clustering factor

인덱스 키의 순서가 테이블 row의 순서와 비슷

나쁜 clustering factor

인덱스 키의 순서가 테이블 row의 순서와 맞지 않은 정도가 심함

정렬 계수(sort factor)가 아님

거의 정렬된 채로 rows 간에 서로 근처에 모여(군집)있기만 하면 됨

인덱스와 테이블의 정렬 순서가 반드시 일치하지 않아도 서로 모여있는 정도가 같으면 clustering factor가 좋음

Fig 2. Table and Index Order

2. Cluster

정의

하나이상의 테이블 그룹이 같은 데이터 블록을 공유해서 저장하는 방법

Fig 3. Clustered Table Data

장점

공유 column(조인의 연결고리)이 있는 테이블을 cluster화하여 수행속도를 향상
Disk I/O감소 : cluster된 테이블의 조인 시 접근 시간이 단축됨
저장 공간 절약 : 각각의 cluster key 값은 cluster(cluster 인덱스 포함)에 한번만 저장되므로 저장공간이 절약

특징

지정된 column값의 순서대로 row를 저장시키는 방법 : Random access의 감소를 위해 정해진 column을 기준으로 동일한 값을 가진 하나 이상의 테이블의 row를 같은 장소에 저장
하나 혹은 그 이상의 테이블을 같은 cluster내 저장 가능
access 기법이 아니라 access 효율향상을 위한 물리적 저장기법
검색의 효율을 높여주나 입력, 수정, 삭제 시는 부하 증가
분포도가 넓을 수록 오히려 유리 : 분포도가 넓은 테이블의 clustering은 오히려 저장공간 절약
clustering 인덱스는 clustering column의 값마다 하나씩의 인덱스 row를 가짐
clustering의 효과는 clustering factor와 비례관계

cluster 구성 순서

cluster 생성 → cluster 테이블 생성 → cluster 인덱스 생성 → 테이블에 삽입(insert)수행

선정 기준

6 블록 이상의 테이블
다량의 범위를 자주 access 해야 하는 경우
인덱스를 사용한 처리가 부담이 되는 넓은 분포도
여러 개의 테이블이 빈번한 조인을 일으킬 때
반복 column이 정규화 작업에 의해 어쩔 수 없이 분할된 경우
UNION, DISTINCT, ORDER BY, GROUP BY 가 빈번한 column이면 고려해 볼 것
수정이 자주 발생하지 않는 column

고려사항

① 데이터 처리(입력, 수정, 삭제)에 오버헤드 발생주의
② 인덱스로도 충분한 범위는 clustering 효과가 없음
③ cluster key는 수정이 빈번하지 않을 것
④ 각종 access 형태에 대해 인덱스와 적절한 역할 분담
⑤ clustering은 기존의 인덱스의 수를 감소시킴(인덱스 재구성)
⑥ cluster parameter 의 적절한 지정
⑦ cluster key 별 row 수의 편차가 심하지 않을 것
⑧ cluster에 데이터 입력 시 row가 적은 테이블부터 실시할 것
⑨ clustering 된 테이블 조인 시 row 수의 역순으로 FROM 절에 기술할 것
⑩ cluster key를 첫 번째로 하는 인덱스는 생성하지 말 것

Cluster Parameter

① Size : cluster 의 크기 지정
② Storage : 저장공간의 효율적인 활용
③ PCTUSED & PCTFREE : chain 감소
④ PCTFREE : row의 길이 증가를 대비하기 위해 여유공간(Free Space) 지정

II. Single-clustering

처리범위가 넓어 문제가 발생되는 경우는 단일 테이블 clustering
한 block을 초과하는 경우가 대부분 : row chain여부와 상관없이 하나 이상의 chain block을 가짐

III. Multi-cluster

조인이 많아 문제가 발생되는 경우는 다중 테이블 clustering

실행계획


SELECT ...............
  FROM emp e,dept d
 WHERE e.deptno = d.deptno
   AND d.deptno like '11%';


SELECT STATEMENT
  NESTED LOOPS
    TABLE ACCESS (CLUSTER) OF 'DEPT'
      INDEX (RANGE SCAN) OF 'DEPT_CLUSTER_IDX' (CLUSTER)
    TABLE ACCESS (CLUSTER) OF 'EMP'

IV. Clustering table 비용

검색의 효율은 높여주나, 입력 - 수정 - 삭제 시 부하 발생

입력 시 부하

1) SORT된 대상은 입력부하 감소
2) 일반 인덱스에 저장은 분리형과 동일 : 일반 인덱스도 정해진 위치에 저장
3) Cluster Key Value에 따라 정해진 위치에 저장
4) 각 rows에 따라 저장 위치가 달라지므로 free list 요구 횟수 증가
5) 테이블에 저장되는 시간은 일반 테이블에 비해 더 소요되지만 인덱스의 개수가 감소되어 전체적으로 소요되는 시간은 큰 차이 없음

수정시의 부하

1) Cluster Key Value 변경 : 기존 rowid를 변경하지 못하므로 cluster chain이 발생
2) Cluster Key Value가 아닌 경우 : 일반 테이블과 동일
3) 새로운 INSERT가 발생하는 개념
4) 아주 빈번하지 않는다면 큰 부하는 없음
5) Clustering factor가 나빠지므로 필요할 경우 정기적인 재생성 수행
6) Row chaining
- 단일 테이블 상의 특정 Row의 길이가 증가해서 더 이상 동일한 데이터 block에 들어갈 수 없을 때 발생
- 이때 RDBMS는 또 다른 데이터 block을 찾고, 이 데이터 block은 원래 block과 연결되어 있음
- 이 경우 데이터 block이 하나의 I/O 작업과 동일한 양을 수행하기 위해 두 개의 I/O를 사용해야 함
- 데이터베이스 성능 저하의 원인

삭제 시의 부하

1) Delete

테이블의 row삭제 후 인덱스 row삭제 : 인덱스 개수가 적다면 일반 테이블보다 유리할 수 있음

2) Drop

① DDL이지만 Rollback Segment 사용\- cluster에는 하나 이상의 테이블이 있는데, 이들이 독립적인 테이블로 존재하는 것이 아니라 cluster 내의 row로 존재하므로, Drop은 row 일부를 제거하는 것과 유사하므로 Delete 개념이 됨
② 심각한 부하 발생 우려
③ clustering factor 악화
④ Drop 대신 재생성할 것
⑤ truncate : Cluster Index Data를 삭제하지 않음
⑥ 일반 인덱스에 비해 부하가 동일하거나 감소
⑦ 참고 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10006.htm#SQLRF01707

Row Migration

ⓐ Data Block상의 하나의 Row는 길이가 증가하면서 갱신되며, Block의 Free space가 0%일 때, Row Migration(이주) 발생
ⓑ 전체 Row가 들어갈 만한 크기의 새로운 Block에 Row에 대한 Data가 Migration 됨

Clustering 테이블에서 row chaining

ⓐ 전제 : General table column = clustering table row
ⓑ Clustering 테이블에서 row chaining은 column의 길이 증가를 의미 = row의 증가
ⓒ Cluster size 초과 no : chain block은 cluster key column에 따라 부여 받고, 같은 key value를 가진 rows은 같은 chain block에 저장되므로 일반 테이블보다는 적은 chain block을 가지므로 영향은 적음

V. Hash Clustered Table

특징

1) Cluster Index를 사용하지 않고 hash 함수를 사용하여 table access
2) "="로만 access
3) 코드를 관리하는 소형 테이블, 우편번호, 시스템 사용자 정보를 관리하는 테이블에 활용