物化檢視可以對一些執行復雜的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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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方法強制使用查詢重寫特性。

12c 查詢重寫物化檢視 - DBMS_ADVISOR.TUNE_MVIEW的使用

相關文章