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
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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;