오라클 mv
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
오라클 M/V 생성[편집]
-- 테이블 생성
create table t (x not null primary key, y not null) as
select rownum x, mod(rownum, 10) y from dual connect by level <= 1000;
-- M/V 생성
create materialized view log on t with rowid (x, y) including new values;
create materialized view mv
refresh fast on demand
enable on query computation
enable query rewrite
as
select y , count(*) c1
from t
group by y;
-- 테이블 입력
insert into t
select 1000+rownum, 1 from dual connect by level <= 100;
commit;
-- M/V rewrite
select /*+ rewrite */y , count(*) from t
group by y;
-- M/V reflash 수동
select /*+ fresh_mv */* from mv;
-- M/V 실시간 반영
alter materialized view mv enable on query computation;