----------------------------------------------------------------------------------
-- 실습목표
-- 1.분리형 테이블의 구조 이해
-- 2.클러스터링 팩터의 개념 이해
-- 3.분리형, 클러스터 테이블, 정렬된 분리형 테이블 세 가지 유형 중에서
-- 클러스터링 팩터는 어느 한 가지 액세스 형태로는 유리하지만,
-- Secondary Access에 따른 클러스터링 팩터는 항상 높게 보장할 수 없음을 이해한다.
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
1. 분리형 테이블 조회하기
----------------------------------------------------------------------------------
1) ENC_ORDERS 테이블의 COMP_DATE 에서 현재의 대략적인 분포도를 확인해보세요.
SELECT AVG(CNT),MAX(CNT),MIN(CNT),SUM(CNT),COUNT(COMP_DATE)
FROM ( SELECT COMP_DATE , COUNT(*) CNT
FROM ENC_ORDERS
GROUP BY COMP_DATE
);
--결과--
1 22.3645303550006 2208 12 1323600 59183
2)아래 SQL을 실행하고 플랜과 TRACE를 확인해 보세요
SELECT *
FROM ENC_ORDERS
WHERE COMP_DATE = '20090101'
;
--결과--
Trace file: C:\Users\ENC-NP-1326\AppData\Local\Encore\IDO\Temp\xe_ora_10528.trc
********************************************************************************
SELECT *
FROM ENC_ORDERS
WHERE COMP_DATE = '20090101'
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 90 0.09 0.09 0 14435 0 2208
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 92 0.09 0.09 0 14435 0 2208
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 71 (ENCDB)
Rows Row Source Operation
------- ---------------------------------------------------
2208 TABLE ACCESS FULL ENC_ORDERS (cr=14435 pr=0 pw=0 time=3292 us cost=3960 size=1584 card=22)
3) 분리형 테이블에 인덱스 생성하세요.
CREATE INDEX ENC_ORDERS_IX01 ON ENC_ORDERS ( COMP_DATE );
4) 특정 날짜 조건 검색시 플랜과 TRACE를 확인해 보세요.
SELECT *
FROM ENC_ORDERS
WHERE COMP_DATE = '20090101'
;
--결과--
Trace file: C:\Users\ENC-NP-1326\AppData\Local\Encore\IDO\Temp\xe_ora_7660.trc
********************************************************************************
SELECT *
FROM ENC_ORDERS
WHERE COMP_DATE = '20090101'
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 90 0.02 0.01 8 233 0 2208
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 92 0.02 0.01 8 233 0 2208
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 71 (ENCDB)
Rows Row Source Operation
------- ---------------------------------------------------
2208 TABLE ACCESS BY INDEX ROWID ENC_ORDERS (cr=233 pr=8 pw=0 time=3475 us cost=25 size=1584 card=22)
2208 INDEX RANGE SCAN ENC_ORDERS_IX01 (cr=98 pr=8 pw=0 time=3958 us cost=3 size=0 card=22)(object id 20534)
5) 총 몇개의 블록을 ACCESS 했습니까?
--결과--
233 블록(총 액세스한 블록 수)
(인덱스 블록 : 98개, 액세스한 DATA 블록 : 135 (233-98) )
select 233-98 from dual;
----------------------------------------------------------------------------------
2.클러스터 테이블 만들기
----------------------------------------------------------------------------------
1) COMP_DATE 를 클러스터 키로 하는 인덱스 클러스터를 생성하세요.
-- 클러스터 생성
CREATE CLUSTER ENC_ORDERS_CLUSTER (COMP_DATE VARCHAR2(18))
SIZE 2000;
-- 클러스터 인덱스 생성
CREATE INDEX ENC_ORDERS_CLUSTER_IX01 ON CLUSTER ENC_ORDERS_CLUSTER;
-- 테이블 생성 ( 클러스터 생성 후 )
CREATE TABLE ENC_ORDERS_CLUSTER (
ORDER_NO VARCHAR2(18) NOT NULL,
CUST_ID VARCHAR2(18) NOT NULL,
ORD_DEPT_NO NUMBER NOT NULL,
ORD_EMP_NO NUMBER NOT NULL,
ORD_DATE DATE ,
ORD_HH VARCHAR2(18) ,
PROD_ID VARCHAR2(18) NOT NULL,
QUANTITY NUMBER ,
AMOUNT NUMBER ,
DEV_DEPT_NO NUMBER NOT NULL,
DEV_EMP_NO NUMBER NOT NULL,
DEV_DATE VARCHAR2(18) ,
COMP_DATE VARCHAR2(18) ,
ORDER_STATUS VARCHAR2(18)
) CLUSTER ENC_ORDERS_CLUSTER (COMP_DATE);
--데이터 생성
INSERT INTO ENC_ORDERS_CLUSTER
SELECT * FROM ENC_ORDERS
ORDER BY COMP_DATE;
COMMIT;
2) 특정 날짜 조건 검색시 플랜과 TRACE를 확인해 보세요.
SELECT *
FROM ENC_ORDERS_CLUSTER
WHERE COMP_DATE = '20090101'
;
--결과--
Trace file: C:\Users\ENC-NP-1326\AppData\Local\Encore\IDO\Temp\xe_ora_5308.trc
********************************************************************************
SELECT *
FROM ENC_ORDERS_CLUSTER
WHERE COMP_DATE = '20090101'
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 90 0.00 0.00 0 114 0 2208
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 93 0.00 0.00 0 114 0 2208
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 71 (ENCDB)
Rows Row Source Operation
------- ---------------------------------------------------
2208 TABLE ACCESS CLUSTER ENC_ORDERS_CLUSTER (cr=114 pr=0 pw=0 time=1059 us cost=2 size=36572 card=223)
1 INDEX UNIQUE SCAN ENC_ORDERS_CLUSTER_IX01 (cr=2 pr=0 pw=0 time=14 us cost=1 size=0 card=1)(object id 20536)
3) 총 몇개의 블록을 ACCESS 했습니까?
--결과--
114 블록(총 액세스한 블록 수)
(인덱스 블록 : 2개, 액세스한 DATA 블록 : 112 )
4) 1-5) 의 결과와 2-3)의 결과를 비교하여 차이점을 설명하세요.
--결과--
1-5)분리형의 경우 ( 233블록 = INDEX(98) + DATA(135) )
2-3)클러스터의 경우 ( 114블록 = INDEX( 2) + DATA(112) )
--INDEX 블록
2-3) INDEX가 2 블록을 액세스한 이유는 DISTINCT한 VALUE로 인덱스를 생성한 반면,
1-5) INDEX가 98 블록을 액세스한 이유는 DATA건수 만큼 인덱스를 생성하기 때문
--DATA 블록
2-3) DATA 블록 액세스가 1-5)보다 23블록 더 작게 액세스.
이는 액세스한 COMP_DATE 컬럼으로 클러스터링 되어 있으므로
동일 블록에 데이터가 저장되어 있을 확율이 높기 때문
----------------------------------------------------------------------------------
3. 정렬된 테이블 만들기
----------------------------------------------------------------------------------
1) 정렬된 테이블 생성(ENC_ORDERS 와 동일 구조)
CREATE TABLE ENC_ORDERS_ORDERBY_TAB NOLOGGING
AS
SELECT * FROM ENC_ORDERS ORDER BY COMP_DATE;
2) 정렬된 테이블에 인덱스 생성하세요.
CREATE INDEX ENC_ORDERS_ORDERBY_IX01 ON ENC_ORDERS_ORDERBY_TAB ( COMP_DATE );
3) 아래 문장을 실행해서 확인하기 전에 위 클러스터의 결과와 비교하면
ACCESS 되는 총 블록갯수는 위 클러스터의 결과보다 어떨 것이라고 예상하십니까?
SELECT *
FROM ENC_ORDERS_ORDERBY_TAB
WHERE COMP_DATE = '20090101' ;
--결과--
INDEX 블록은 클러스터 구조가 항상 적은 블록을 액세스 하므로 유리하나
DATA 블록은 정렬된 테이블이 클러스터 구조가 차지하는 블록수보다 같거나 작을 수 있음
왜냐하면 클러스터의 경우는 SIZE 파라미터에 의해 클러스터 키값을 저장하는 영역(블록) 이
확보되므로 해당 블록을 다 채우지 못한 경우에는 블록을 낭비할 수 있음
그러나 클러스터 인덱스의 경우는 키 값에 대한 값을 한번만 저장하므로 인덱스 저장공간을
아주 많이 줄일 수 있음
4) 정렬된 테이블에 특정 날짜 조건 검색시 플랜과 TRACE를 확인해 보세요.
SELECT *
FROM ENC_ORDERS_ORDERBY_TAB
WHERE COMP_DATE = '20090101' ;
--결과--
--TRACE
Trace file: C:\Users\ENC-NP-1326\AppData\Local\Encore\IDO\Temp\xe_ora_12188.trc
********************************************************************************
SELECT *
FROM ENC_ORDERS_ORDERBY_TAB
WHERE COMP_DATE = '20090101'
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 90 0.00 0.00 0 209 0 2208
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 93 0.00 0.00 0 209 0 2208
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 71 (ENCDB)
Rows Row Source Operation
------- ---------------------------------------------------
2208 TABLE ACCESS BY INDEX ROWID ENC_ORDERS_ORDERBY_TAB (cr=209 pr=0 pw=0 time=1190 us cost=60 size=25912 card=158)
2208 INDEX RANGE SCAN ENC_ORDERS_ORDERBY_IX01 (cr=98 pr=0 pw=0 time=1120 us cost=3 size=0 card=6290)(object id 20542)
--TRACE 결과 비교설명--
ENC_ORDERS_CLUSTER 클러스터 ( 114블록 = INDEX( 2) + DATA(112) )
ENC_ORDERS_ORDERBY_TAB 정렬된 테이블 ( 209블록 = INDEX(98) + DATA(111) )
INDEX 블록은 ENC_ORDERS_CLUSTER 가 항상 적은 블록을 액세스 하나
DATA 블록은 ENC_ORDERS_ORDERBY_TAB 과 유사 하다.
----------------------------------------------------------------------------------
4. 다른 컬럼값으로 ACCESS 했을때 블록 갯수의 차이를 비교
----------------------------------------------------------------------------------
1) ENC_ORDERS_CLUSTER(클러스터) , ENC_ORDERS_ORDERBY_TAB(정렬된 테이블) 의
ORDER_NO 로 인덱스 생성.
( ENC_ORDERS 에는 ORDER_NO 컬럼에 인덱스 존재 )
CREATE UNIQUE INDEX ENC_ORDERS_CLUSTER_IX02 ON ENC_ORDERS_CLUSTER (ORDER_NO);
CREATE UNIQUE INDEX ENC_ORDERS_ORDERBY_IX02 ON ENC_ORDERS_ORDERBY_TAB (ORDER_NO);
2) ENC_ORDERS(분리형) , ENC_ORDERS_CLUSTER(클러스터) , ENC_ORDERS_ORDERBY_TAB(정렬된 테이블)
을 ORDER_NO 로 검색시 플랜 및 TRACE를 확인하세요.
--ENC_ORDERS(분리형)
SELECT /*+ INDEX(A ENC_ORDERS_PK ) */ *
FROM ENC_ORDERS A
WHERE ORDER_NO LIKE '2814%' ;
--ENC_ORDERS_CLUSTER(클러스터)
SELECT /*+ INDEX(A ENC_ORDERS_CLUSTER_IX02) */ *
FROM ENC_ORDERS_CLUSTER A
WHERE ORDER_NO LIKE '2814%' ;
--ENC_ORDERS_ORDERBY_TAB(정렬된 테이블)
SELECT /*+ INDEX(A ENC_ORDERS_ORDERBY_IX02) */ *
FROM ENC_ORDERS_ORDERBY_TAB A
WHERE ORDER_NO LIKE '2814%' ;
--결과--
--ENC_ORDERS(분리형)
Rows Row Source Operation
------- ---------------------------------------------------
111 TABLE ACCESS BY INDEX ROWID ENC_ORDERS (cr=36 pr=5 pw=0 time=1433 us cost=4 size=72 card=1)
111 INDEX RANGE SCAN ENC_ORDERS_PK (cr=10 pr=1 pw=0 time=2966 us cost=3 size=0 card=1)(object id 20519)
--ENC_ORDERS_CLUSTER(클러스터)
Rows Row Source Operation
------- ---------------------------------------------------
111 TABLE ACCESS BY INDEX ROWID ENC_ORDERS_CLUSTER (cr=60 pr=2 pw=0 time=1069 us cost=5501 size=36572 card=223)
111 INDEX RANGE SCAN ENC_ORDERS_CLUSTER_IX02 (cr=8 pr=2 pw=0 time=1934 us cost=31 size=0 card=15124)(object id 20543)
--ENC_ORDERS_ORDERBY_TAB(정렬된 테이블)
Rows Row Source Operation
------- ---------------------------------------------------
111 TABLE ACCESS BY INDEX ROWID ENC_ORDERS_ORDERBY_TAB (cr=65 pr=52 pw=0 time=1675 us cost=3901 size=25912 card=158)
111 INDEX RANGE SCAN ENC_ORDERS_ORDERBY_IX02 (cr=8 pr=2 pw=0 time=3325 us cost=31 size=0 card=14152)(object id 20544)
3) 각각 ACCESS되는 블록 갯수는 몇개입니까?
--결과--
ENC_ORDERS (분리형) ( 36블록 = INDEX( 10) + DATA(26) )
ENC_ORDERS_CLUSTER (클러스터) ( 60블록 = INDEX( 8 ) + DATA(52) )
ENC_ORDERS_ORDERBY_TAB (정렬된 테이블) ( 65블록 = INDEX( 8 ) + DATA(57) )
4) 위 결과에 따른 시사점은?
--결과--
클러스터링 팩터는 한개의 인덱싱된 컬럼을 기준으로는 클러스터링 팩터를 좋게 할 수 있지만
실제 업무에서는 여러가지의 ACCESS 패턴들이 존재하므로 여러가지 상황들을 고려하여 설계해야 합니다.