[Hint]ACCESS 경로를 변경하는 힌트(REWRITE)
구로디지털 오엔제이프로그래밍실무교육센터
이 힌트는 CBO에서 Matreriakized views(구체화뷰)에 대해 Query Rewrite가 일어나도록 하는 힌트인데 8i 이상부터 가능 합니다.
REWRITE 힌트 구문에 VIEW가 인자로 와도 되고 안 와도
됩니다. 인자로 뷰 리스트를
주지 않는 경우 적절한
materialized view를
찾고 항상 비용(COST)과 관계없이 사용 합니다.
Materialized
views라는 것이 DW(Data WareHouse)에서
집계 데이터 등을 추출할 때 쿼리 수행속도를 빠르게 해주는 것인데 Oracle에서 Query Rewrite가 일어나기
위해서는 다음과 같은 조건이 만족 되어야 합니다.
OPTIMIZER_MODE = ALL_ROWS or FIRST_ROWS or CHOOSE
QUERY_REWRITE_ENABLED = TRUE
COMPITABLE = 8.1.0 이상
사용 형식은 다음과 같습니다.
[형식]
create materialized view sum_sales
build immediate
refresh complete -- 만들어지자 마자 실행 가능한 상태로 원본테이블이 변경되면모두 갱신함, FAST는 갱신된 데이터만 반영, NEVER는 반영안함
enable query rewrite -- 오라클이 데이터 검색시 구체화뷰를 통해 검색하도록
as
select deptno,
sum(sal) sum_sales
from emp
group by deptno
만약 위 Query 실행 시 권한이 없다면서… 에러가 난다면 관리자로 로그인 하여 다음과 같이 실습 계정에
권한을 주시기 바랍니다.
grant create any materialized view to scott;
grant global query rewrite to scott;
grant query rewrite to scott;
grant alter any materialized view to scott;
MVIEW를 사용하지 않는 모양의 질의를 한번 볼까여…
위와
같이 한 후 다음과 같은 SQL문을 실행 합니다.
select deptno, sum(sal)
from emp
group by deptno
--------------------------------------------------------------------
Operation Object Name Rows Bytes
Cost
-----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS 3
4
HASH GROUP BY 3 15
4
TABLE ACCESS FULL SCOTT.EMP 15
75 3
우선 세션 레벨에서 query_rewrite_enabled를 true로 바꿉니다.
alter session set query_rewrite_enabled=true;
select
deptno, sum(sal)
from emp
group by deptno
----------------------------------------------------------------
Operation Object Name Rows Bytes
Cost
----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS 4
3
MAT_VIEW REWRITE ACCESS FULL
SCOTT.SUM_SALES 4 104 3
위 실행 계획을 보면 EMP 테이블을 이용하지 않고 SUM_SALES라는 MVIEW를 이용하여 실행 계획이 만들어 짐을 알 수
있습니다.
[실습]
- 실습을
위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
myemp1 : 1000만건
myemp1_old : 100만건
mydept : 5건
테스트환경 : oracle
11g
우선 구체화뷰를 만들지 않고 CBO, RBO 경우 아래의 쿼리를 날려보자.
select
e.deptno,
avg(sal) sal_sum
from
myemp1 e, mydept1 d
where
e.deptno = d.deptno
group by
e.deptno;
SQL>alter session set
optimizer_mode = choose --myemp1통계정보 있으니 CBO로 동작
SQL> select e.deptno,
2 avg(sal) sal_sum
3 from myemp1 e, mydept1 d
4 where e.deptno = d.deptno
5 group
by e.deptno;
DEPTNO SAL_SUM
----------
----------
0 999997.5
1 999998.5
2 999999.5
3 1000000.5
4 1000001.5
경 과: 00:00:07.93
---------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | | 1 | 41 | 17484 (4)| 00:03:30
|
| 1 |
SORT GROUP BY NOSORT | | 1 | 41 | 17484 (4)| 00:03:30
|
| 2 |
MERGE JOIN | | 10 | 410 | 17484 (4)| 00:03:30
|
| 3 |
SORT JOIN | | 5 | 140 | 17480 (4)| 00:03:30
|
| 4
| VIEW | VW_GBC_5 | 5 | 140 | 17480 (4)| 00:03:30
|
| 5
| HASH GROUP BY | | 5 | 35 | 17480 (4)| 00:03:30
|
| 6
| TABLE ACCESS FULL| MYEMP1 | 10M| 66M| 16961 (1)| 00:03:24
|
|* 7 |
SORT JOIN | | 10 | 130 | 4 (25)| 00:00:01
|
| 8
| TABLE ACCESS FULL | MYDEPT1 | 10 | 130 | 3 (0)| 00:00:01
|
---------------------------------------------------------------------------
8초 정도 걸렸다.
이번에는 RBO로 붜리를 실행해 보자. (2분 넘는다)
SQL> alter session set optimizer_mode =
rule
SQL> select e.deptno,
2 avg(sal) sal_sum
3 from myemp1 e, mydept1 d
4 where e.deptno = d.deptno
5 group by e.deptno;
DEPTNO SAL_SUM
----------
----------
0 999997.5
1 999998.5
2 999999.5
3 1000000.5
4 1000001.5
경 과: 00:02:17.53
-----------------------------------------------------------
| Id |
Operation | Name |
-----------------------------------------------------------
| 0 |
SELECT STATEMENT | |
| 1 |
SORT GROUP BY | |
| 2 |
NESTED LOOPS | |
| 3 |
NESTED LOOPS | |
| 4
| TABLE ACCESS FULL | MYDEPT1 |
|* 5
| INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO |
| 6 |
TABLE ACCESS BY INDEX ROWID| MYEMP1 |
-----------------------------------------------------------
지금 부터는 물리뷰를 만들어서
실습하자.
Materialized
views를 하나 만듭니다.
SQL> create materialized view
avg_myemp_sales
2 build
immediate
3 refresh
complete
4 enable query
rewrite
5 as
6 select
e.deptno,
7 avg(sal)
sal_sum
8 from myemp1 e, mydept1
d
9 where e.deptno =
d.deptno
10 group by
e.deptno;
구체화된 뷰가 생성되었습니다.
n 이번에는 구체화 뷰를 사용 안하고 질의해 보자. 엄청 시간 걸린다.(거의 3분)
n 뷰를
만들지 말고 먼저 질의 해 보라.
SQL>
SQL>-- 뷰를 사용하도록 힌트 구성(힌트를 안써도 구체화뷰를 통해 데이터를 가져 올 것이다.)
SQL> select
e.deptno,
2 avg(sal) sal_sum
3
from myemp1 e, mydept1 d
4
where e.deptno = d.deptno
5
group by e.deptno;
DEPTNO
SAL_SUM
---------- ----------
0
999997.5
1
999998.5
2
999999.5
3
1000000.5
4
1000001.5
경 과: 00:00:00.23 -- 바로 나온다
------------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 5 | 130 | 3 (0)| 00:00:01
|
| 1 | MAT_VIEW REWRITE ACCESS FULL|
AVG_MYEMP_SALES | 5 | 130 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
댓글 없음:
댓글 쓰기