Oracle Materialized Views Containing Joins Only-981428
介绍只包含表连接而没有聚合的物化视图。与聚合类似，表连接也是相当消耗资源的操作，将其结果预先计算并存储于物化视图中，可以提高SQL执行效率。
每一个基表(包括inline view)的Rowid必须出现在物化视图的select部分。这也是该物化视图可以快速刷新的必要条件之一。其他条件包括：
From 语句后的所有表必须建立MV log，且包含rowid
不可包含group by或其他聚合函数
Select语句中不能包含object类型的列
下面是一个例子：
CREATE MATERIALIZED VIEW LOG ON sales WITHROWID;
CREATE MATERIALIZED VIEW LOG ON times WITHROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT s.ROWID sales_rid, t.ROWIDtimes_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;
同样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.ROWIDtimes_rid, s.time_id,t.day_name, s.amount_sold, s.quantity_sold
FROM times t INNER JOIN sales s ONt.time_id = s.time_id(+)
Error at Command Line:20 Column:6
Error report:
SQL Error: ORA-12015: cannot create a fastrefresh materialized view from a complex query
12015. 00000 -"cannot create a fast refreshmaterialized 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 EditionRelease 11.2.0.1.0 - 64bit Production
PL/SQL> CORE11.2.0.1.0Production
TNS for Linux: Version 11.2.0.1.0 -Production
NLSRTL Version 11.2.0.1.0 - Production
Elapsed: 00:00:00.01
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
页:
[1]