인덱스 클러스터 테이블 (by jongmali) [2012.11.10]
인덱스 클러스터 테이블
- 동일 데이터베이스에서 공통의 컬럼을 사용하는 테이블 그룹을 함께 저장.
- 연관된 데이터를 동일 블록에 함께 저장하도록 하는 방식.
- 다수의 블록에 존재 할 수도 있다
- 동일 블록에 데이터를 모두 저장하지 못한다면, 블록이 추가되고 원래 블록과 연결. (IOT의 오버플로 블록과 동일한 방식)
- 클러스터는 한 데이터 블록에 다수 테이블의 데이터를 저장 할 수 있다.
- 미리 조인된 데이터를 저장.
- 어떤 컬럼에 의해 그룹화된 한 개 테이블에도 사용 가능.
- 정렬된 데이터를 저장하는 것은 아니다.
- http://wiki.gurubee.net/pages/viewpage.action?pageId=1507561
h3.클러스터 생성
create cluster emp_dept_cluster
(deptno number(2))
size 1024
/
* deptno number(2)
** 클러스터의 클러스터링 대상 컬럼은 deptno, 테이블에서의 컬럼은 deptno라고 정의될 필요는 없지만, 반드시 number(2) 컬럼이어야 함.
* size 1024
** 클러스터 키 값과 관련된 데이터는 1024 바이트 정도라고 정의. 오라클은 이것을 이용하여 각 블록에 저장할 수 있는 클러스터 키의 최대수를 계산.
** 블록 크기가 8KB라면 오라클은 한 블록에 7개의 클러스터키를 위치시킨다.
- size 옵션
- 한 블록에 저장할 수 있는 최대 클러스터 키의 수를 결정.
- 크기를 너무 크게 잡아서 각 블록에 단지 몇 개의 키만을 가지도록 한다면 필요한 공간보다 더 많은 공간을 사용
- 너무 작게 잡아서 과도한 데이터 체이닝이 발생한다면 클러스터의 장점에 반하는 결과 초래.
클러스터 인덱스 생성
- 클러스터에 데이터를 삽입하기 전에 클러스터 인덱스를 생성.
- 클러스터 인덱스는 클러스터 키 값을 가지며, 키 값을 가진 데이터 블록 주소를 반환.
create index emp_dept_cluster_idx
on cluster emp_dept_cluster
/
- 일반적인 인덱스의 모든 파라미터를 가질 수 있다.
테이블 생성
create table dept
( deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13)
)
cluster emp_dept_cluster(deptno)
/
create table emp
( empno number primary key,
ename varchar2(10),
job varchar2(9),
mgr number,
hiredate date,
sal number,
comm number,
deptno number(2) references dept(deptno)
)
cluster emp_dept_cluster(deptno)
/
- cluster 키워드를 사용하여 클러스터 자체의 클러스터 키에 대응하는 베이스 테이블의 컬럼이 무엇인지 정의.
- 클러스터는 세그먼트. 해당 클러스터에 속하는 테이블은 세그먼트 속성을 가질 수 없다.
- 클러스터 테스트
-- 클러스터 사이즈에 모든 데이터가 들어갈 경우
insert into dept
(deptno, dname, loc)
select deptno+r, dname, loc
from scott.dept,
(select level r from dual connect by level < 10);
insert into emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
select rownum, ename, job, mgr, hiredate, sal, comm, deptno+r
from scott.emp,
(select level r from dual connect by level < 10);
select min(count(*)), max(count(*)), avg(count(*))
from dept
group by dbms_rowid.rowid_block_number(rowid)
/
?
MIN(COUNT(*)) MAX(COUNT(*)) AVG(COUNT(*))
------------- ------------- -------------
1 7 6
1 row selected.
select *
from (select dept_blk,
emp_blk,
case
when dept_blk <> emp_blk then '*'
end flag,
deptno
from (select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
dept.deptno
from emp,
dept
where emp.deptno = dept.deptno ) )
where flag = '*'
order by deptno
/
no rows selected
-- 데이터가 클러스터 사이즈 보다 큰 경우
insert into dept
(deptno, dname, loc)
select deptno+r, dname, loc
from scott.dept,
(select level r from dual connect by level < 10);
insert into emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
select rownum, ename, job, mgr, hiredate, sal, comm, deptno+r
from scott.emp,
(select level r from dual connect by level < 10),
(select level r2 from dual connect by level < 8);
select min(count(*)), max(count(*)), avg(count(*))
from dept
group by dbms_rowid.rowid_block_number(rowid)
/
MIN(COUNT(*)) MAX(COUNT(*)) AVG(COUNT(*))
------------- ------------- -------------
1 7 6
1 row selected.
select *
from (select dept_blk,
emp_blk,
case
when dept_blk <> emp_blk then '*'
end flag,
deptno
from (select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
dept.deptno
from emp,
dept
where emp.deptno = dept.deptno ) )
where flag = '*'
order by deptno
/
DEPT_BLK EMP_BLK F DEPTNO
---------- ---------- - ----------
11307 11338 * 12
11307 11338 * 12
11307 11338 * 12
11307 11338 * 12
11307 11338 * 12
11309 11338 * 17
11310 11339 * 19
11310 11339 * 19
11310 11339 * 19
11310 11339 * 19
11310 11339 * 19
11310 11339 * 19
11307 11338 * 22
11307 11338 * 22
11307 11338 * 22
11307 11338 * 22
11307 11338 * 22
11307 11338 * 22
11307 11338 * 22
11309 11338 * 27
11310 11339 * 29
11310 11339 * 29
11310 11339 * 29
11310 11339 * 29
11310 11339 * 29
11310 11339 * 29
11310 11339 * 29
11310 11339 * 29
11307 11338 * 32
11307 11338 * 32
11307 11338 * 32
11307 11338 * 32
11307 11338 * 32
11307 11338 * 32
11307 11338 * 32
11307 11338 * 32
11309 11338 * 37
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
11310 11338 * 39
46 rows selected.
-- 클러스터 사이즈 변경
-- delete table 로는 클러스터 사이즈를 변경해도 기존 블럭에는 영향을 미치지 않는다.
alter table emp modify constraints SYS_C0014799 disable;
truncate cluster emp_dept_cluster;
alter table emp modify constraints SYS_C0014799 enable;
alter cluster emp_dept_cluster
size 1200
/
insert into dept
(deptno, dname, loc)
select deptno+r, dname, loc
from scott.dept,
(select level r from dual connect by level < 10);
insert into emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
select rownum, ename, job, mgr, hiredate, sal, comm, deptno+r
from scott.emp,
(select level r from dual connect by level < 10),
(select level r2 from dual connect by level < 8);
select min(count(*)), max(count(*)), avg(count(*))
from dept
group by dbms_rowid.rowid_block_number(rowid)
/
MIN(COUNT(*)) MAX(COUNT(*)) AVG(COUNT(*))
------------- ------------- -------------
6 6 6
1 row selected.
select *
from (select dept_blk,
emp_blk,
case
when dept_blk <> emp_blk then '*'
end flag,
deptno
from (select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
dept.deptno
from emp,
dept
where emp.deptno = dept.deptno ) )
where flag = '*'
order by deptno
/
no rows selected
rowid는 한 테이블 내에서만 유일한 pseudo 컬럼이다.
select rowid from emp
intersect
select rowid from dept;
......
36 rows selected.
- 오라클의 많은 데이터 딕셔너리는 다양한 클러스터에 저장된다.
break on cluster_name
select cluster_name, table_name
from dba_tables
where cluster_name is not null
order by 1;
CLUSTER_NAME TABLE_NAME
------------------------------ ---------------------
C_COBJ# CDEF$
CCOL$
C_FILE#_BLOCK# UET$
SEG$
C_MLOG# SLOG$
MLOG$
C_OBJ# REFCON$
NTAB$
SUBCOLTYPE$
COLTYPE$
LOB$
TAB$
CLU$
COL$
IND$
ICOL$
OPQTYPE$
ICOLDEP$
VIEWTRCOL$
LIBRARY$
ASSEMBLY$
TYPE_MISC$
ATTRCOL$
C_OBJ#_INTCOL# HISTGRM$
C_RG# RGCHILD$
RGROUP$
C_TOID_VERSION# METHOD$
ATTRIBUTE$
PARAMETER$
COLLECTION$
TYPE$
RESULT$
C_TS# TS$
FET$
C_USER# USER$
TSQ$
EMP_DEPT_CLUSTER DEPT
EMP
SMON_SCN_TO_TIME_AUX SMON_SCN_TIME
39 rows selected.
- 클러스터를 사용하지 않아야 하는 경우
- 클러스터 테이블에 대량의 수정이 발생할 수 있는 경우
- 클러스터 테이블에 대하여 전체 스캔을 수행 할 때
- 테이블을 파티션해야 하는 경우
- 빈번하게 truncate와 데이터 적재를 수행해야 하는 경우
- 대부분이 데이터 읽기를 수행하고, 클러스터 키 인덱스 또는 클러스터 테이블에 생성된 다른 인덱스를
경유해서 읽기를 수행하고, 함께 조인된 결과정보를 자주 읽는다면 클러스터가 적합한 방법이 될 수 있다. - 항상 함께 조인되거나 관련된 데이터의 집합을 얻기 위한 읽기 중심의 수행에 적합.