弹冠相庆

October 10, 2010

创建基于复杂查询的快速刷新物化视图一例

Filed under: Uncategorized — Corey @ 22:24
Tags:

如果基于一些复杂查询直接建立快速刷新的物化视图,oracle会返回一个这样的错误
ORA-12015: cannot create a fast refresh materialized view from a complex query
比如下面这条查询

SELECT t2.c1
      ,t4.c1
      ,MAX(t3.c1)
FROM (SELECT MAX(t1.c1) c1
      FROM t1) t4
    ,t2
    ,t3
WHERE t3.c1 > t4.c1
      AND t2.c1 = t3.c2
GROUP BY t2.c1
        ,t4.c1

在此感谢yangtingkun,咨询后他给出了一个使用嵌套物化视图的解决方案,如下

SQL> CREATE TABLE T1(C1 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 NUMBER);

Table created.

SQL> CREATE TABLE T3(C1 NUMBER,C2 NUMBER);

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T1
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON T2
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON T3
  2  WITH ROWID, SEQUENCE (C1, C2)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T4
  2  REFRESH FAST AS
  3  SELECT COUNT(*) CN, COUNT(C1), MAX(T1.C1) C1
  4  FROM T1;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T4
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW  MV_T123
  2  REFRESH FAST AS
  3  SELECT T2.C1 T2_C1, MV_T4.C1 T4_C1, COUNT(*) CNT, COUNT(T3.C1), MAX(T3.C1)
  4  FROM MV_T4, T2, T3
  5  WHERE T3.C1 > MV_T4.C1
  6  AND T2.C1 = T3.C2
  7  GROUP BY T2.C1, MV_T4.C1;

Materialized view created.

快速刷新的物化视图创建成功。

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: