改造包含MAX或MIN的物化视图

        物化视图包含MAX或MIN聚集函数，那么这个物化视图只支持INSERT语句的快速刷新。不过当物化视图没有限制条件时，这个物化视图也是可以支持UPDATE和DELETE语句的。

看一个具体的例子

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 AS
2 SELECT OBJECT_TYPE,
3 COUNT(*) COUNT,
4 MAX(OBJECT_ID) MAX_ID
5 FROM T
6 WHERE WNER = 'TEST'
7 GROUP BY OBJECT_TYPE;

Materialized view created.

SQL> SELECT * FROM MV_T;

OBJECT_TYPE COUNT MAX_ID
------------------- ---------- ----------
INDEX PARTITION 3 122825
TABLE PARTITION 9 122821
SEQUENCE 5 120409
QUEUE 4 120455
RULE 2 120470
SCHEDULE 1 118814
PROCEDURE 6 121813
DATABASE LINK 4
PACKAGE BODY 3 118919
PACKAGE 3 118918
PROGRAM 5 121801
LOB 10 134226
RULE SET 4 120457
TYPE BODY 1 80158
MATERIALIZED VIEW 4 134841
UNDEFINED 2 120468
TABLE 60 135006
FUNCTION 7 118917
INDEX 36 134836
VIEW 10 120458
SYNONYM 2 120472
TYPE 4 120402
EVALUATION CONTEXT 2 120411
JOB 5 121809
CLUSTER 3 117302

25 rows selected.

SQL> INSERT INTO T
2 (OWNER, OBJECT_TYPE, OBJECT_ID)
3 VALUES
4 ('TEST', 'TABLE', 5000000);

1 row created.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL procedure successfully completed.

SQL> SELECT * FROM MV_T;

OBJECT_TYPE COUNT MAX_ID
------------------- ---------- ----------
INDEX PARTITION 3 122825
TABLE PARTITION 9 122821
SEQUENCE 5 120409
QUEUE 4 120455
RULE 2 120470
SCHEDULE 1 118814
PROCEDURE 6 121813
DATABASE LINK 4
PACKAGE BODY 3 118919
PACKAGE 3 118918
PROGRAM 5 121801
LOB 10 134226
RULE SET 4 120457
TYPE BODY 1 80158
MATERIALIZED VIEW 4 134841
UNDEFINED 2 120468
TABLE 61 5000000
FUNCTION 7 118917
INDEX 36 134836
VIEW 10 120458
SYNONYM 2 120472
TYPE 4 120402
EVALUATION CONTEXT 2 120411
JOB 5 121809
CLUSTER 3 117302

25 rows selected.

SQL> UPDATE T SET OBJECT_ID = 1000000 WHERE OBJECT_ID = 5000000;

1 row updated.

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

*
ERROR at line 1:
ORA-32314: REFRESH FAST of "TEST"."MV_T" 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

由于指定了MAX聚集函数，这个物化视图只支持基表的INSERT语句的快速刷新，如果对基表执行了UPDATE和DELETE，则快速刷新报错。

不过如果包含MAX或MIN的物化视图不包含WHERE语句，则这个物化视图是支持基表的UPDATE和DELETE语句的。

因此，如果包含MAX或MIN的物化视图的WHERE条件不是很复杂，可以将其改造为可快速刷新的物化视图：

SQL> DROP MATERIALIZED VIEW MV_T;

Materialized view dropped.

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

Materialized view created.

SQL> SELECT *
2 FROM MV_T
3 WHERE WNER = 'TEST';

OWNER OBJECT_TYPE COUNT MAX_ID
------------------------------ ------------------- ---------- ----------
TEST PACKAGE BODY 3 118919
TEST PROCEDURE 6 121813
TEST SCHEDULE 1 118814
TEST FUNCTION 7 118917
TEST PROGRAM 5 121801
TEST JOB 5 121809
TEST SYNONYM 2 120472
TEST CLUSTER 3 117302
TEST UNDEFINED 2 120468
TEST TABLE PARTITION 9 122821
TEST SEQUENCE 5 120409
TEST QUEUE 4 120455
TEST TABLE 61 1000000
TEST TYPE 4 120402
TEST EVALUATION CONTEXT 2 120411
TEST MATERIALIZED VIEW 4 134841
TEST PACKAGE 3 118918
TEST VIEW 10 120458
TEST RULE SET 4 120457
TEST TYPE BODY 1 80158
TEST INDEX 36 134836
TEST LOB 10 134226
TEST INDEX PARTITION 3 122825
TEST RULE 2 120470
TEST DATABASE LINK 4

25 rows selected.

SQL> UPDATE T SET OBJECT_ID = 500000 WHERE OBJECT_ID = 1000000;

1 row updated.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM MV_T
3 WHERE WNER = 'TEST';

OWNER OBJECT_TYPE COUNT MAX_ID
------------------------------ ------------------- ---------- ----------
TEST PACKAGE BODY 3 118919
TEST PROCEDURE 6 121813
TEST SCHEDULE 1 118814
TEST FUNCTION 7 118917
TEST PROGRAM 5 121801
TEST JOB 5 121809
TEST SYNONYM 2 120472
TEST CLUSTER 3 117302
TEST UNDEFINED 2 120468
TEST TABLE PARTITION 9 122821
TEST SEQUENCE 5 120409
TEST QUEUE 4 120455
TEST TYPE 4 120402
TEST EVALUATION CONTEXT 2 120411
TEST MATERIALIZED VIEW 4 134841
TEST PACKAGE 3 118918
TEST VIEW 10 120458
TEST RULE SET 4 120457
TEST TYPE BODY 1 80158
TEST INDEX 36 134836
TEST LOB 10 134226
TEST INDEX PARTITION 3 122825
TEST RULE 2 120470
TEST DATABASE LINK 4
TEST TABLE 61 500000

25 rows selected.

将WHERE条件中指定的列也放到GROUP BY语句中，去掉WHERE查询条件，是的物化视图可以快速刷新。当需要查询时，对物化视图指定对应的限制条件既可。这种方法会占用更多的磁盘空间，而且物化视图的查询性能也会受到一定的影响，不过避免了物化视图不支持UPDATE和DELETE的限制。对于绝大部分情况，这种方式的代价还是很值得的。

 

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

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