物化视图可以对一些运行复杂的sql语句起到“缓存”作用。物化视图将查询结果保存在一个基表中,当访问这个视图时可以快速的返回基表中的数据而不需要在原表中重新运算。
当我们创建了物化视图后一定希望每次访问这个sql语句时,数据库优化器都会智能的访问为其量身定做的物化视图。跟Oracle11g中的物化视图查询重写功能相比,似乎不必再
使用alter system set query_rewrite_enabled=true这个设置了。
使用alter system set query_rewrite_enabled=true这个设置了。
1. 首先看看我们的SQL语句
select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;
2. 登录pdb数据库查询步骤1中的语句
[oracle@snow ~]$ sqlplus / as sysdba
SYS@cdb > alter session set container=pdb1;
SYS@cdb > grant dba to hr;
SYS@cdb > conn hr/hr@pdb1
HR@pdb1 > select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;
DEPARTMENT_ID TOTAL_COMPENSATION
------------- ------------------
100 51608
30 24900
80 50
90 58000
20 19000
70 10000
110 20308
50 156400
80 377140
40 6500
60 28800
10 4400
3. 使用DBMS_ADVISOR.TUNE_MVIEW和DBMS_ADVISOR.CREATE_FILE将步骤一的sql语句创建成一个物化视图脚本。使用生成的脚本可以方便的创建出物化视图。
vi /home/oracle/scripts/tune_mv.sql
VARIABLE my_tune_mview_task VARCHAR2(30);
VARIABLE SQL VARCHAR2(4000);
--清理之前残留的任务
--EXECUTE DBMS_ADVISOR.DELETE_TASK('my_tune_mview_task');
--给变量赋值
EXECUTE :my_tune_mview_task := 'mview_task';
EXECUTE :SQL := 'CREATE MATERIALIZED VIEW MY_MV REFRESH FAST AS select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id';
--使用系统包分析:SQL中的创建语句
EXECUTE DBMS_ADVISOR.TUNE_MVIEW (:my_tune_mview_task,:sql);
--创建目录并授权,用来保存“分析诊断结果”
CREATE OR REPLACE DIRECTORY TUNE_RESULTS AS '/home/oracle' ;
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
--使用系统包创建诊断结果,/home/oracle/scripts/my_tune_mview_create.sql
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:my_tune_mview_task), -
'TUNE_RESULTS','my_tune_mview_create.sql');
4. 执行脚本tuen_mv.sql来完成对创建物化视图语句的分析,并给出建议的全部过程。
HR@pdb1 > @/home/oracle/scripts/tune_mv.sql
5. 经过诊断Oracle给出的建议脚本如下, 如果我们需要查询重写特性,直接修改
DISABLE
QUERY REWRITE为enable query rewrite。
[oracle@snow scripts]$ vi my_tune_mview_create.sql
Rem SQL Access Advisor: Version 12.1.0.1.0 - Production
Rem
Rem Username: HR
Rem Task: mview_task
Rem Execution date:
Rem
CREATE MATERIALIZED VIEW LOG ON
"HR"."EMPLOYEES"
WITH ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."EMPLOYEES"
ADD ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW HR.MY_MV
REFRESH FAST WITH ROWID
DISABLE
QUERY REWRITE
AS SELECT HR.EMPLOYEES.DEPARTMENT_ID C1, SUM(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M1, COUNT(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M2, COUNT(
*) M3 FROM HR.EMPLOYEES GROUP BY HR.EMPLOYEES.DEPARTMENT_ID;
修改后脚本如下
vi my_tune_mview_create.sql
CREATE MATERIALIZED VIEW LOG ON
"HR"."EMPLOYEES"
WITH ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."EMPLOYEES"
ADD ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW HR.MY_MV
REFRESH FAST WITH ROWID
ENABLE
QUERY REWRITE
AS SELECT HR.EMPLOYEES.DEPARTMENT_ID C1, SUM(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M1, COUNT(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M2, COUNT(
*) M3 FROM HR.EMPLOYEES GROUP BY HR.EMPLOYEES.DEPARTMENT_ID;
6. 执行该脚本创建物化视图,包括快速刷新物化视图日志,和可查询重写的物化视图。
HR@pdb1 > @/home/oracle/scripts/my_tune_mview_create.sql
7. 测试查询重写的效果,执行预期的sql语句,优化器判断可以走物化视图。
HR@pdb1 > set autot trace exp;
HR@pdb1 > select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 4040700093
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 84 | 3 (0)| 00:00:01 |
| 1 |
MAT_VIEW REWRITE ACCESS FULL
| MY_MV | 12 | 84 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
本篇完, 下一篇将介绍使用hint方法强制使用查询重写特性。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29047826/viewspace-1619414/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29047826/viewspace-1619414/