[bbk3201] 第64集 - Chapter 16-Using Materialized Views 00

Objectives

After completing this lesson,you should be able to do the following:

• Create materialized views
• Refresh materialized views
• Create nested materialized views
• Create UNION ALL materialized views
• Explain the use of query rewrites
• Enable and control query rewrites

Materialized Views

• Instantiations of a SQL query
• May be used for query rewrites
• Refresh types:
  • -Complete or Fast
  • -Force or Never
• Refresh modes:
  • -Manual
  • -Automated(synchronous or asynchronous)

Creating Materialized Views

DROP MATERIALIZED VIEW depart_sal_sum;

CREATE MATERIALIZED VIEW depart_sal_sum
AS
SELECT d.department_name,SUM(e.salary)
FROM hr.departments d,hr.employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_name;

SELECT * FROM depart_sal_sum;

 

Refreshing MVs

• The required parameters are:
• A list of materialized views to refresh
• The refresh method:F-Fast,?-Force,C-Complete
• Set push_deferred_rpc to True,if using updatable materialized views 
• Refresh after errors
  • -True:Allows the process to continue after an error

-False:Refresh will stop with errors(default value)

• Automatic refresh
  • -True:All refreshes are done in one transaction
  • -False:Each refresh is a separate transaction

DROP TABLE t;

CREATE TABLE t(key int primary key,val varchar2(5));
INSERT INTO t VALUES(1,'a');
INSERT INTO t VALUES(2,'b');
INSERT INTO t VALUES(3,'c');
INSERT INTO t VALUES(4,'d');
INSERT INTO t VALUES(5,'e');

COMMIT;

CREATE OR REPLACE VIEW v AS SELECT * FROM t;

SELECT rowid , a.* FROM v a;

DROP MATERIALIZED VIEW mv;

CREATE MATERIALIZED VIEW mv AS SELECT * FROM t;

SELECT rowid , b.* FROM mv b;

EXEC DBMS_MVIEW.REFRESH('MV','C');

MV:Manual Refreshing

• Refresh specific materialized views:

dbms_mview.refresh('CUST_SALES',parallelism => 10);--刷新materialized view，设置parallelism参数，增加处理的并行度. 

 

• Refresh materialized views based on one or more base tables:

dbms_mview.refresh_dependent('SALES');--将所有与基表SALES相关的materialized view全部进行刷新.

 

• Refresh all materialized views that are due to be refreshed:

dbms_mview.refresh_all_mviews;