5. 经过诊断Oracle给出的建议脚本如下, 如果我们需要查询重写特性,直接修改DISABLE QUERY REWRITE为enable query rewrite。
[oracle@snow scripts]$ vi my_tune_mview_create.sql
Rem SQL Access Advisor: Version 11.2.0.4.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;
7. 测试查询重写的效果,执行预期的sql语句,优化器判断可以走物化视图。
> set autot trace exp;
> select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;