IOT와 클러스터 테이블

IOT, 인덱스 클러스터, 해시 클러스터의 차이, IOT 생성과 조회 대상이 다르며 공간과 DML 실측값은 확인 불가

IOTORGANIZATION INDEX인덱스 클러스터해시 클러스터CREATE CLUSTERHASHKEYSSIZE

#핵심 요약

IOT(Index-Organized Table)는 테이블 데이터를 PK 인덱스 구조에 함께 저장해, PK로 찾을 때 테이블 방문 없이 인덱스만 읽음
실습에서 empno = 7788 조회가 7버퍼에서 1버퍼로 줄었음

클러스터 테이블은 클러스터 키 값이 같은 행을 같은 블록에 모아 저장함
실습에서는 job을 클러스터 키로 한 emp 조회가 4블록에서 3블록으로 줄었다고 기록했음

해시 클러스터는 키 값의 해시로 저장 위치를 정해 인덱스 없이 바로 찾아감
실습에서 dept 조회가 7버퍼에서 1버퍼로, emp와 dept를 함께 저장한 조인이 8에서 2로 줄었음

#개념과 동작 원리

실습의 인덱스 클러스터 크기 계산: 클러스터 SIZE는 키 하나에 들어갈 바이트 수로, 평균 행 길이 × 키당 평균 행 수 × 1.1로 잡았음
예: emp의 AVG_ROW_LEN 38 × job별 평균 2.8행 × 1.1 ≈ 117
1.1 배수는 실습에서 여유 공간을 추정할 때 사용한 값임

실습의 HASHKEYS 설명: 키 종류보다 넉넉하게 잡아 해시 충돌을 줄이고 확장에 대비함
실습에서는 Oracle이 HASHKEYS 값을 그보다 크거나 같은 가장 가까운 소수로 올려 잡는다고 설명했음(4 → 5, 10 → 11)
HASHKEYS는 지정값 이상인 가장 가까운 소수로 올림하며 최소값은 2임
실습에서는 해시 클러스터의 공간을 나중에 늘리려면 데이터를 옮겨 다시 만들어야 한다고 적었음

#주요 용어와 구성 요소

구문	의미
IOT 기본 키	테이블 행 자체를 기본 키 B-tree 리프에 저장하는 기준임
논리 ROWID	IOT 보조 인덱스가 기본 키를 통해 행을 찾는 식별자임 힙 테이블의 고정 물리 위치와 다름
오버플로	IOT 행의 일부를 별도 세그먼트에 두는 구성임 오버플로 컬럼 접근에는 추가 읽기가 필요할 수 있음
클러스터 키	함께 저장할 행을 묶는 기준임 테이블의 PK와 동일할 필요 없음

#필수 명령어 또는 SQL

항목	실행 조건
작업 대상	IOT, 인덱스 클러스터, 해시 클러스터의 저장 구조와 조회 경로
필요 권한	CREATE TABLE, CREATE CLUSTER 권한과 공간 할당, 소유 객체 인덱스 생성 및 대상 SELECT, INSERT 권한
바꿀 입력값	EMP 등 실습 객체의 존재, 컬럼형 확인 필요 클러스터와 인덱스 이름은 독립된 실습 이름으로 정함
결과 해석	IOT_TYPE은 조회한 테이블의 구조를 나타냄 EMP_IOT 생성과 DEPT_IOT 조회는 서로 다른 대상을 가리키므로 한 실행 결과로 묶지 않음

#IOT

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE TABLE emp_iot
( empno    NUMBER(4) PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2) )
ORGANIZATION INDEX;

INSERT INTO emp_iot SELECT * FROM emp;
COMMIT;

SQL*Plus (실습 계정)조회만 함

SELECT * FROM emp     WHERE empno = 7788;   -- 실습 7버퍼
SELECT * FROM emp_iot WHERE empno = 7788;   -- 실습 1버퍼

SELECT table_name, iot_type
  FROM user_tables
 WHERE table_name IN ('DEPT', 'DEPT_IOT');

결과 해석: IOT_TYPE이 IOT로 나오면 인덱스 구조 테이블임(실습은 dept_iot로 확인, 결과값 확인 불가)
퀴즈는 조회에 필요한 모든 컬럼을 담은 인덱스(커버링 인덱스)의 이름을 물었는데 답을 "index organized table"로 적었음
커버링 인덱스와 IOT는 다른 개념임

#인덱스 클러스터

SQL*Plus (실습 계정)조회만 함

SELECT avg_row_len FROM user_tables WHERE table_name = 'EMP';   -- 실습 38
SELECT AVG(COUNT(*)) FROM emp GROUP BY job;                     -- 실습 2.8

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE CLUSTER empclujob
( jo VARCHAR2(9) )
PCTFREE 10
PCTUSED 60
SIZE 117;

CREATE INDEX empclujob_idx ON CLUSTER empclujob;

CREATE TABLE emp_cluster
( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4),
  hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) )
CLUSTER empclujob (job);

INSERT INTO emp_cluster SELECT * FROM emp;

SQL*Plus (실습 계정)조회만 함

SELECT * FROM emp         WHERE job = 'SALESMAN';   -- 실습 4블록 (emp_job 인덱스 사용)
SELECT * FROM emp_cluster WHERE job = 'SALESMAN';   -- 실습 3블록

결과 해석:

인덱스 클러스터는 클러스터 인덱스가 만들어지기 전에는 소속 테이블에 DML을 수행할 수 없음
실습에서는 생성 직후 클러스터 인덱스를 만들었음

#해시 클러스터

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE CLUSTER c_dept_hash ( deptno NUMBER(10) ) HASHKEYS 4;

CREATE TABLE dept_hash
( deptno NUMBER(10), dname VARCHAR2(14), loc VARCHAR2(13) )
CLUSTER c_dept_hash (deptno);

INSERT INTO dept_hash SELECT * FROM dept;
COMMIT;

SQL*Plus (실습 계정)조회만 함

SELECT * FROM dept      WHERE deptno = 20;   -- 실습 7버퍼
SELECT * FROM dept_hash WHERE deptno = 20;   -- 실습 1버퍼

결과 해석: 개별 실행 출력은 기재 없음

#두 테이블을 한 해시 클러스터에

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE CLUSTER c_emp_dept_hash ( deptno NUMBER(10) ) SIZE 1024 HASHKEYS 10;

CREATE TABLE dept_cluster CLUSTER c_emp_dept_hash (deptno)
AS SELECT * FROM dept WHERE 1 = 2;

ALTER TABLE emp MODIFY deptno NUMBER(10);

CREATE TABLE emp_cluster CLUSTER c_emp_dept_hash (deptno)
AS SELECT * FROM emp WHERE 1 = 2;

INSERT INTO emp_cluster  SELECT * FROM emp;
INSERT INTO dept_cluster SELECT * FROM dept;
COMMIT;

SQL*Plus (실습 계정)조회만 함

SELECT e.ename, e.sal, e.job, d.loc
  FROM emp e, dept d
 WHERE e.deptno = d.deptno AND e.deptno = 20;                 -- 실습 8

SELECT e.ename, e.sal, e.job, d.loc
  FROM emp_cluster e, dept_cluster d
 WHERE e.deptno = d.deptno AND e.deptno = 20;                 -- 실습 2

결과 해석:

이 순서에서는 앞의 인덱스 클러스터 실습에서 만든 emp_cluster를 먼저 지워야 함(실습은 DROP TABLE emp_cluster;를 실행했음)

#구문과 옵션 설명

SIZE는 같은 클러스터 키 또는 해시 키와 연결된 데이터의 예상 바이트 크기임
HASHKEYS를 지정하면 초기 공간 할당은 SIZE × HASHKEYS에 기초함
SIZE와 키 개수를 과하게 잡으면 공간 낭비가 생기고 부족하면 추가 블록 접근으로 이점이 줄 수 있음

CREATE CLUSTER에는 자기 스키마의 CREATE CLUSTER 권한과 해당 테이블스페이스 할당량이 필요함
클러스터 생성만으로 인덱스가 자동 생성되지는 않음

#사용 조건과 주의사항

클러스터 테이블과 IOT의 DML 비용, 저장 공간에 대한 실측값은 기재 없음

IOT는 기본 키 접근에 유리하지만 넓은 행의 오버플로와 보조 인덱스를 통한 기본 키 재탐색 비용을 함께 고려함
클러스터는 함께 조회되는 키의 행을 모으는 구조임
개별 테이블 전체 스캔이나 키 변경이 많은 작업에서는 불리할 수 있음

AVG_ROW_LEN × 평균 행 수 × 1.1은 실습의 용량 추정식임
필요 공간과 버퍼 수는 환경에 따라 달라짐