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

作者: Maclean Liu, post on February 19th, 2011

不少人大概和我一样在创建物化视图的时候会犯头痛，怎样合理的改写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存储过程 可以将这种查询变形使得满足快速刷新的条件 */ — . . . → Read More: 利用DBMS_ADVISOR.TUNE_MVIEW包生成物化视图创建语句

Leave a comment   Oracle, Oracle物化视图materialized view   , ,  

How to Cancel a Refresh Operation of a Materialized View (MV)

作者: Maclean Liu, post on January 19th, 2010

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.5.0 – Release: 10.2 to 10.2 Information in this document applies to any platform. Checked for relevance on 19-SEP-2010

Goal

Refreshing a materialized view (MV) is controlled by either

a user of the RDBMS initiating a refresh a commit a job scheduled to run . . . → Read More: How to Cancel a Refresh Operation of a Materialized View (MV)

One comment   Oracle, Oracle物化视图materialized view   ,