Oracle Materialized Views Containing Joins Only

来源：互联网发布：python 单元测试框架编辑：程序博客网时间：2024/05/16 09:50

介绍

只包含表连接而没有聚合的物化视图。与聚合类似，表连接也是相当消耗资源的操作，将其结果预先计算并存储于物化视图中，可以提高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_mvBUILD IMMEDIATEREFRESH FAST ON DEMANDENABLE QUERY REWRITEASSELECT s.ROWID sales_rid, t.ROWID times_rid, s.time_id, t.day_name, s.amount_sold, s.quantity_soldFROM 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_mvBUILD IMMEDIATEREFRESH FAST ON DEMANDENABLE QUERY REWRITEASSELECT s.ROWID sales_rid, t.ROWID times_rid, s.time_id,t.day_name, s.amount_sold, s.quantity_soldFROM times t INNER JOIN sales s ON t.time_id = s.time_id(+)Error at Command Line:20 Column:6Error report:SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query12015. 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 ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE    11.2.0.1.0      ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production

Linux服务器信息：

SQL> !uname -aLinux 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

0 0