퍼온곳 : http://blog.naver.com/idtong/130040408722
클러스터링 사이즈 결정
클러스터링 사이즈 = ∑(table 평균로우 길이 * 클러스터 키당 평균 로우수)*1.1
1) table 평균로우 길이 산정
. 테이블의 통계정보 생성
analyze table [table_name] compute statistics;
ex) analyze table TB_MEMBER compute statistics
. tavle 평균 길이 조회
ex) select avg_row_len from user_tables where table_name='TB_MEMBER';
2). 클러스터 키당 평균 로우수 구하기
select avg(count(*))
from TB_MEMBER group by reg_date;
여기서 reg_date는 회원가입일을 기준으로 클러스터링 컬럼을 임의로 설정 하였으나 실무에서는 클러스터 컬럼 선정에 신중을 기해야 한다.
3). 블럭내 평균 로우수를 산정한다.
select avg_row_len/(blocks-NVL(empty_blocks,0))
from user_tables
where table_name='TB_MEMBER'
4). 클러스터 및 클러스터 인덱스 생성
create cluster TB_MEMBER# (reg_date char(8))
pctfree 10 pctused 60
size 8600;
create index TB_MEMBER#x on cluster TB_MEMBER#
pctfree 5
storage (initial 20K next 10K);
5) 기존테이블의 명칭 변경
rename TB_MEMBER to TB_MEMBER_TMP;
6). 클러스터내에 테이블을 생성
(create table 시 마지막에 반드시 클러스터 지정)
create table TB_MEMBER
(
....
) cluster TB_MEMBER#(reg_date);
7). 생성된 테이블 내에 테이터를 저장시킨다
insert into TB_MEMBER
select * from TB_MEMBER_TMP
where reg_date >= '19000101'
8). 기존 테이블을 삭제 후
다른 인덱스를 추가 한다.
================================================================================================================
기타 좋은 글.... ::::::::::::::::::::: 꼭 하단 읽어본 후 실습 권장
================================================================================================================
Manage Cluster Segments
■ Chapter 개요
• 데이터 세그먼트는 테이블에 입력된 실제 데이터를 저장한다.
• 데이터 세그먼트 종류 :
• 인덱스 클러스터 :
• Index Cluster는 테이블의 컬럼 값에 따른 테이블 행 데이터를 저장 (contains rows from one or more tables stored together based on the value in a column of the tables.)
• 해쉬 클러스터 :
• Hash Cluster는 Hash Algorithm을 사용하여 테이블 데이터 행을 저장하는 기법이다.(Contains rows from a table stored by way of a hashing algorithm)
• hash cluster는 하나 이상의 테이블을 저장(매우 드문 경우)하기도 한다.
• Clusters define a storage structure, each cluster contains one or more table definitions
• Tables in cluster are accessed by a user as if they are stand alone tables
■ 인덱스 클러스터
■ 인덱스 클러스터 개요
• cluster는 table 데이터 저장을 위한 다른 방법(일반적인 데이터 저장과 비교하여)이다. (Cluster is the optional Method to store the table data)
• 클러스터는 같은 컬럼을 가지고 있는 테이블 또는 종종 같이 사용되는 테이블들을 하나의 그룹으로 묶은 오라클 DB Object이다.
• 클러스터를 사용 장점
• I/O 성능 향상 (increase I/O performance)
• 스토리지 오버헤드를 감소시켜 줌 (reduce storage overhead)
• 따라서 한 클러스터에 포함된 테이블들은 같은 데이터를 위해 하나의 블록을 사용하고 조회한다.
• 여기서 클러스터로 그룹된 테이블들(clustered tables)이 공유하는 컬럼이 클러스터 키(Cluster key)이고,
• 그 클러스터 키 컬럼(cluster key columns) 위에 만들어진 인덱스가 Cluster Index이다.
• 한 클러스터에 속해있는 테이블들의 특징
• 같은 종류의 컬럼을 가짐 (have columns in common)
• 종종 같이 사용됨 (often used together)
• 같은 데이터 블록에 저장됨 (stored in the same data blocks)
■ 클러스터 특징
• 클러스터 사용 장점
• 디스크 I/O를 줄여준다. 이유는 그룹된 테이블들 중에 관련된 컬럼 데이터 행들이 같은 데이터 Block에 저장되기 때문 (reduce disk I/O because related rows are stored together in the same data blocks)
• 클러스터된 테이블들 사이에 Join이 발생할 경우, 그 처리 시간이 단축된다. (improved access time for joins of clustered tables)
• 인덱싱에 필요한 스페이스를 절약할 수 있다. (Reduces the amount of space needed for indexes)
• 테이블 클러스터링 특징
• 한 클러스터 키에 포함될 수 있는 컬럼의 최대 수는 16개 (Max, number of columns in a cluster key = "16")
• 클러스터는 데이터 조회 성능을 향상시키지만 데이터 저장, 수정, 삭제 또는 한 테이블 전체 Scan의 성능을 감소시킨다. (clusters improve query performance, but reduce the performance of inserts, updates, deletes, & all table scans)
• 만약 다수개의 테이블이 클러스터로 그룹되어 있을 때, 한 테이블의 전체 Scanning을 수행하면 그 성능이 감소하며, 이는 하나의 테이블을 읽으려 할 때, 클러스터화 된 다른 테이블들도 모두 같이 읽기 때문이다. (if multiple tables are clustered, full table scans for a table become slower, because all records from all tables have to be scanned.)
• 클러스터 하기 좋은 테이블에는 .. (cluster table candidates)
• 주로 조회가 발생하고 수정이 거의 발생하지 않을 테이블 (primarily queried & not frequently updated)
• 컬럼 안에 많은 중복 데이터를 가지는 테이블 (contain redundant values within a column)
• 자주 Join되는 테이블 (often joined tables)
• Cluster key가 되기 좋은 columns은
• 데이터 값의 범위가 큰 컬럼 (contain a wide range of values)
• 테이블 간의 조인에 사용되는 컬럼 (used in joins on multiple tables)
• Cluster key 가 되기 나쁜 columns는
• 특정 데이터 값이 적은 컬럼 (few distinct values)
• 자주 데이터 수정이 발생하는 컬럼 (frequently updated)
• 그래서 cluster key 로는
• LONG 또는 LONG RAW column 은 안된다.
• index 하기 좋은 column이 cluster key 하기 좋은 column
• update 자주 안 일어나는 컬럼이어야 함
• 한 cluster key 에 너무 많은 데이터 행이 들어가면, 예를 들어 "성별"과 같은 컬럼에는 "남자" 또는 "여자"와 같이 두 개의 데이터만이 존재하게 되며, 이 경우, 클러스터 키를 사용할 이유가 없어진다.
• 한 cluster key 에 너무 적은 data가 들어가면 wasting space
■ 클러스터 인덱스
클러스터 키는 그 인덱스를 가지는데 이를 클러스터 인덱스라 하며, 이는 클러스터 키 값을 빠르게 조회하기 위해 사용된다. 따라서 클러스터에 데이터를 저장하기 전에 꼭 클러스터 인덱스를 생성해야만 한다.
• 클러스터 인덱스는 클러스터 키 값을 가진 데이터 블록을 직접 포인트하고 있기 때문에 최소 두 번의 디스크 I/O만으로 데이터를 조회할 수 있게 한다. (used to lookup cluster key values quickly by pointing directly to the data block containing the cluster key rule with allowing row to be accessed within minimum of two I/O)
• 클러스터는 클러스터 인덱스가 없는 데이터를 조회하지 못함. (Clustered data 가 access 되려면 cluster index가 있어야 함)
• 따라서 클러스터를 사용하려면 데이터를 저장이 이루어지기 전에 인덱스 하나당 한 개의 클러스터 인덱스를 생성해야 한다. (you must create index before inserting data only one entry per index key.)
• cluster index로 unique, LONG, 또는 LONG RAW인 컬럼을 사용할 수 없다.
■ 클러스터 관리
• 성능향상을 위해 클러스터와 인덱스를 각기 다른 디스크의 데이터 파일로 각각 다른 테이블스페이스에 저장.
⇒ 클러스터 데이터와 인덱스 데이터를 분리한다.
• 클러스터 Storage Parameters
• INITIAL, NEXT, MAXEXTENTS, MINEXTENTS, PCTINCREASE,
• SIZE,
• PCTUSED, PCTFREE, INITRANS, MAXTRANS
• 여기서
• PCTUSED &PCTFREE: 클러스터 자체에 적용되는 파라미터임(클러스터내의 각각의 테이블이 아니다)
• size : 클러스터 키의 최대 값 (Max. number of cluster keys)
• 클러스터 키와 그 데이터 행을 저장하기 위한 크기 지정 (Specifies the estimated number of bytes needed to store the average cluster key & its data rows)
• 오라클 서버는 하나의 cluster key와 그 데이터 행을 하나의 데이터 Block에 저장한다
• size가 너무 큰 경우 ⇒ space waste 발생
• size 가 너무 작은 경우 ⇒ 같은 cluster key안의 데이터들이 다른 클러스터 Block에 저장되어 그 성능이 저하된다.
■ 클러스터 생성
클러스터는 다수개의 테이블을 하나의 오라클 블록에 저장하는 메커니즘이며, 클러스터 인덱스를 꼭 필요로 한다. 따라서 테이블을 클러스터링(Clustering)하는 순서는 다음과 같다
1. 클러스터를 생성한다.
2. 클러스터 인덱스를 생성한다.
3. 클러스터 테이블들을 생성한다
1. 클러스터 생성
• 클러스터 생성 문법
CREATE CLUSTER cluster (column data_type, ... )
[PCTUSED int]
[PCTFREE int]
[INITRANS int]
[MAXTRANS int]
[SIZE int [K or M]]
[TABLESPACE tablespace]
[STORAGE storage_clause]
• 여기서 PCTUSED & PCTFREE : 오직 클러스터 그 자체에 적용되는 block 파라미터 (only applies to cluster, not to individual tables)
• SIZE: Cluster와 그 해당 데이터 행을 모두 저장하기 위해 필요한 스페이스를 나타낸다.
2. 클러스터 인덱스 생성
• 클러스터 생성 뒤에 ⇒ 클러스터 인덱스 만들기
Cluster 만든 뒤에 Table이 그 Cluster 안에서 생성된다. 하지만 Data를 클러스터화 된 데이터에 입력하기 전에 그 클러스터 인덱스를 생성해야 함. 넣기 전에 cluster index 만들어야 함
• 클러스터 인덱스 생성 문법
CREATE INDEX index
ON CLUSTER cluster
[INITRANS int]
[MAXTRANS int]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PCTFREE int]
[NOSORT]
• 클러스터 인덱스의 생성은 일반 인덱스 생성과 크게 다르지 않으며 CREATE INDEX 뒤에 ON CLUSTER 옵션을 붙여서 클러스터 인덱스 생성임을 명시한다.
• 클러스터 인덱스의 생성도 일반 인덱스 생성과 같이 PCTUSED를 사용할 수 없다.
3. 클러스터 테이블 생성
일반 테이블 생성방법과 같으며 뒤에 사용할 클러스터 명을 명시하여 생성한다. (아래 예제 참조)
■ 클러스터링 예제
다음은 클러스터와 그 클러스터 인덱스를 생성하고, 클러스터 테이블을 생성하는 예제로, 예를 들어 IDNUM 컬럼을 공유하는 두 테이블 T1과 T2가 있다고 가정하고, 이들을 클러스터링 하려고 한다면 다음과 같을 것이다.
// 클러스터 생성
SQL> CREATE CLUSTER cluster_T1_T2 (IDNUM NUMBER(3))
2> SIZE 400
3> TABLESPACE DATA1
4> STORAGE (INITIAL 30K);
Cluster created.
// 클러스터 인덱스 생성
SQL> CREATE INDEX ind_cluster_T1_T2
2> ON CLUSTER cluster_T1_T2
3> TABLESPACE IDX1;
Index created.
// 클러스터링할 테이블 생성
SQL> CREATE TABLE T1 (name VARCHAR2(10),
2> hire_date DATE,
3> IDNUM NUMBER(3))
4> CLUSTER cluster_T1_T2 (IDNUM);
Table created.
SQL> CREATE TABLE T2 (IDNUM NUMBER(3),
2> idname VARCHAR2(10))
3> CLUSTER cluster_T1_T2 (IDNUM);
Table created.
이렇게 생성된 테이블 T1과 T2의 데이터는 같은 데이터 블록에 저장되게 된다.
■ 클러스터 변경 및 삭제
• 클러스터 변경
CREATE CLUSTER cluster (column data_type, ... )
[PCTUSED int]
[PCTFREE int]
[INITRANS int]
[MAXTRANS int]
[SIZE int [K or M]]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[ALLOCATE EXTENT SIZE/DATAFILE]
• PCTUSED, PCTFREE, SIZE, MAXTRANS의 값을 변경하면 클러스터로 그룹되어진 모든 테이블에 적용된다.
• INITRANS 앞으로 생성될 cluster data block에만 적용됨
• INITIAL 과 MINEXTENTS의 값은 변경할 수 없다.
• 클러스터 삭제
• 클러스터의 삭제 전에 클러스터화된 테이블의 삭제가 먼저 이루어 지는 것이 좋다.
• DROP CLUSTER SQL문 문법
DROP CLUSTER cluster_name INCLUDING TABLE CASCADE CONSTRAINTS;
• 여기서
• INCLUDING TABLE : 클러스터로 그룹화된 테이블이 아직 남아 있는 상태에서 이 옵션없이 클러스터를 삭제하면 에러가 발생한다.
• CASCADE CONSTRAINTS : 클러스터로 그룹화된 테이블에 Referential Integrity 제약 조건이 지정되어 있을 때 이 옵션없이 클러스터 삭제 시 에러 발생
• 클러스터를 삭제하면 클러스터내의 모든 테이블과 클러스터 인덱스가 같이 삭제된다.
• 클러스터 삭제 시, 그 클러스터에 사용되었던 모든 Extents는 그 소속 테이블스페이스에 return 된다.
■ 인덱스 클러스터 사용 장단점
장점 단점
같은 cluster key를 가진 여러 데이터 행에 빠르게 access할 수 있다. insert 작업이 매우 느려진다.
select, updates, delete 작업이 더 빨라진다.
테이블 loading이 매우 느려진다.
인덱스 클러스터의 사용으로 점진적으로는 인덱스 스페이스 사용을 줄일 수 있다. index 없이 clustered table load 안됨.
클러스터 키가 클 경우, 인덱스 클러스터는 스페이스를 절약할 수 있다. (index cluster saves space if the cluster key is large.) 클러스터 인덱스가 수정되어야 롤백 데이터 및 리두 로그 데이터가 생성되어 지므로 그 만큼의 시스템 리소스 사용으로 성능 저하 (cluster index must be updated, so that the rollback data, redo data가 generated)
클러스터 데이터 블록은 SGA내에 상주할 수 없으므로 엑세스가 발생할 때마다 여러 번 읽혀져야 함.. 시스템 속도 저하(Blocks cannot stay in SGA & have to be read several time)
인덱스 클러스터의 사용을 위해 더 많은 스페이스 사용을 가져 옴 (index cluster need more space)
데이터 직접 로딩(direct loading)이 불가능하다.
■ Hashing & Hash Cluster
• 오라클에서 말하는 해싱(Hashing)이란 테이블의 컬럼으로 해쉬(Hash) 함수를 제공하고, 그 해쉬 함수의 결과에 따라 데이터 행을 저장하는 방법을 가리킴.
• 이러한 해싱은 데이터 블록을 찾는데(Retrieving) 필요한 디스크 I/0의 수를 감소시킨다.
• 따라서 해싱은 그 사용목적에서 보면 인덱스와 비슷하다.
■ Hashing Overview
• Hashing은 데이터 Retrieve 성능을 향상시키기 위해 사용.
• 테이블 컬럼에 Hash 함수를 적용한다. (apply a hash faction to the columns of a table)
• Hash 함수의 결과에 따라 데이터 행을 저장한다. (store rows according to the result of the hash function)
• 데이터 블록을 access하는 I/O를 줄여 준다. (reduce number of I/O needed to retrieve a data blocks)
• Hash 함수 사용 시점 (use hashing when)
• 테이블의 크기가 고정적일 때 (tables do not vary in size)
• Query 성능을 최적화하는 것이 필수적인 사항(Update 또는 Insert와 비교하여)일 때 (optimizing query performance가 필수일 때)
• 해쉬된 컬럼들에 대하여 항상 일정하게 같은 수의 조회가 이루어 질 때 (when queries are equality queries on hashed column.)
• 정리
• 해싱을 사용하는 경우:
• 테이블의 크기에 변화가 없는 경우
• 해쉬된 컬럼들에 대하여 항상 일정하게 같은 수의 조회가 이루어 질 때
• 데이터 조회 작업의 성능 최적화가 가장 중요한 요소일 때
• 해쉬 키(Hash Key)가 잘 분산(Distributed)되었을 때
• 인덱스를 사용하는 경우 :
• 테이블의 크기가 고정적이지 않은 경우
• 해쉬할 테이블의 크기를 예측할 수 없는 경우
• 저장 공간을 미리 할당할 수 없을 때
• 응용 프로그램이 테이블 전체의 스캔(Scan) 작업을 자주 수행할 때
▶ table을 hash 하려면 table create 전에 hash cluster가 있어야 한다.
• Hash Function
• 해쉬 함수는 Hash 값을 return하는 cluster key value에 적용되는 함수이다.
• 하나 또는 다수의 컬럼에 적용될 수 있다. (can be applied to one column or a composite key)
• 해쉬 값 사이에서 Maximum Distribution을 제공해야 함 (should provide max. distribution of rows among the hash values)
• Collision을 최소화한다. (attempts to minimize the number of undesired collisions)
• User-Defined로 사용할 수 있다.
• Collision은
두 개 이상의 클러스터 값이 동일한 Hash 값을 access하려 할 때 발생 (collision happens when TWO Cluster key values hash to the same hash value.)
• Hash Cluster 생성 :
• Hash Cluster 생성시 HASHKEYS와 SIZE 파라미터가 해쉬 클러스터 Block안의 데이터 Storage 사용에 영향을 미침.
• HASHKEYS의 최소값은 2
• HASHKEYS를 사용하여 Hash Cluster를 생성한다.
• 해쉬 클러스터 생성시 지정한 HASHKEYS 값은 소수(Prime number) 로 roundup되어 사용된다.
• HASH IS
• 해쉬 값을 주는 컬럼을 명시 (specify a column that gives the hash values)
• "HASH IS"는 해쉬 함수의 작업을 대신한다. (It replaces the results of the hash function)
※ hash key가 n개이면 collision의 가능성은 1/n 이다.
• 사용되는 Hash Key의 수
• SIZE 파라미터에 의해 Hash Key의 수가 결정됨.
• 여기서 SIZE는 해당 해쉬 키가 적용되는 데이터 행을 저장하는데 필요한 공간을 설정하는 파라미터이다. (average amount of space needed to hold all rows for a given hash key.)
• 해쉬 키 모순(Trade-Off)
• 해쉬 키의 크기를 작게 설정하면, 상대적으로 많은 해쉬 키가 각각의 데이터 블록에 위치할 수 있게 되지만, 만약 새로이 저장되는 데이터 행이, 위치할 데이터 블록의 크기보다 클 경우, 그 데이터를 저장하기 위한 오버플로(Overflow) 데이터 블록이 생성되어 더 많은 디스크 I/O를 초래하여 그 성능을 저하시킨다.
• 만약 해쉬 키를 크게 설정한다면, 상대적으로 적은 수의 해쉬 키가 각각의 데이터 블록에 위치할 것이며, 따라서 그만큼의 스토리지 스페이스가 더 필요해지게 된다.
■ Hash cluster Blocks
• average hash key size가 작으면
⇒ 각각의 데이터 블록에 더 많은 수의 해쉬 키가 지정될 수 있게 된다.(many hash keys(Data block) can be assigned to each data block)
• average hash key size가 크면
⇒ 적은 수의 해쉬 키가 데이터 블록에 지정됨
• Collision이 너무 자주 발생하면 Overflow Block이 생기게 되어 성능 저하 (frequent collisions cause overflow blocks to be created)
• 새로운 데이터 행이 현재의 데이터 Block내의 프리 스페이스보다 크면, Overflow Block이 생성되고, 따라서 그 데이터를 조회하는데 사용되는 I/O가 증가하여 성능이 감소하게 된다. (if new rows too large to fit into original data black, an overflow block is created and more I/O is needed to retrieve that data)
예제
만약 Block size가 2K이고, Block내에 사용 가능한 Data Space가 1800 byte일 때, 다음과 같이 Hash 클러스터를 생성하면,
SQL> create cluster emp_h_clu (job VARCHAR2 (9))
> tablespace user_data
> size 500
> HASH KEYS 40;
■ HASH IS
• "HASH IS" 옵션 파라미터는 해쉬 값과 함께 사용될 컬럼을 명시하는데 사용된다. (Specify a column to be used for the hash values with the HASH IS parameter.)
• HASH IS 특성
• "HASH IS" column은 그 cluster key 안에서 유일한 컬럼이어야 한다.
• Cluster key는 데이터 타입이 NUMBER(양의 정수만)인 하나의 컬럼이어야 한다. (containing non-negative)
• 사용자가 생성한 Hash 함수를 사용할 수 있다 (can contain a user-defined hash function.)
■ Hash Cluster 생성, 변경, 삭제
• Hash Cluster 생성 예제
다음은 해쉬 키의 크기가 55이고 해쉬 키의 수가 1000개이고 해쉬 함수로 emp_no를 사용하는 해쉬 클러스터를 생성하고,
해쉬 테이블 con을 생성하는 예제이다. 이때, emp 해쉬 테이블에서 해쉬 함수 emp_no로 사용되는 컬럼은 empno가 된다.
SQL> CREATE CLUSTER emp_raise (emp_no NUMBER(4))
2> TABLESPACE user_data
3> HASHKEYS 1000
4> HASH IS emp_no;
5> SIZE 55;
Cluster created.
SQL> CREATE TABLE emp (empno NUMBER(4),
2> empname VARCHAR2(10) )
3> CLUSTER emp_raise(empno);
Table created.
• Hash Cluster 변경
• PCTUSED, PCTFREE 장 INITRANS는 앞으로 생길 Block에만 적용됨
• SIZE, HASH KEYS, HASH IS는 변경 안됨
• 예제
SQL> ALTER CLUSTER emp_raise
2> PCTFREE 20 PCTUSED 50
3> STORAGE (NEXT 256K PCTINCREASE 0 MAXEXTENTS 50);
Cluster altered.
• Hash Cluster 삭제
• 문법
→ drop cluster ------------- cluster ---→
┗ schema┛
→ including tables --------------------→
└cascade constraint┘
• 예제
SQL> DROP CLUSTER sn_reserve
2> INCLUDING TABLES;
Cluster dropped.
• 인덱스 클러스터나 해쉬 클러스터가 삭제되면, 거기에 쓰였던 모든 Extents는 다시 원래의 테이블스페이스로 return된다.
■ Hash Cluster 사용 장단점
• 장점
• Hash clusters는 Single Record로의 빠른 access를 제공한다.
• Hash clusters는 같은 Cluster Key 값으로 다수의 데이터를 제공할 수 있다.
• Selecting, updating, & deleting 처리 속도 향상
• Single 데이터 입력 속도는 느리지 않다.
• 단점
• Full 테이블 Scan시에 모든 할당 block을 다 Scan해야 하기 때문에 속도가 느려진다.
• hash clusters는 Equality Search시에만 사용될 수 있다. (column=constant)
• Overflow의 위험이 있다.
• 일반 테이블보다 더 많은 공간이 필요하게 된다.
• 테이블 Loading이 느려짐
• Direct loading이 불가능하다.
• 데이터 블록이 SGA내에 상주하지 못하므로 여러 번 읽어야 한다.
출처 : [기타] http://blog.naver.com/blubeard/80030003231
[출처] oracle 클러스터(cluster) 사이즈 계산 및 생성하기|작성자 시골사람