[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 |
----------------------------------------------------------------------------------------------
 | |||||
|---|---|---|---|---|---|
[기업100%환급]Spring ,MyBatis,Hibernate실무과정  | 총 | 5일 | 40시간 | 01-27 | |
[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지  | 총 | 5일 | 40시간 | 02-05 | |
 | |||||
| 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 | 총 | 24일 | 72시간 | 01-24 | |
| Spring3.X, MyBatis, Hibernate실무과정 | 총 | 12일 | 36시간 | 02-03 | |
 | |||||
| Spring3.X, MyBatis, Hibernate실무과정 | 총 | 5일 | 35시간 | 02-15 | |
| 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 | 총 | 10일 | 70시간 | 02-15 | |
| JAVA&WEB프레임워크실무과정 | 총 | 14일 | 98시간 | 03-08 | |
댓글 없음:
댓글 쓰기