校园网站素材,网站引导页分为三个板块设计风格,社区网站建设资金申请,wordpress自定义搜索当前页不少人大概和我一样在创建物化视图的时候会犯头痛#xff0c;怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后#xff0c;极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到…不少人大概和我一样在创建物化视图的时候会犯头痛怎样合理的改写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 tuneprocedure tune_mview (task_name in out varchar2,mv_create_stmt in clob);SQL set serveroutput on;SQL declare2 tn varchar2(200);3 begin4 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_484PL/SQL procedure successfully completed.SQL select script_type,statement2 from dba_tune_mview3 where task_name TASK_4844 order by action_id;SCRIPT_TYPE STATEMENTIMPLEMENTATION 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 FAST3 ON DEMAND ENABLE QUERY REWRITE AS 4 select t2,t3,count(*) from strc group by t2,t3 5 union all6 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 querySQL set serveroutput on;
SQL declare2 tn varchar2(200);3 begin4 DBMS_ADVISOR.TUNE_MVIEW(tn,5 mv_create_stmt CREATE MATERIALIZED VIEW MACLEAN.STRMTC6 USING INDEX REFRESH FAST7 ON DEMAND ENABLE QUERY REWRITE AS8 select t2,t3,count(*) from strc group by t2,t39 union all10 select t2,t3,count(*) from strd group by t2,t3);11 dbms_output.put_line(tn);12 end;13 /
TASK_547PL/SQL procedure successfully completed.SQL select statement2 from dba_tune_mview3 where task_name TASK_5474 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,t3union allselect 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.declarev_state varchar2(2000);
beginselect statementinto v_statefrom dba_tune_mviewwhere task_name TASK_547and 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 all3 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个物化视图的扫描 */ 转载于:https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2967682.html
