改造DISTINCT物化视图

改造DISTINCT物化视图

包含DISTINCT的物化视图无法快速刷新，但是有些情况可以转化为可快速刷新的物化视图。
一.SELECT语句中的DISTINCT操作。
其实这种情况最简单，因为如果只是包含一个DISTINCT操作，那么完全可以转换为GROUP BY语句：
SQL> CREATE TABLE T AS
2 SELECT *
3 FROM DBA_OBJECTS;

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T
2 WITH ROWID, SEQUENCE (OWNER, OBJECT_TYPE)
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT DISTINCT OWNER, OBJECT_TYPE
3 FROM T;

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

SQL> CREATE MATERIALIZED VIEW MV_T_COMPLETE
2 AS SELECT DISTINCT OWNER, OBJECT_TYPE
3 FROM T;

Materialized view created.

显然包含DISTINCT的物化视图只能完全刷新而不能快速刷新，不过这种物化视图可以很轻松的改成下面的写法：
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT OWNER, OBJECT_TYPE, COUNT(*) COUNT
3 FROM T
4 GROUP BY OWNER, OBJECT_TYPE;

Materialized view created.

物化视图使用GROUP BY的写法和使用DISTINCT的写法是等价的，而且可以使用快速刷新。唯一不同是多了一个COUNT(*)的字段，而一般情况下，物化视图的额外列没有多大影响。如果基表不包括UPDATE和DELETE，只有INSERT操作，那么物化视图中可以去掉COUNT(*)列。

SQL> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST
2 AS SELECT OWNER, OBJECT_TYPE
3 FROM T
4 GROUP BY OWNER, OBJECT_TYPE;

Materialized view created.

SQL> DELETE T WHERE ROWNUM = 1;

1 row deleted.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1')
BEGIN DBMS_MVIEW.REFRESH('MV_T1'); END;

*
ERROR at line 1:
ORA-32314: REFRESH FAST of "TEST"."MV_T1" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 1

可以看到如果不包括COUNT(*)，则物化视图不支持UPDATE和DELETE语句的快速刷新。

二.COUNT或SUM聚集函数中的DISTINCT操作。

如果物化视图包括COUNT(DISTINCT COLUMN)、SUM(DISTINCT COLUMN)或者AVG(DISTINCT COLUMN)，则物化视图也是无法快速刷新的：

SQL> CREATE TABLE T AS
2 SELECT *
3 FROM DBA_OBJECTS;

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T
2 WITH ROWID, SEQUENCE (OWNER, OBJECT_TYPE, OBJECT_ID)
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT OWNER,
3 OBJECT_TYPE,
4 COUNT(*) COUNT,
5 COUNT(OBJECT_ID) COUNT_ID,
6 SUM(DISTINCT OBJECT_ID) SUM_ID
7 FROM T
8 GROUP BY OWNER, OBJECT_TYPE;

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

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT OWNER,
3 OBJECT_TYPE,
4 COUNT(*) COUNT,
5 COUNT(OBJECT_ID) COUNT_ID,
6 SUM(OBJECT_ID) SUM_ID
7 FROM T
8 GROUP BY OWNER, OBJECT_TYPE;

Materialized view created.

对于这种情况，可以将一个物化视图转化为两个物化视图：

SQL> CREATE MATERIALIZED VIEW MV_T_INTER REFRESH FAST AS
2 SELECT OWNER,
3 OBJECT_TYPE,
4 OBJECT_ID,
5 COUNT(*) COUNT
6 FROM T
7 GROUP BY OWNER, OBJECT_TYPE, OBJECT_ID;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T_INTER
2 WITH ROWID, SEQUENCE (OWNER, OBJECT_TYPE, OBJECT_ID)
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST AS
2 SELECT OWNER,
3 OBJECT_TYPE,
4 COUNT(*) COUNT,
5 COUNT(OBJECT_ID) COUNT_ID,
6 SUM(OBJECT_ID) SUM_ID
7 FROM MV_T_INTER
8 GROUP BY OWNER, OBJECT_TYPE;

Materialized view created.

第二个物化视图和前面创建失败的物化视图是等价的。同样AVG(DISTINCT COLUMN)也可以在这个物化视图中获得。

三.看一个稍微复杂一点的例子：

SQL> CREATE TABLE T AS
2 SELECT *
3 FROM DBA_TABLES;

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T
2 WITH ROWID, SEQUENCE (OWNER, TABLESPACE_NAME, STATUS)
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE TABLE T1 AS
2 SELECT *
3 FROM DBA_INDEXES;

Table created.

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

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
2 SELECT DISTINCT OWNER,
3 TABLESPACE_NAME,
4 DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS
5 FROM T
6 UNION ALL
7 SELECT DISTINCT OWNER,
8 TABLESPACE_NAME,
9 DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS
10 FROM T1;
FROM T1
*
ERROR at line 10:
ORA-12015: cannot create a fast refresh materialized view from a complex query

由于包含了DISTINCT，使包含UNION ALL的物化视图无法快速刷新。这时利用上面描述的方法，就可以实现UNION ALL视图的快速刷新：

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
2 SELECT OWNER,
3 TABLESPACE_NAME,
4 DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
5 COUNT(*) COUNT,
6 1 FLAG
7 FROM T
8 GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID')
9 UNION ALL
10 SELECT OWNER,
11 TABLESPACE_NAME,
12 DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
13 COUNT(*) COUNT,
14 2 FLAG
15 FROM T1
16 GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID');

Materialized view created.

除了这个方法，也可以建立嵌套的物化视图——先建立两个单表聚集的物化视图，在这两个物化视图上再建立一个UNION ALL的物化视图：

SQL> CREATE MATERIALIZED VIEW MV_T1_AGG REFRESH FAST AS
2 SELECT OWNER,
3 TABLESPACE_NAME,
4 DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
5 COUNT(*) COUNT
6 FROM T
7 GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID');

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MV_T2_AGG REFRESH FAST AS
2 SELECT OWNER,
3 TABLESPACE_NAME,
4 DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
5 COUNT(*) COUNT
6 FROM T1
7 GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID');

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T1_AGG
2 WITH ROWID;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T2_AGG
2 WITH ROWID;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T_UNIONALL REFRESH FAST AS
2 SELECT ROWID R_ID,
3 OWNER,
4 TABLESPACE_NAME,
5 STATUS,
6 1 FLAG
7 FROM MV_T1_AGG
8 UNION ALL
9 SELECT ROWID R_ID,
10 OWNER,
11 TABLESPACE_NAME,
12 STATUS,
13 2 FLAG
14 FROM MV_T2_AGG;

Materialized view created.

需要注意，对于嵌套物化视图而言，先建聚集物化视图后建UNION ALL物化视图和先建UNION ALL物化视图后建聚集物化视图，都是可以支持快速刷新的。但是这两种方式返回的最终结果是有区别的。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14270146/viewspace-676574/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14270146/viewspace-676574/