Oracle Materialized Views Containing Joins Only

介绍

只包含表连接而没有聚合的物化视图。与聚合类似，表连接也是相当消耗资源的操作， 将其结果预先计算并存储于物化视图中，可以提高SQL执行效率。

 

每一个基表(包括inline view)的Rowid必须出现在物化视图的select部分。这也是该物化视图可以快速刷新的必要条件之一。其他条件包括：

• From 语句后的所有表必须建立MV log，且包含rowid
• 不可包含group by或其他聚合函数
• Select语句中不能包含object类型的列

 

一个例子：

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;

CREATE MATERIALIZED VIEW detail_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT s.ROWID sales_rid, t.ROWID times_rid, s.time_id, t.day_name, s.amount_sold, s.quantity_sold
FROM times t , sales s WHERE t.time_id = s.time_id;

ORA-12015

上面的DDL中的内连接，用inner join就会出现ORA-12015错误

Error starting at line 14 in command:

CREATE MATERIALIZED VIEW detail_sales_mv

BUILD IMMEDIATE

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT s.ROWID sales_rid, t.ROWID times_rid, s.time_id,t.day_name, s.amount_sold, s.quantity_sold

FROM times t INNER JOIN sales s ON t.time_id = s.time_id(+)

Error at Command Line:20 Column:6

Error report:

SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query

12015. 00000 -  "cannot create a fast refresh materialized view from a complex query"

*Cause:    Neither ROWIDs and nor primary key constraints are supported for

           complex queries.

*Action:   Reissue the command with the REFRESH FORCE or REFRESH COMPLETE

           option or create a simple materialized view.

数据库版本：

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Linux服务器信息：

SQL> !uname -a

Linux odilab 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux