query rewrite

不少人大概和我一样在创建物化视图的时候会犯头痛，怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后，极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到优化物化视图中的查询定义，修正物化视图日志的问题，此外它还能为原先不能refresh fast的物化视图提出建议以使得其可以快速刷新。


SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTS 
  2  USING INDEX REFRESH FAST ON DEMAND 
  3  ENABLE QUERY REWRITE 
  4  AS select distinct t1,t2 from MACLEAN.strb;
AS select distinct t1,t2 from MACLEAN.strb
                                      *
ERROR at line 4:
ORA-12015: cannot create a fast refresh materialized view from a complex query


/* 以select distinct查询语句为例，该语句本身不符合refresh fast的标准，但TUNE_MVIEW存储过程
    可以将这种查询变形使得满足快速刷新的条件 */

--    PROCEDURE DBMS_ADVISOR.TUNE_MVIEW
--    PURPOSE: Tune a Create Materialized View statement to
--    ADVISOR SUPPORT:        SQL Access Advisor
--    PARAMETERS:
--         TASK_NAME
--            The user can pass in a user-defined task name or
--            get a returned system-generated task name.
--         MV_CREATE_STMT
--            CREATE MATERIALIZED VIEW SQL statement to tune

procedure tune_mview (task_name      in out varchar2,
                      mv_create_stmt in     clob);

SQL> set serveroutput on;

SQL> declare
  2    tn varchar2(200);
  3  begin
  4    DBMS_ADVISOR.TUNE_MVIEW(tn,
  5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTS 
  6                     USING INDEX REFRESH FAST ON DEMAND 
  7                     ENABLE QUERY REWRITE 
  8                     AS select distinct t1,t2 from MACLEAN.strb');
  9    dbms_output.put_line(tn);
 10  end;
 11  /
TASK_484

PL/SQL procedure successfully completed.

SQL> select script_type,statement
  2    from dba_tune_mview
  3   where task_name = 'TASK_484'
  4   order by action_id;
 
SCRIPT_TYPE    STATEMENT

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRB" WITH ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRB" ADD ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW MACLEAN.STRMTS USING INDEX  REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2, COUNT(*) M1 FROM MACLEAN.STRB GROUP BY MACLEAN.STRB.T2, MACLEAN.STRB.T1
UNDO           DROP MATERIALIZED VIEW MACLEAN.STRMTS

/* 可以看到TUNE_MVIEW存储过程将原查询变形为SELECT...GROUP BY的形式 */

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRB" WITH ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES;
Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRB" ADD ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES;
Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTS USING INDEX  REFRESH FAST WITH ROWID 
ENABLE QUERY REWRITE AS SELECT MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2, COUNT(*) M1 
FROM MACLEAN.STRB GROUP BY MACLEAN.STRB.T2, MACLEAN.STRB.T1;

Materialized view created.

针对那些确实无法快速刷新的复杂查询，TUNE_MVIEW过程也可能给出将一个查询分解为多个物化视图达到快速刷新和查询重写的目的:



SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTD 
  2  USING INDEX REFRESH FAST
  3  ON DEMAND ENABLE QUERY REWRITE AS 
  4  select t2,t3,count(*) from strc group by t2,t3 
  5  union all
  6  select t2,t3,count(*) from strd group by t2,t3;
select t2,t3,count(*) from strd group by t2,t3
                           *
ERROR at line 6:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> set serveroutput on;
SQL> declare
  2    tn varchar2(200);
  3  begin
  4    DBMS_ADVISOR.TUNE_MVIEW(tn,
  5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTC
  6                                                   USING INDEX REFRESH FAST
  7                                                   ON DEMAND ENABLE QUERY REWRITE AS
  8                                                   select t2,t3,count(*) from strc group by t2,t3
  9                                                   union all
 10                                                  select t2,t3,count(*) from strd group by t2,t3');
 11    dbms_output.put_line(tn);
 12  end;
 13  /
TASK_547

PL/SQL procedure successfully completed.

SQL> select statement
  2    from dba_tune_mview
  3   where task_name = 'TASK_547'
  4   order by action_id;

CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB1
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB2
CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS  (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")
DROP MATERIALIZED VIEW MACLEAN.STRMTC
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('MACLEAN.STRMTC$RWEQ','select t2,t3,count(*) from strc group by t2,t3
                                                 union all
                                                select t2,t3,count(*) from strd group by t2,t3',' (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")',600916906)
 
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('MACLEAN.STRMTC$RWEQ')

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID 
ENABLE QUERY REWRITE AS  
(SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") 
UNION ALL  
(SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2");

Materialized view created.

declare
  v_state varchar2(2000);
begin
  select statement
    into v_state
    from dba_tune_mview
   where task_name = 'TASK_547'
     and action_id = 15;
  v_state := 'begin ' || v_state || '; end;';
  dbms_output.put_line(v_state);
  execute immediate v_state;
end;
PL/SQL procedure successfully completed.

SQL> set linesize 200 pagesize 1400;
SQL> select t2,t3,count(*) from strc group by t2,t3 
  2  union all
  3  select t2,t3,count(*) from strd group by t2,t3;
no rows selected

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL                    |             |       |       |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB1 |     1 |    37 |     2   (0)| 00:00:01 |
|   3 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB2 |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

/*　可以看到查询成功被rewrite为对2个物化视图的扫描 */

Materialized Views and Refresh Types

This practice will familiarize you with the various features and privileges to ensure successful creation of a materialized view from a base table.

1) Grant the necessary privileges for user Scott to create materialized views and allow query rewrite on the materialized views owned by schema Scott.

As user SYSTEM, execute the following command:> grant CREATE MATERIALIZED VIEW, QUERY REWRITE to scott;

2) As user Scott, create a materialized view name STAFF_MV_SIMPLE from the Employees table. You want the materialized view to only store data for the job of a STAFF, and you want a complete refresh. You need to first create the EMPLOYEES table by importing employees.dmp.

As user Scott, execute the following command:> CREATE MATERIALIZED VIEW staff_mv_simple
REFRESH COMPLETE
AS SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’;

3) Create a materialized view name STAFF_MV_REFRESH, still only storing data for the job of a STAFF, but you want a refresh feature that will only apply the changes made to the base table since the last time you refresh the materialized view. You will be creating a materialized view with a fast refresh.

As user Scott, execute the following command: > CREATE MATERIALIZED VIEW staff_mv_refresh
REFRESH FAST
AS SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’;

4) Create a materialized view name STAFF_MV_QR, still only storing data for the job of a STAFF, using 2 parallel processes, allowing query rewrite, and you
want a complete refresh.

As user Scott, execute the following command: > CREATE MATERIALIZED VIEW staff_mv_qr
PARALLEL (DEGREE 2)
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’;

Query Rewrites

This practice will familiarize you with the various features of creating a materialized view with query rewrite capabilty.

1) Alter your session to allow query rewrite.

As user Scott, execute the following command: > alter session set QUERY_REWRITE_ENABLED = true;

2) Use EXPLAIN PLAN to verify that rewrite has taken place. Confirm you have a Plan_Table. If you do not, please create it by running the utlxplan.sql file.
It should be located in the subdirectory where you installed Oracle.

For example, if Oracle 8.1.6 is install on c:oracle, then the file will be in the c:oracleora81rdbmsadmin

Create the Plan_table for schema Scott if it does not exist already.

As user Scott, execute the following command: > @c:oracleora81rdbmsadminutlxplan.sql

Confirm the plan_table exists.

As user Scott, execute the following command: > describe plan_table

3) Confirm materialized view STAFF_MV_QR will be use in a query rewrite request.

As user Scott, execute the following command: >delete from plan_table;

This is to ensure there are no row the the plan_table before populaing it with the explain plan results.

>explain plan for
> SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’

>col Operation format a30
col Options format a20
col Object format a20

>select lpad(‘ ‘, 2*LEVEL) || OPERATION ||
decode( ID, 0, ‘ Cost = ‘||POSITION) “Operation”,
OPTIONS “Options”, OBJECT_NAME “Object”
from PLAN_TABLE
connect by prior ID = PARENT_ID start with ID = 0
order by ID
/

Dimensions

This practice will familiarize you with the various features of creating a dimension, storing the hierachy definition in the database, and being familiar with the the data dictionary views that can be used to gather information regarding dimensions.

1) Confirm user Scott has the privilege to create a dimension. If not, grant that privilege to Scott.

As user System, execute the following command: > select grantee, privilege
from dba_sys_privs
where grantee = ‘SCOTT’;

If you don’t see user Scott has the CREATE DIMENSION privilege, grant it to user Scott.

> grant create dimension to scott;

2) As user Scott, create a dimension name mv_time_dim from the time table with a hierarchy name scott_calendar. Frist create the time table by exporting from
the file time.dmp.

As user Scott execute the following command: >CREATE DIMENSION mv_time_dim
LEVEL sdate IS time.sdate
LEVEL month IS time.month
LEVEL qtr IS time.quarter
LEVEL yr IS time.year
HIERARCHY scott_calendar
(sdate CHILD OF month CHILD OF qtr CHILD OF yr)
ATTRIBUTE month DETERMINES month_name;

3) Determine the levels of the dimersion you have created. To see that information, query the user_dim_levels view.
As user Scott execute the following command:

>select dimension_name, level_name, detailobj_name
from user_dim_levels;

Summary Management

1) After you have set up Oracle Trace Manager to monitor the utilization of your materialized views. you can determine if you should keep the materialized views you have created by querying the mview$_recommendations view.
As user Scott execute the following command:

>SELECT recommended_action, mview_name, group_by_columns, measures_list
FROM mview$_recommendations;

利用DBMS_ADVISOR.TUNE_MVIEW包生成物化视图创建语句

Materialized Views and Dimensions