이펙티브 오라클 (2008년)
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가 비약적으로 줄어들었슴을 확인할 수 있다.
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3558
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.