조인기법
조인 종류
1) Sort-Merge Join : 조인에 참여하는 컬럼에 모두 인덱스 없는 경우. 성능이 가장 나쁨
2) Nested-Loops Join : 가장 보편적, 조인에 참여하는 컬럼 둘다 또는 하나에 인덱스가 사용되는 경우
3) Outer Join : 외부키에 null이 포함되어도 검색대상에 포함..
4) Hash Join : Master-Detail, Parent-Child 관계의 테이블에서 사용
- NL, SM join 보다는 성능이 좋지만 CBO에서만 사용할 수 있음
- 성능을 향상하는 파라메터
- HASH_JOIN_ENABLE : 10g부터 default
- HASH_AREA_SIZE : 해쉬조인시 사용되는 메모리 최대크기 (기본값은 SORT_AREA_SIZE 의 2배)
- HASH_MULTIBLOCK_IO_COUNT : 9i까지, 10g부터는 DB_MULTIBLOCK_READ_COUNT 와 통합됨
5) Cluster Join :
6) Star Join : 대용량의 fact테이블과 이를 참조하는 차원테이블이 많을때, 데이터웨어하우스 & CBO 에서
조인시 성능에 영향을 주는 요소
(1) Driving Table 선택에 의한 성능 차이 : 데이터양과 분포도에 따라 선택
- 먼저 실행되는 테이블은 Driving Table 또는 Outer Table, 나중에 실행되는 테이블은 Inner Table
- 동등 조건이면 가장 오른쪽 테이블부터 : 모두 같은 인덱스 있거나 모두 인덱스 없거나..
ex) select * from A(3), B(2), C(1) - 싱글 인덱스 < 결합 인덱스, non-unique 인덱스 < unique 인덱스
ex) select * from A(싱글,3), B(결합,1), C(싱글,2) - and 일때 왼쪽부터
ex) select * from A from deptno=10(1) and deptno=180(2) - or 일때 오른쪽부터
ex) select * from A from deptno=10(3) or deptno=180(2) or deptno=180(1)
==> 내부적으로는 union all
==> order by 필요없이 deptno=30 or deptno=20 or deptno=10으로 쿼리하면 10,20,30 순으로 출력
(2) 테이블의 조인 순서에 의한 성능 차이
- 구동테이블이 선택되면 나머지들의 순서도 잘 정해줘야함.
ex) 테이블 A, B, C 일때 순서에 대한 경우의 수는 6가지 (n!) - 조인하는 테이블수가 많으면 CBO일때 cost계산에 부하가 너무 많이 걸림
--> 어느정도에서 cut off됨
--> 더 좋은 실행계획을 위해 hint을 통해 join 순서를 정해줄 수도 있다.
(3) 테이블의 검색 양에 의한 성능 차이 : 넓은->좁은 범위로, full_table_scan --> index_scan 으로
(4) Index 사용 여부에 의한 성능 차이 : 잘못된 SQL로 인해 인덱스를 사용못할 수도 있다.
SQL> create index i_emp_empno_deptno on big_emp (deptno, sal, empno);
인덱스가 생성되었습니다.
SQL> create index i_dept_deptno on big_emp (deptno);
인덱스가 생성되었습니다.
SQL> create index i_account_customer on account (close_status, customer);
인덱스가 생성되었습니다.
SQL> select a.empno, a.ename, a.deptno, b.dname, c.no
2 from big_dept b, big_emp a, account c
3 where b.deptno = a.deptno
4 and a.empno = c.customer
5 and a.deptno = 10 and a.sal > 2000 and groupno='2'
6 and c.close_status = 'Y';
EMPNO ENAME DEPTNO DNAME NO
---------- ---------- ---------- -------------- ----------
5497 KING 10 RESEARCH 1300005497
5427 KING 10 RESEARCH 1300005427
5455 KING 10 RESEARCH 1300005455
SQL> @tkprof.sql
********************************************************************************
select
a.empno, a.ename, a.deptno, b.dname, c.no
from big_dept b, big_emp a, account c
where b.deptno = a.deptno
and a.empno = c.customer
and a.deptno = :"SYS_B_0" and a.sal > :"SYS_B_1" and groupno=:"SYS_B_2"
and c.close_status = :"SYS_B_3"
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 2 0.00 0.04 43 522 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.04 43 522 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID ACCOUNT (cr=522 pr=43 pw=0 time=20036 us)
7 NESTED LOOPS (cr=520 pr=43 pw=0 time=120048 us)
3 NESTED LOOPS (cr=514 pr=43 pw=0 time=20050 us)
289 TABLE ACCESS FULL BIG_DEPT (cr=4 pr=0 pw=0 time=647 us)
3 TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=510 pr=43 pw=0 time=40506 us)
5336 INDEX RANGE SCAN I_DEPT_DEPTNO (cr=329 pr=43 pw=0 time=40557 us)(object id 52644)
3 INDEX RANGE SCAN I_ACCOUNT_CUSTOMER (cr=6 pr=0 pw=0 time=41 us)(object id 52645)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: RULE
3 TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUNT' (TABLE) ==> 3
7 NESTED LOOPS
3 NESTED LOOPS
289 TABLE ACCESS (FULL) OF 'BIG_DEPT' (TABLE) ==> 2
3 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'BIG_EMP' (TABLE) ==> 1
5336 INDEX MODE: ANALYZED (RANGE SCAN) OF 'I_DEPT_DEPTNO' (INDEX)
3 INDEX MODE: ANALYZED (RANGE SCAN) OF 'I_ACCOUNT_CUSTOMER' (INDEX)
********************************************************************************
==> 조인 순서를 바꾼후 성능 좋아짐
SQL> select --+ ORDERED
2 a.empno, a.ename, a.deptno, b.dname, c.no
3 from big_dept b, big_emp a, account c
4 where b.deptno = a.deptno
5 and a.empno = c.customer
6 and a.deptno = 10 and a.sal > 2000 and groupno='2'
7 and c.close_status = 'Y';
********************************************************************************
select --+ ORDERED
a.empno, a.ename, a.deptno, b.dname, c.no
from big_dept b, big_emp a, account c
where b.deptno = a.deptno
and a.empno = c.customer
and a.deptno = :"SYS_B_0" and a.sal > :"SYS_B_1" and groupno=:"SYS_B_2"
and c.close_status = :"SYS_B_3"
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 3 0 0
Fetch 2 0.01 0.00 0 399 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 402 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
3 HASH JOIN (cr=399 pr=0 pw=0 time=8456 us)
3 HASH JOIN (cr=188 pr=0 pw=0 time=3777 us)
1 TABLE ACCESS FULL BIG_DEPT (cr=3 pr=0 pw=0 time=91 us)
3 TABLE ACCESS FULL BIG_EMP (cr=185 pr=0 pw=0 time=3383 us)
2868 TABLE ACCESS FULL ACCOUNT (cr=211 pr=0 pw=0 time=2951 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: RULE
3 HASH JOIN
3 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'BIG_EMP' (TABLE) ==> 2
1 NESTED LOOPS
3 TABLE ACCESS (FULL) OF 'BIG_DEPT' (TABLE) ==> 1
2868 INDEX MODE: ANALYZED (RANGE SCAN) OF 'I_EMP_EMPNO_DEPTNO' (INDEX)
0 TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUNT' (TABLE) ==> 3
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'I_ACCOUNT_CUSTOMER' (INDEX)
********************************************************************************
Driving Table(구동테이블) 선택 기준
Sort Merge Join 경우
2개의 테이블의 컬럼에 index가 없으므로 from 절의 순서를 따름 --> 우측 테이블이 구동테이블
Nested Loop Join 경우
한쪽에 index가 없으면 어차피 full table scan 일어난다 --> index 없는 테이블이 구동테이블
index있는 테이블과 unique-index 있는 테이블 --> unique-index가 더 성능이 좋으므로 구동테이블
모두 같은 레벨의 index가 있는 테이블 --> 오른쪽 테이블이 구동테이블
Outer Join 경우
모든값을 가진 테이블쪽이 구동테이블 : select ~~ from emp, detp where emp.detpno (+)= dept.deptno;
조인문 작성 방법
- 추출하고자 하는 테이블의 행수를 분석
- 부분처리가 가능한 테이블 중에서 분석된 행수와 가장 유사한 행수를 가진 테이블을 구동테이블로 선정
- 인덱스 종류를 분석후 select list 컬럼절을 결정 (fast-index scan)
- 조인컬럼과 인덱스 구조를 분석하여 구동테이블 결정 (검색 범위가 좁은 놈으로)
- 구동 테이블과 내부 테이블 간의 관계 고려 (from 절에서 구동테이블을 우측순으로)
- sql 작성 완료후 분석/통계생성 (성능이 안 나오면 hint 등을 사용)
보고서 작성
(1) decode 함수
JOB SAL SALARY
--------- ---------- ----------
SALESMAN 2532 2532
SALESMAN 2182.5 2182.5
MANAGER 3908 3908
SALESMAN 2183.5 2183.5
MANAGER 3784 3784
MANAGER 3384.5 3384.5
ANALYST 3935 4328.5
(2) case 함수 : decode는 내부적으로 sort 발생하므로 8i 이후 추가된 기능
2 case when job='ANAYST' then sal*1.10
3 when job='CLERK' then sal*1.15
4 else sal
5 end as salary
6 from emp;
JOB SAL SALARY
--------- ---------- ----------
CLERK 800 920
SALESMAN 1600 1600
SALESMAN 1250 1250
MANAGER 2975 2975
SALESMAN 1250 1250
MANAGER 2850 2850
MANAGER 2450 2450
ANALYST 3000 3000
PRESIDENT 5000 5000
SALESMAN 1500 1500
CLERK 1100 1265
CLERK 950 1092.5
ANALYST 3000 3000
CLERK 1300 1495
(3) rollup, cube : 주로 corsstab(좌우로 합계를 내는 보고서)를 만들때 사용
2 from emp
3 group by rollup(deptno, job);
DEPTNO GROUPING(DEPTNO) JOB GROUPING(JOB) COUNT(*) AVG(SAL)*12
---------- ---------------- --------- ------------- ---------- -----------
10 0 CLERK 0 1 15600
10 0 MANAGER 0 1 29400
10 0 PRESIDENT 0 1 60000
10 0 1 3 35000 ==> 부서 바뀔때마다 합
20 0 CLERK 0 2 11400
20 0 ANALYST 0 2 36000
20 0 MANAGER 0 1 35700
20 0 1 5 26100 ==> 부서 바뀔때마다 합
30 0 CLERK 0 1 11400
30 0 MANAGER 0 1 34200
30 0 SALESMAN 0 4 16800
30 0 1 6 18800 ==> 부서 바뀔때마다 합
1 1 14 24878.5714 ==> 전체합
2 from emp
3 group by cube(deptno, job);
---------- ---------------- --------- ------------- ---------- -----------
1 1 14 24878.5714 ==> 총 집계
1 CLERK 0 4 12450 ==> 직업별 집계
1 ANALYST 0 2 36000
1 MANAGER 0 3 33100
1 SALESMAN 0 4 16800
1 PRESIDENT 0 1 60000
10 0 1 3 35000 ==> 부서별 집계
10 0 CLERK 0 1 15600
10 0 MANAGER 0 1 29400
10 0 PRESIDENT 0 1 60000
20 0 1 5 26100 ==> 부서별 집계
20 0 CLERK 0 2 11400
20 0 ANALYST 0 2 36000
20 0 MANAGER 0 1 35700
30 0 1 6 18800 ==> 부서별 집계
30 0 CLERK 0 1 11400
30 0 MANAGER 0 1 34200
30 0 SALESMAN 0 4 16800
튜닝실습 #1
- 유인물 참조 실행결과 : orcl_ora_1412.trc.txt
- 심화학습 : SQL심화과정.zip
튜닝실습 #2
********************************************************************************
SELECT A.SUJU_NO, B.ILLYEON_NO, C.GOGAEK_NM, D.PROM_CD, B.SANGPUM_CD,
B.SURYANG, B.DANGA
FROM S_SUJU A, S_SUJU_DTL B, S_GOGAEK C, S_PROMOTION D
WHERE A.SUJU_NO = B.SUJU_NO AND
A.GOGAEK_NO = C.GOGAEK_NO AND
A.PROM_CD = D.PROM_CD AND
A.JUMUN_DT LIKE :"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.04 0.03 0 0 0 0
Fetch 817 3.71 9.44 70382 71206 0 12229
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819 3.76 9.48 70382 71206 0 12229
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
12229 HASH JOIN (cr=71206 pr=70382 pw=0 time=5417700 us)
1063 INDEX FAST FULL SCAN SYS_C005421 (cr=6 pr=5 pw=0 time=21753 us)(object id 52567)
12229 HASH JOIN (cr=71200 pr=70377 pw=0 time=5358177 us)
3238 HASH JOIN (cr=43927 pr=43920 pw=0 time=3499826 us)
3238 TABLE ACCESS FULL S_SUJU (cr=29436 pr=29432 pw=0 time=231249 us)
378789 TABLE ACCESS FULL S_GOGAEK (cr=14491 pr=14488 pw=0 time=1906429 us)
4136970 TABLE ACCESS FULL S_SUJU_DTL (cr=27273 pr=26457 pw=0 time=4150124 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
12229 HASH JOIN
1063 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 'SYS_C005421'
(INDEX (UNIQUE))
12229 HASH JOIN
3238 HASH JOIN
3238 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'S_SUJU'
(TABLE)
378789 INDEX MODE: ANALYZED (RANGE SCAN) OF 'I_S_SUJU' (INDEX)
4136970 TABLE ACCESS MODE: ANALYZED (FULL) OF 'S_GOGAEK' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'S_SUJU_DTL' (TABLE)
********************************************************************************
2. rule-based optimizer로 실행시
********************************************************************************
SELECT /*+ rule */
A.SUJU_NO, B.ILLYEON_NO, C.GOGAEK_NM, D.PROM_CD,
B.SANGPUM_CD, B.SURYANG, B.DANGA
FROM S_SUJU A, S_SUJU_DTL B, S_GOGAEK C, S_PROMOTION D
WHERE A.SUJU_NO = B.SUJU_NO AND
A.GOGAEK_NO = C.GOGAEK_NO AND
A.PROM_CD = D.PROM_CD AND
A.JUMUN_DT LIKE :"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 817 0.28 2.58 480 28125 0 12229
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819 0.29 2.59 480 28125 0 12229
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
12229 TABLE ACCESS BY INDEX ROWID S_SUJU_DTL (cr=28125 pr=480 pw=0 time=5447157 us)
15468 NESTED LOOPS (cr=25705 pr=432 pw=0 time=11043563 us)
3238 NESTED LOOPS (cr=17565 pr=261 pw=0 time=1140478 us)
3238 NESTED LOOPS (cr=7035 pr=14 pw=0 time=87740 us)
3238 TABLE ACCESS BY INDEX ROWID S_SUJU (cr=2981 pr=14 pw=0 time=48875 us)
3238 INDEX RANGE SCAN I_S_SUJU (cr=828 pr=14 pw=0 time=35908 us)(object id 52575)
3238 INDEX UNIQUE SCAN SYS_C005421 (cr=4054 pr=0 pw=0 time=25687 us)(object id 52567)
3238 TABLE ACCESS BY INDEX ROWID S_GOGAEK (cr=10530 pr=247 pw=0 time=1695294 us)
3238 INDEX UNIQUE SCAN PK_T_GOGAEK (cr=7292 pr=10 pw=0 time=123482 us)(object id 52560)
12229 INDEX RANGE SCAN PK_T_SUJU_DTL (cr=8140 pr=171 pw=0 time=454625 us)(object id 52579)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: RULE
12229 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'S_SUJU_DTL'
(TABLE)
15468 NESTED LOOPS
3238 NESTED LOOPS
3238 NESTED LOOPS
3238 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'S_SUJU' (TABLE)
3238 INDEX MODE: ANALYZED (RANGE SCAN) OF 'I_S_SUJU'
(INDEX)
3238 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SYS_C005421'
(INDEX (UNIQUE))
3238 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'S_GOGAEK' (TABLE)
3238 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_T_GOGAEK'
(INDEX (UNIQUE))
12229 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_T_SUJU_DTL'
(INDEX (UNIQUE))
********************************************************************************
3. 튜닝
1) rule-based가 유리함을 알았고, from절에서 구동테이블이 S_SUJU 임을 판단함
2) S_SUJU의 인덱스를 조사
Table Pct Cluster
Table # Rows Index Space U Column # Dist Free Factor BLevel Leaf BALANCE
------------- -------- -------------------- ---------- - ------------ -------- ---- ------- ------ ----- -------
S_SUJU 828209 I_S_SUJU_CHULHAJISI_NO SYSTEM N SUJU_NO 828209 10 ###### 2 4701
SYSTEM CHULHAJISI_NO 794199 10 ###### 2 4701
828209 I_S_SUJU SYSTEM N PROM_CD 1023 10 ###### 2 2877
SYSTEM JUMUN_DT 1886 10 ###### 2 2877
828209 PK_T_SUJU SYSTEM U SUJU_NO 828209 3 ###### 2 2780
3) S_SUJU의 검색조건중에 A.JUMUN_DT LIKE '2001%'; 와 같은 구문이 있어서 인덱스 추가함
CREATE INDEX I_SUJU_JUMUN_DT ON S_SUJU(JUMUN_DT);
********************************************************************************
SELECT /*+ rule */
A.SUJU_NO, B.ILLYEON_NO, C.GOGAEK_NM, D.PROM_CD,
B.SANGPUM_CD, B.SURYANG, B.DANGA
FROM S_SUJU A, S_SUJU_DTL B, S_GOGAEK C, S_PROMOTION D
WHERE A.SUJU_NO = B.SUJU_NO AND
A.GOGAEK_NO = C.GOGAEK_NO AND
A.PROM_CD = D.PROM_CD AND
A.JUMUN_DT LIKE :"SYS_B_0"
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 817 0.21 0.29 13 26508 0 12229
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819 0.21 0.30 13 26508 0 12229
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
12229 TABLE ACCESS BY INDEX ROWID S_SUJU_DTL (cr=26508 pr=13 pw=0 time=335889 us)
15468 NESTED LOOPS (cr=24939 pr=13 pw=0 time=2583119 us)
3238 NESTED LOOPS (cr=16802 pr=13 pw=0 time=140907 us)
3238 NESTED LOOPS (cr=6275 pr=13 pw=0 time=82616 us)
3238 TABLE ACCESS BY INDEX ROWID S_SUJU (cr=2224 pr=13 pw=0 time=46990 us)
3238 INDEX RANGE SCAN I_SUJU_JUMUN_DT (cr=822 pr=13 pw=0 time=34023 us)(object id 52656)
3238 INDEX UNIQUE SCAN SYS_C005421 (cr=4051 pr=0 pw=0 time=28656 us)(object id 52567)
3238 TABLE ACCESS BY INDEX ROWID S_GOGAEK (cr=10527 pr=0 pw=0 time=56800 us)
3238 INDEX UNIQUE SCAN PK_T_GOGAEK (cr=7289 pr=0 pw=0 time=26977 us)(object id 52560)
12229 INDEX RANGE SCAN PK_T_SUJU_DTL (cr=8137 pr=0 pw=0 time=52960 us)(object id 52579)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: RULE
12229 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'S_SUJU_DTL'
(TABLE)
15468 NESTED LOOPS
3238 NESTED LOOPS
3238 NESTED LOOPS
3238 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'S_SUJU' (TABLE)
3238 INDEX MODE: ANALYZED (RANGE SCAN) OF
'I_SUJU_JUMUN_DT' (INDEX)
3238 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SYS_C005421'
(INDEX (UNIQUE))
3238 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'S_GOGAEK' (TABLE)
3238 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_T_GOGAEK'
(INDEX (UNIQUE))
12229 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_T_SUJU_DTL'
(INDEX (UNIQUE))
********************************************************************************
구체화된 뷰
- View : 실시간의 데이터는 유지하지만 시간이 많이 걸린다는 단점, data없음. Select만 됨. 논리적 존재.
- Table : Data있음. Select/Insert/Update/Delete 됨. 물리적 존재.
- CTAS : Summary table (CREATE TABLE AS SELECT ~~), 실시간 데이터는 아니지만 성능 좋음
- MView : Materialized View, 물리적 존재. Data 있음. Select만 됨
- 대용량의 데이터를 SUM, MIN, MAX, AVG, COUNT(*)이런 명령어를 사용해 자주 조회하는 Query를 수행속도를 향상을 위해서 Query 결과 만큼의 새로운 테이블을 생성해 놓는 벙법
파라메터 확인
SQL> col parameter format a30
SQL> col value format a20
SQL> select * from v$option;
PARAMETER VALUE
----------------------------------- --------------------
Materialized view rewrite TRUE
Materialized view warehouse refresh TRUE
==> 테스트한 버전은 10g 엔터프라이즈여서 true가 디폴트임
권한 생성
SQL> connect system/비번
SQL> grant create materialized view to scott;
SQL> grant global query Rewrite to scott;
구체화된 뷰 생성
SQL> connect scott/tiger
SQL> create materialized view reg_count
tablespace users
build immediate ┌-> 주기적으로 갱신하려면 : start with sysdate+1/24 (1시간마다)
refresh complete on commit -┴-> 변경/입력시 새로생성 (refresh fast는 변경/입력내용만 갱신함)
enable query rewrite --> as 이하의 쿼리가 오면 MView 내용을 보도록 실행계획 잡는다.
as select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno;
구체화된 뷰가 생성되었습니다.
구체화된 뷰가 생성되었는지 확인
SQL> select * from user_objects where object_name like '%REG_COUNT%';
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL TIMESTAMP STATUS T G S
-------- -------- ------------------- ------- - - -
REG_COUNT
52659 52659 TABLE
08/06/04 08/06/04 2008-06-04:16:39:55 VALID N N N
REG_COUNT
52660 MATERIALIZED VIEW
08/06/04 08/06/04 2008-06-04:16:39:55 VALID N N N
Query Rewrite를 사용하는지 실행계획 확인 #1
SQL> set autotrace on
SQL> select /*+rewrite*/ a.empno, a.ename, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2074428718
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 406 | 3 (0)|00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| REG_COUNT | 14 | 406 | 3 (0)|00:00:01 | ==> rewrite 실행함
------------------------------------------------------------------------------------------
Query Rewrite를 사용하는지 실행계획 확인 #2
SQL> select /*+norewrite*/ a.empno, a.ename, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 770 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 2 (0)| 00:00:01 | ==> rewrite 실행 안함
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Rewrite를 사용하는지 실행계획 확인 #3
SQL> alter session set query_rewrite_enabled = false;
세션이 변경되었습니다.
SQL> select /*+rewrite*/ a.empno, a.ename, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 770 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 2 (0)| 00:00:01 | ==> 환경설정으로 인해 rewrite 실행 안함
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> alter session set query_rewrite_enabled = true;
세션이 변경되었습니다.
커밋되었을때 구체화된뷰에 입력되는지 확인
SQL> create materialized view tot_count
2 build immediate
3 refresh complete on commit
4 enable query rewrite
5 as select deptno, sum(sal) from emp group by deptno;
구체화된 뷰가 생성되었습니다.
SQL> select * from tot_count;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
SQL> insert into emp(empno, deptno, sal) values (11, 10, 1000);
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select * from tot_count;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 9750 ==> 변경됨
클러스터
(1) 종류
- Index Cluster : 2개 이상 table이 자주 join 되는 경우 사용.
- Hash Cluster : 1개의 table 일때.
(2) Index Cluster
- join 되는 테이블을 처음부터 블록 구조내에 join하여 결합된 형태로 저장
- I/O 개선, 저장공간 줄임
- 입력,수정,삭제가 빈번한 테이블에서는 성능이 저하됨
- 생성 절차 : index cluster → cluster index → table1 → table2
2 (dept number(3))
3 size 1000;
클러스터가 생성되었습니다.
SQL> create index i_clu_emp_edpt
2 on cluster clu_emp_dept;
인덱스가 생성되었습니다.
SQL> create table dept
2 (deptno number(3),
3 dname varchar2(14))
4 cluster clu_emp_dept(deptno);
테이블이 생성되었습니다. --> 각각의 테이블에 데이터를 입력한다.
SQL> create table emp
2 (empno number(4),
3 ename varchar2(10),
4 deptno number(3))
5 cluster clu_emp_dept(deptno);
테이블이 생성되었습니다. --> 각각의 테이블에 데이터를 입력한다.
SQL> select rowid, empno, ename, deptno from emp;
ROWID EMPNO ENAME DEPTNO
------------------ ---------- ---------- ----------
AAAHckAAJAAAABGAAA 111 일지매 10
AAAHckAAJAAAABGAAB 112 황진희 20
AAAHckAAJAAAABGAAC 113 홍길동 30
EMP 테이블과 DEPT 테이블에서 데이터를 저장하기 위해 저장한 블럭이
AAAABG 로 같다는 것을 확인 할수 있다.
클러스터를 이용하기 전에는 위에서 처럼 블록이 서로 달랐다.
SQL> select rowid, deptno, dname from dept;
ROWID DEPTNO DNAME
------------------ ---------- --------------
AAAHckAAJAAAABGAAA 10 전산과
AAAHckAAJAAAABGAAB 20 인사과
AAAHckAAJAAAABGAAC 30 총무과
일반적인 경우에는 서로 다른 블록에 데이터가 저장되어 있지만 인덱스 크러스트는
조인될때 조인되는 컬럼 값을 기준으로 같은 블록 내에 행이 저장된다.
그리고 SELECT문이 실행되면 하나의 블록에 있는 데이터를 읽어서 별도의 분류작업 없이
사용자에게 결과를 보여주게 된다.
즉 2개 이상의 테이블이 조인된 형태로 블록에 저장되는 기법이다.
SQL> select emp.empno, emp.ename, dept.dname
2 from emp, dept
3 where emp.deptno = dept.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
111 일지매 전산과
112 황진희 인사과
113 홍길동 총무과
사용된 예제는 실제로 데이터를 얼마 가지고 있지 않기때문에 검색상의 속도 차이는 느낄수 없지만
행수가 많다면 인덱스 크러스트를 이용하는 방법이 빠를 것이다.
- 삭제 절차 #1 : table1 → table2 → cluster
DROP TABLE [테이블명2] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] CASCADE CONSTRAINTS;
- 삭제 절차 #2 : cluster
- 성능 개선이 기대되는 테이블
2) Master-Detail 관계에서 Detail 테이블
3) Master-Detail 관계의 테이블
4) 자주 조인되지만 개별적으로는 검색되지 않는 테이블
(3) Hash Cluster
- hash 함수를 통해 빠르게 cluster-key를 찾음
- Hash Cluster 생성해야 하는 경우
(범위로 검색하면 성능 떨어짐)
2) table에 모든행을 저장하는데 저장공간이 얼마인지 알 수 있는 경우
(미리 충분한 공간을 확보하여 생성하는 방식이므로 잘못 예측시 성능이 떨어진다.)
3) cluster에 의해 행의 값이 균등하게 분산된 경우
- 생성 : cluster → table
(empno number(4)) --> empno 컬럼은 해쉬함수에 의한 결과값이며 클러스트 키를 의미한다.
HASHKEYS 40 --> 해쉬 클러스트가 저장하게 될 전체 행수를 의미한다.
HASH IS mod(empno,1000) --> 해쉬 함수를 정의해 주는 부분 해쉬함수는 사용자가 직접 정의할수도 있고
SIZE 40; ---┐ 오라클 서버가 제공하는 해쉬알고리즘을 사용할 수도 있으며,
| 테이블 내의 특정 컬럼을 정의할 수도 있다.
└-> 하나의 블록 내에 저장되는 하나의 행 길이를 의미한다.
(empno number(4),
ename varchar2(10),
sal number,
comm number)
CLUSTER clu2(empno);
- 삭제 #1
DROP TABLE [테이블명2] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] CASCADE CONSTRAINTS;
- 삭제 #2
파티션 뷰
- Partition view는 Oracle Release 7.3 에서 대용량 테이블을 지원하기 위해서 추가된 새로운 방법이다.
- 장점
- 각 테이블별로 index 생성될 수 있고, 병렬 옵션 사용 가능
- 병렬 옵션으로 partition view 전체를 병렬 스캔할 수 있음
- Partition 제거 (where조건을 만족하는 table만 검색)
- 단점
- patition view를 통해 table 변경 못함
- patition view에 대한 index를 생성할 수 없음
- 관련 parameter : PARTITION_VIEW_ENABLED
- 예제 : 서로다른 tablespace에 정의된 table을 union all 을 통해 view로 생성 --> 경합이 줄어듬
as select * from jeon1999 ---> 서로 다른 디스크에 생성된 테이블 (1999년 전표)
union all
select * from jeon2000; ---> 서로 다른 디스크에 생성된 테이블 (2000년 전표)
SQL> select * from tot_chul; ---> total 전표 view
파티션 테이블
- partition view는 유지보수와 관리가 불편하므로 추가된 기법
- table의 값을 분할 방법에 따라 서로 다른 tablespace를 할당 (경합을 피하기 위해 tablespace는 각 다른 disk)
- 종류
- Range Patition : 컬럼값을 기준으로 범위를 정해서 분할
- Hash Patition : 오라클서버가 가지고 있는 해시알고리즘으로 알아서 분할
- List Patition : 정의된 컬럼값 List를 통해 분할
1. partition table 생성하기
create table part_tbl
( in_date number primary key ,
empno number,
ename varchar2(20),
job varchar2(20) )
partition by range (in_date)
(
partition part_tbl_200803 values less than (20080332) tablespace pts_03,
partition part_tbl_200804 values less than (20080432) tablespace pts_04,
partition part_tbl_200805 values less than (20080532) tablespace pts_05,
partition part_tbl_max values less than (maxvalue) tablespace pts_max
);
2. add partition 하기
6월 partition을 add하고 싶은 경우 다음과 같이 할 수 있다.
alter table part_tbl add partition part_tbl_200806 values less than (20080632) tablespace pts_06;
# 단 MAX 파티션 테이블이 있는경우는 add partition 할 수 없다!
따라서 위 예제에서 생성된 파티션 테이블에서는 add partition 불가!!
3. 특정 partition 을 삭제하기
3월에 해당하는 partition을 없애고 싶은 경우는 다음과 같이 실행한다.
alter table part_tbl drop partition part_tbl_200803;
만약..
drop된 후에 새로 3월에 해당하는 데이타가 입력되면
4월의 partition이 less then (20080432) 으로 되어 있으므로
4월에 해당하는 partition에 저장된다.
3. partition 나누기
6월에 해당하는 partition을 생성하려면 MAX partition에 add하는 것으로는 불가능하고 MAX partition을 split 해야 한다.
alter table part_tbl split partition part_tbl_max at (20080632)
into (partition part_tbl_200806 tablespace pts_06, partition part_tbl_max tablespace pts_max);
+)
into(partition AAA, partition BBB) -> BBB파티션을 AAA 파티션으로 split한다.
위와 같이 하면 기존의 MAX 파티션 테이블에서 6월31일을 이전 데이터는 part_tbl_200806에 옮겨지고
MAX 파티션 테이블에는 6월31일 이후의 데이터가 들어있게 된다.
4. partition name을 변경하기
partition name 을 바꾸고 싶다면 다음과 같이 실행한다.
alter table part_tbl rename partition part_tbl_200805 to part_tbl_200805_new;
5. partition의 tablespace를 옮기기
partition part_tbl_05을 저장하는 tablespace를 pts_05 에서 pts_new로 바꾸고 싶은 경우는 다음과 같이 실행한다.
alter table part_tbl move partition part_tbl_05 tablespace pts_new nologging;
6. 특정 partition의 data를 truncate하기
partition의 data를 모두 삭제하려면 truncate하는 방법을 사용할 수가 있는 데,
truncate는 rollback 이 불가능하며 특정 partition 전체를 삭제하므로 주의하여 사용하여야 한다.
alter table part_tbl truncate partition part_tbl_03;
7. Partition table의 물리적인 속성 변경하기
partition table은 특정 partition의 속성만 변경할 수 있고,
table의 속성을 변경하여 전체 partition에 대해 동일한 변경을 할 수 있다.
alter table part_tbl storage( next 10M);
-> part_tbl 의 모든 partition의 next 값이 변경된다.
alter table part_tbl modify partition part_tbl_05 storage ( maxextents 1000 );
-> part_tbl_05 partition의 maxextents 값만 변경한다.
8. Index의 관리
변경작업을 통해 테이블 파티션이 변경된 경우 해당 테이블에 관련된 인덱스를 rebuild 해야합니다.
1) local 인덱스인 경우
DDL작업을 통해 변경된 파티션 테이블에 걸려있는 해당 local 인덱스만 "UNUSABLE" 상태가 된다.
-> MAX를 Split 한경우는 MAX 파티션과 Split로 새롭게 생성된 파티션의 인덱스 상태만 "UNUSABLE" 된다.
따라서 해당 파티션 인덱스를 반드시 rebuild 해 주어야 합니다.
-- 파티션 상태 확인
select INDEX_NAME, PARTITION_NAME, HIGH_VALUE, STATUS, TABLESPACE_NAME
from user_ind_partitions;
-- local Index rebuild 하기
alter index 인덱스이름 rebuild partition 파티션이름;
예제)
part_tbl_max 를 part_tbl_200806로 split 했을경우
각각의 파티션에 걸린 인덱스 part_tbl_indx_pk1 는 UNUSABLE 상태가 된다.
NDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------ ---------------------------- ---------------- ----------------- ------------------------------
part_tbl_indx_pk1 part_tbl_200804 '20080432' USABLE pts_04
part_tbl_indx_pk1 part_tbl_200805 '20080432' USABLE pts_05
part_tbl_indx_pk1 part_tbl_200806 '20080632' UNUSABLE pts_06
part_tbl_indx_pk1 part_tbl_max MAXVALUE UNUSABLE pts_max
해당 파티션 인텍스를 rebuild 해준다.
alter index WEMS_BPEMAAN_PK1 rebuild partition WEMS_BPEMAAN_200806_P6;
alter index WEMS_BPEMAAN_PK1 rebuild partition WEMS_BPEMAAN_MAX;
2)global Index인 경우
- 변경된 해당 파티션 뿐만 아니라 해당 테이블 파티션에 관련된 모든 global 인덱스 상태를 "UNUSABLE"로 만든다.
따라서 인덱스 전체를 반드시 rebuild 해 주어야 합니다.
-- global Index rebuild 하기
alter index 인덱스이름 rebuild;
파티션 인덱스
- 분할된 Table에 생성하는 인덱스
- 종류 (구조적 기준)
- Local Index : Table과 Index의 분할구조가 다른 경우
- Global Index : Table과 Index의 분할구조가 같은 경우
- 종류 (분할키 사용 기준)
- prefix Index : 분할 Table의 분할 컬럼으로 생성한 index
- Non-Prefix Index : 분할 Table의 분할 컬럼이 아닌 다른 컬럼 생성한 index
