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 /*+
rewrite(avg_myemp_sales)
*/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 |
----------------------------------------------------------------------------------------------
댓글 없음:
댓글 쓰기