DNAME, sum(SAL) as tot_sum
from DEPT d, EMP e
where d.DEPTNO = e.DEPTNO
group by d.DNAME;
Chapter 17: Managing Distributed Databases 615
NOTE
A materialized view query cannot reference tables or views owned by
the user SYS.
The following example shows another example of a materialized view creation, using the
refresh fast on commit clause. To support fast refreshes when commits occur, you will need to
create a materialized view log on the base table. See ???Managing Materialized View Logs??? later
in this chapter for details.
create materialized view STORE_DEPT_SAL_MV
tablespace MYMVIEWS
parallel
build immediate
refresh fast on commit
enable query rewrite
as
select d.DNAME, sum(SAL) as tot_sum
from DEPT d, EMP e
where d.DEPTNO = e.DEPTNO
group by d.DNAME;
In this example, the same base query is used, but the materialized view is created with refresh
fast on commit so that a fast refresh occurs every time a transaction is committed in any of the
materialized view??™s base queries. This materialized view will be populated with data on creation,
and the inserted rows will be loaded in parallel. Query rewrite is enabled as well.
NOTE
The fast refresh option will not be used unless a materialized view
log is created on the base table for the materialized view. Oracle can
perform fast refreshes of joined tables in materialized views.
For both of these examples, the materialized view uses the default storage parameters for its
tablespace.
Pages:
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905