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

1. 장점

1) 데이터는 정렬되지 않지만 같은 키를 가진 데이타는 같은 물리적 위치에 저장된다. 모든 데이타를 수용할 수 있을 정도로 블럭이 크다면 같은 블럭에 저장된다.
2) 서로 다른 테이블의 데이타일지다로 같은 불럭에 저장될 수 있다. DEPT 테이블과 EMP 테이블에서 DEPTNO=10인 행은 같은 곳에 저장됨으로써 미리 조인한 효과를 볼 수 있다.

2. 단점

1) 클러스터에는 직접 경로 적재를 수행할 수 없다.(?)
2) 클러스터 테이블을 분할할 수 없다.

1. 클러스터 생성하기

1. B*트리 클러스터

B*트리 인덱스를 사용하여 키 값과 데이터가 저장된 블록의 주소를 저장한다.
오라클은 클러스터 인덱스에서 키 값을 찾고 데이터가 있는 데이터베이스 블록 주소를 찾아서 그곳에서 가져온다.

2. 해시 클러스터

해싱 알고리즘을 이용하여 키 값을 데이터베이스 블록 주소로 바꿈으로써 블록 읽기 자체를 제외하고 모든 I/O가 생략된다.
해시 클러스터에서는 데이터 자체가 인덱스로 조회를 수행하는데 논리적인 I/O는 최적의 상태에서 한번만 수행된다.

1-1 B* 트리 클러스터 생성하기



1) 클러스터 생성

CREATE CLUSTER user_objects_cluster_btree
(username varchar2(30))
SIZE 1024

-생성될 클러스터 이름
-클러시터 키 정의
-SIZE : 클러스터 키의 크기를 1024 BYTE로 정의했다. 만일 블럭크기가 8KB라면 최대 7개의 클러스터 키가 들어갈 것이다. 
이 말은 하나의 블럭에 7가지 종류의 username 값을 가진 로우들이 한 장소에 저장되며 8번째 username은 
새로운 블럭이 저장되어야 함을 의미한다.

2) 클러스터 인덱스 생성

CREATE INDEX user_objects_idx
ON user_objects_cluster_btree

-클러스터에 데이터를 저장하기 위해서는 반드시 클러스터 인덱스가 생성되어야 한다.
-클러스터 인덱스는 클러스터키와 이 키 값의 데이터를 포함하고 있는 첫번재 블럭을 가리키는 포인터(클러스터ID)에 
대한 엔트리를 포함하는 B* 인덱스를 생성한다.
-클러스터ID로 인해 찾고자 하는 로우가 어느 블럭이 위치하는지 알게된다.

1-2 해쉬 클러스터 생성하기



CREATE CLUSTER user_objects_cluster_hash
(username varchar2(30))
HASKKEYS 100
SIZE 3168

-HASKKEYS 는 고유키 값의 갯수를 지정한다. 그렇다고 username이 100개로 제한되는 것은 아니며 오라클이 설정하는 
해쉬 테이블의 크기가 제한될 뿐이다.
-SIZE : SIZE가 1500이고 블럭크기가 4KB라면 블럭별로 두개의 키를 저장할 수 있다. 따라서 1000개의 해쉬 키를 사용한다면 
총 500개의 블럭이 할당될 것이다.
-해쉬 클러스터에서는 데이터 자체가 인덱스이므로 인덱스 생성과정은 없다.

2. 클러스터 사용하기

2-1 데이터 적재방법 제어하기



1) 클러스터 생성

CREATE CLUSTER SCOTT_CLUSTER_BTREE
(USERNAME VARCHAR2(30))
SIZE 1024

2) 클러스터 인덱스 생성

CREATE INDEX USER_OBJECTS_IDX
ON CLUSTER SCOTT_CLUSTER_BTREE

3) 클러스터에 USER_INFO 테이블 생성

CREATE TABLE USER_INFO(
USERNAME VARCHAR2(30)
,USER_ID NUMBER
,ACCOUNT_STATUS VARCHAR2(32)
,LOCK_DATE DATE
,EXPIRY_DATE DATE
,DEFAULT_TABLESPACE VARCHAR2(30)
,TEMPORARY_TABLESPACE VARCHAR2(30)
,CREATED DATE
,PROFILE VARCHAR2(30)
)
CLUSTER SCOTT_CLUSTER_BTREE(USERNAME)

4) 클러스터에 USERS_OBJECTS 테이블 생성

CREATE TABLE USERS_OBJECTS (
OWNER VARCHAR2(30)
,OBJECT_NAME VARCHAR2(128)
,OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(18)
,CREATED DATE
,LAST_DDL_TIME DATE
,TIMESTAMP VARCHAR2(19)
,STATUS VARCHAR2(7)
)
CLUSTER SCOTT_CLUSTER_BTREE(OWNER)

5) 두 테이블에 데이터 입력

-동일한 클러스터 키값을 가진 데이터들을 최대한 같은 블럭에 저장하기 위한 입력

INSERT 
WHEN (R=1) THEN /*USER_INFO 테이블에는 OWNER당 한번씩만 입력*/
INTO SCOTT.USER_INFO
(
	USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE
	,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,PROFILE
)
VALUES
(
	USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE
	,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,PROFILE
)
WHEN (1=1) THEN /*USERS_OBJECTS 에는 몽땅 입력*/
INTO SCOTT.USERS_OBJECTS
(
	OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME
	,TIMESTAMP,STATUS
)
VALUES
(
	OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,BCREATED,LAST_DDL_TIME
	,TIMESTAMP,STATUS
)
SELECT 
	A.USERNAME,A.USER_ID,A.ACCOUNT_STATUS,A.LOCK_DATE,A.EXPIRY_DATE
	,A.DEFAULT_TABLESPACE,A.TEMPORARY_TABLESPACE,A.CREATED,A.PROFILE
	,B.OWNER,B.OBJECT_NAME,B.OBJECT_ID,B.OBJECT_TYPE,B.CREATED BCREATED
	,B.LAST_DDL_TIME,B.TIMESTAMP,B.STATUS
	,ROW_NUMBER() OVER(PARTITION BY OWNER ORDER BY OBJECT_ID) R
FROM DBA_USERS A,DBA_OBJECTS B
WHERE A.USERNAME=B.OWNER
	AND A.USERNAME<>'SYS'

-무작위 입력

INSERT INTO SCOTT.USER_INFO
(
	USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE
	,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,PROFILE
)
SELECT 
	A.USERNAME,A.USER_ID,A.ACCOUNT_STATUS,A.LOCK_DATE,A.EXPIRY_DATE
	,A.DEFAULT_TABLESPACE,A.TEMPORARY_TABLESPACE,A.CREATED,A.PROFILE
FROM DBA_USERS A
WHERE USERNAME<>'SYS'

INSERT INTO SCOTT.USERS_OBJECTS
(
	OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME
	,TIMESTAMP,STATUS
)
SELECT 
	B.OWNER,B.OBJECT_NAME,B.OBJECT_ID,B.OBJECT_TYPE,B.CREATED BCREATED,B.LAST_DDL_TIME
	,B.TIMESTAMP,B.STATUS
FROM DBA_OBJECTS B
WHERE B.OWNER<>'SYS'
ORDER BY OBJECT_TYPE,OBJECT_NAME


6) 통계생성

--클러스터
ANALYZE CLUSTER SCOTT_CLUSTER_BTREE COMPUTE STATISTICS

--USER_INFO 
ANALYZE TABLE USER_INFO COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS

--USERS_OBJECTS 
ANALYZE TABLE USERS_OBJECTS COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS

7) 결과조회

SELECT
	OWNER                       /*소유자*/
	,ARFNO||'.'||ABLOCK AROWID  /*USER_INFO : 데이터가 저장된 DATAFILE번호.BLCOK번호*/
	,BRFNO||'.'||BBLOCK BROWID  /*USERS_OBJECTS : 데이터가 저장된 DATAFILE번호.BLCOK번호*/
	,COUNT(*)                   /*저장된 로우 수*/
FROM(
	SELECT 
		B.OWNER
		,DBMS_ROWID.ROWID_RELATIVE_FNO(A.ROWID) ARFNO
		,DBMS_ROWID.ROWID_BLOCK_NUMBER(A.ROWID) ABLOCK
		,DBMS_ROWID.ROWID_RELATIVE_FNO(B.ROWID) BRFNO
		,DBMS_ROWID.ROWID_BLOCK_NUMBER(B.ROWID) BBLOCK
	FROM USER_INFO A,USERS_OBJECTS B
	WHERE A.USERNAME=B.OWNER
)
GROUP BY OWNER,ARFNO,ABLOCK,BRFNO,BBLOCK
ORDER BY OWNER,ARFNO,ABLOCK,BBLOCK

8) 결과비교

* 제어입력

SCOTT	1.52803	1.52803	47
SCOTT	1.52803	1.52804	31

-USER_INFO 로우는 1개가 데이터파일 1번,블럭 52803에 입력되어 있다.
-USERS_OBJECTS 로우는 총 77개가 데이터파일 1번, 블럭 52803(47개 로우),52804(31개 로우)에 입력되어 있다.

* 무작위입력

SCOTT	1.52804	1.52795	1
SCOTT	1.52804	1.52800	6
SCOTT	1.52804	1.52804	15
SCOTT	1.52804	1.53047	2
SCOTT	1.52804	1.53048	15
SCOTT	1.52804	1.53051	33
SCOTT	1.52804	1.53055	2
SCOTT	1.52804	1.53057	3

-USER_INFO 로우는 1개가 데이터파일 1번,블럭 52804에 입력되어 있다.
-USERS_OBJECTS 로우는 총 77개가 데이터파일 1번에 있지만 총 8개의 블럭에 흩어져 있슴을 보이고 있다.

9) 결론

클러스터에 테이블을 만들었다고 하여 같은 클러스터 키를 가진 데이터들이 완벽하게 같은 블럭에 저장되는 것은 아니다.
데이터가 적재되는 방법을 제어할 수 있는 환경에서 클러스터의 효용성이 극대화되는 것이다.
따라서 항상 데이터가 클러스터 키와 무관하게 무작위로 입력되는 경우는 B*트리 클러스터에 적합하지 않다.

2-2 B*트리-I/O를 줄이고 버퍼 캐시의 효율성 높이기

클러스터 테이블은 힙 테이블에 비해 물리적,논리적 I/O가 현격히 줄어든다.



1) 힙테이블 만들기

CREATE TABLE USER_INFO_HEAP(
USERNAME VARCHAR2(30)
,USER_ID NUMBER
,ACCOUNT_STATUS VARCHAR2(32)
,LOCK_DATE DATE
,EXPIRY_DATE DATE
,DEFAULT_TABLESPACE VARCHAR2(30)
,TEMPORARY_TABLESPACE VARCHAR2(30)
,CREATED DATE
,PROFILE VARCHAR2(30)
)

CREATE TABLE USERS_OBJECTS_HEAP (
OWNER VARCHAR2(30)
,OBJECT_NAME VARCHAR2(128)
,OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(18)
,CREATED DATE
,LAST_DDL_TIME DATE
,TIMESTAMP VARCHAR2(19)
,STATUS VARCHAR2(7)
)

2) 인데스 생성

CREATE INDEX USER_INFO_HEAP_IDX ON USER_INFO_HEAP(USERNAME)
CREATE INDEX USERS_OBJECTS_HEAP_IDX ON USERS_OBJECTS_HEAP(OWNER)

3) 데이터 입력

INSERT INTO SCOTT.USER_INFO_HEAP
(
	USERNAME
	,USER_ID
	,ACCOUNT_STATUS
	,LOCK_DATE
	,EXPIRY_DATE
	,DEFAULT_TABLESPACE
	,TEMPORARY_TABLESPACE
	,CREATED
	,PROFILE
)
SELECT 
	A.USERNAME
	,A.USER_ID
	,A.ACCOUNT_STATUS
	,A.LOCK_DATE
	,A.EXPIRY_DATE
	,A.DEFAULT_TABLESPACE
	,A.TEMPORARY_TABLESPACE
	,A.CREATED
	,A.PROFILE
FROM DBA_USERS A
WHERE USERNAME<>'SYS'

INSERT INTO SCOTT.USERS_OBJECTS_HEAP
(
	OWNER
	,OBJECT_NAME
	,OBJECT_ID
	,OBJECT_TYPE
	,CREATED
	,LAST_DDL_TIME
	,TIMESTAMP
	,STATUS
)
SELECT 
	B.OWNER
	,B.OBJECT_NAME
	,B.OBJECT_ID
	,B.OBJECT_TYPE
	,B.CREATED BCREATED
	,B.LAST_DDL_TIME
	,B.TIMESTAMP
	,B.STATUS
FROM DBA_OBJECTS B
WHERE B.OWNER<>'SYS'
ORDER BY OBJECT_TYPE,OBJECT_NAME

4) 통계정보 생성

ANALYZE TABLE USER_INFO_HEAP COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS

ANALYZE TABLE USERS_OBJECTS_HEAP COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS

5) 힙 테이블과 클러스터 테이블 TRACE 분석

* 클러스터 테이블

BEGIN
FOR X IN (SELECT USERNAME FROM ALL_USERS)
LOOP
	FOR I IN 1..10
	LOOP
		FOR Y IN (
			SELECT /*CLUSTER*/
				   A.USERNAME,A.TEMPORARY_TABLESPACE
				   ,B.OBJECT_NAME,B.OBJECT_TYPE
			FROM USER_INFO A,USERS_OBJECTS B
			WHERE A.USERNAME=B.OWNER
			AND A.USERNAME='SCOTT'
		)
		LOOP
			NULL;
		END LOOP;
	END LOOP;
END LOOP;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    310      0.00       0.00          0          0          0           0
Fetch    24180      0.21       0.19          0       6510          0       23870
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    24491      0.21       0.20          0       6510          0       23870

Rows     Row Source Operation
-------  ---------------------------------------------------
  23870  MERGE JOIN CARTESIAN 
    310   TABLE ACCESS CLUSTER USER_INFO 
    310    INDEX UNIQUE SCAN USER_OBJECTS_IDX (object id 30648)
  23870   BUFFER SORT 
  23870    TABLE ACCESS CLUSTER USERS_OBJECTS 
    310     INDEX UNIQUE SCAN USER_OBJECTS_IDX (object id 30648)

* 힙 테이블

BEGIN
FOR X IN (SELECT USERNAME FROM ALL_USERS)
LOOP
	FOR I IN 1..10
	LOOP
		FOR Y IN (
			SELECT /*HEAP*/
				   A.USERNAME,A.TEMPORARY_TABLESPACE
				   ,B.OBJECT_NAME,B.OBJECT_TYPE
			FROM USER_INFO_HEAP A,USERS_OBJECTS_HEAP B
			WHERE A.USERNAME=B.OWNER
			AND A.USERNAME='SCOTT'
		)
		LOOP
			NULL;
		END LOOP;
	END LOOP;
END LOOP;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    310      0.01       0.00          0          0          0           0
Fetch    25420      0.29       0.23          0       7440          0       25110
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25731      0.31       0.24          0       7440          0       25110

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   MERGE JOIN (CARTESIAN)
      0    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'USER_INFO_HEAP'
      0     INDEX (RANGE SCAN) OF 'USER_INFO_HEAP_IDX' (NON-UNIQUE)
      0    BUFFER (SORT)
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'USERS_OBJECTS_HEAP'
      0      INDEX (RANGE SCAN) OF 'USERS_OBJECTS_HEAP_IDX' (NON-UNIQUE)

2-3 해쉬클러스터-인덱스 블럭 없애기



SELECT /*HASH*/
    A.USERNAME,A.TEMPORARY_TABLESPACE
    ,B.OBJECT_NAME,B.OBJECT_TYPE
FROM USER_INFO A,USERS_OBJECTS B
WHERE A.USERNAME=B.OWNER
AND A.USERNAME='SCOTT'

MERGE JOIN CARESIAN
   TABLE ACCESS HASH USERS_OBJECTS
BUFFER SORT
   TABLE ACCESS HASH USER_INFO

* 해쉬클러스터에서는 데이터가 사실상 인덱스이므로 데이터를 찾기 위해 인덱스 스캔을 하지 않는다.
* B*트리 클러스터 인덱스가 유사한 데이터를 함께 저장함으로써 버퍼 캐시 이용률을 제고할 수 있다면 
해쉬 클러스터는 인덱스 블럭을 모두 없앰으로써 관련 데이터의 양이 줄어들기 때문에 보다 많은 데이터를 버퍼에 넣을 수 있다.

2-4 단일테이블 해쉬-읽기 전용 테이블에 유용


1.단일테이블 해시클러스터 생성

CREATE CLUSTER OBJECT_ID_LOOKUP
(OBJECT_ID NUMBER)
SINGLE TABLE
HASHKEYS 50000
SIZE 100


2.해시클러스터 테이블 생성

CREATE TABLE SCOTT.SINGLE_TABLE_HASH_CLUSTER(
 OWNER,OBJET_NAME,OBJECT_ID,OBJECT_TYPE,CREATED
 ,LAST_DDL_TIME,TIMESTAMP,STATUS 
)
CLUSTER OBJECT_ID_LOOKUP(OBJECT_ID)
AS
SELECT 
 OWNER,OBJET_NAME,OBJECT_ID,OBJECT_TYPE,CREATED
 ,LAST_DDL_TIME,TIMESTAMP,STATUS 
FROM(
 SELECT * FROM DBA_OBJECTS
 UNION ALL
 SELECT * FROM DBA_OBJECTS
)
WHERE ROWNUM<=50000

3.힙 테이블 생성

CREATE TABLE SCOTT.HEAP_TABLE(
 OWNER,OBJET_NAME,OBJECT_ID,OBJECT_TYPE,CREATED
 ,LAST_DDL_TIME,TIMESTAMP,STATUS 
)
AS
SELECT 
 OWNER,OBJET_NAME,OBJECT_ID,OBJECT_TYPE,CREATED
 ,LAST_DDL_TIME,TIMESTAMP,STATUS 
FROM(
 SELECT * FROM DBA_OBJECTS
 UNION ALL
 SELECT * FROM DBA_OBJECTS
)
WHERE ROWNUM<=50000

4.쿼리실행

DECLARE
L_REC SINGLE_TABLE_HASH_CLUSTER%ROWTYPE;
BEGIN
  FOR ITERS IN 1..3
  LOOP
    FOR I IN 1..50000
   LOOP
    SELECT * INTO L_REC
    FROM SINGLE_TABLE_HASH_CLUSTER
    WHERE OBJECT_ID=I;        
    SELECT * INTO L_REC
    FROM HEAP_TABLE
    WHERE OBJECT_ID=I;
   END LOOP;
  END LOOP;
END;
/

5.문제점

1)아래 쿼리로 인해 같은 OBJECT_ID로 두개씩 로우가 입력되는데 SELECT시 
<ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다>
라는 에러 발생
SELECT * FROM DBA_OBJECTS
UNION ALL
SELECT * FROM DBA_OBJECTS

2)PL/SQL 안쪽 루프문을 보면 1~50000 까지 순차적으로 OBJECT_ID를 찾고 있다.
그런데 DBA_OBJECTS 테이블의 OBJECT_ID는 1번부터 없다. 그래서 실행시
<ORA-01403: 데이터가 없습니다>
라는 에러 발생

6. 요지

573페이지 하단의 TRACE를 보면 단일테이블 해시클러스터가 힙테이블에 비해 논리적 I/O가 비약적으로 줄어들었슴을 확인할 수 있다.

B*트리 인덱스 클러스터 테이블 (by kkabong) [2009.04.30]

1. 장점

2. 단점

1. 클러스터 생성하기

1. B*트리 클러스터

2. 해시 클러스터

1-1 B* 트리 클러스터 생성하기

1-2 해쉬 클러스터 생성하기

2. 클러스터 사용하기

2-1 데이터 적재방법 제어하기

2-2 B*트리-I/O를 줄이고 버퍼 캐시의 효율성 높이기

2-3 해쉬클러스터-인덱스 블럭 없애기

2-4 단일테이블 해쉬-읽기 전용 테이블에 유용

6. 요지