12c 查询重写物化视图 - DBMS_ADVISOR.TUNE_MVIEW的使用

物化视图可以对一些运行复杂的sql语句起到“缓存”作用。物化视图将查询结果保存在一个基表中，当访问这个视图时可以快速的返回基表中的数据而不需要在原表中重新运算。

当我们创建了物化视图后一定希望每次访问这个sql语句时，数据库优化器都会智能的访问为其量身定做的物化视图。跟Oracle11g中的物化视图查询重写功能相比，似乎不必再
使用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/