materialized view(1)

1. 这两天偶然发现两个导致物化视图无法快速刷新的原因刷新，这两个原因在Oracle的文档上并没有明确给出，在MV_CAPABILITIES_TABLE中给出的msgno在文档中也找不到对应的说明。
6. 下面做两个简单的例子重新这两个错误：
8. SQL> CREATE TABLE TEST AS SELECT MOD(ROWNUM, 5) COL1, ROWNUM COL2 FROM ALL_OBJECTS;
10. 表已创建。
12. SQL> CREATE MATERIALIZED VIEW LOG ON TEST WITH ROWID, SEQUENCE (COL1, COL2) INCLUDING NEW VALUES;
14. 实体化视图日志已创建。
16. SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
17. 2 FROM TEST GROUP BY COL1;
19. 实体化视图已创建。
21. 上面建立的这个简单的物化视图显然是可以快速刷新的，下面做点变化，建立一个指向基表的同义词，然后建立物化视图的时候指向同义词。
23. SQL> CREATE SYNONYM C_TEST FOR TEST;
25. 同义词已创建。
27. SQL> DROP MATERIALIZED VIEW MV_TEST;
29. 实体化视图已删除。
31. SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
32. 2 FROM C_TEST GROUP BY COL1;
33. FROM C_TEST GROUP BY COL1
34. *
35. ERROR 位于第 2 行:
36. ORA-12015: cannot create a fast refresh materialized view from a complex query
38. 现在出现错误了，检查一下错误原因：
40. SQL> BEGIN
41. 2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
42. 3 FROM C_TEST GROUP BY COL1');
43. 4 END;
44. 5 /
46. PL/SQL 过程已成功完成。
48. SQL> COL MSGTXT FORMAT A80
49. SQL> SELECT MSGNO, MSGTXT FROM MV_CAPABILITIES_TABLE 
50. 2 WHERE CAPABILITY_NAME = 'REFRESH_FAST_AFTER_INSERT';
52. MSGNO MSGTXT
53. ---------- ---------------------------------------------------------------
54. 2101 mv references a synonym in the FROM list
56. 很显然是由于引用了同义词造成的，但是这个错误很奇怪，首先Oracle的文档里并没有明确说明不能引用同义词（也可能是我没有看到），而且Oracle的文档中也没有看到2101的错误。
58. 对这个错误的解决方法就是不要使用同义词，直接使用基表，当然如果非要利用同义词来达到隐藏基表的目的，可以考虑使用视图代替，入下例，当然，视图和同义词还是有很多差别的，这里就不再讨论了。
60. SQL> CREATE VIEW V_TEST AS SELECT * FROM TEST;
62. 视图已建立。
64. SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
65. 2 FROM V_TEST GROUP BY COL1;
67. 实体化视图已创建。
70. 下面看第二个问题：
72. SQL> CREATE TABLE TEST1 AS SELECT * FROM TEST;
74. 表已创建。
76. SQL> CREATE MATERIALIZED VIEW LOG ON TEST1 WITH ROWID, SEQUENCE (COL1, COL2) INCLUDING NEW VALUES;
78. 实体化视图日志已创建。
80. SQL> CREATE MATERIALIZED VIEW MV_TEST_TEST1 REFRESH FAST AS
81. 2 SELECT A.ROWID A_ROWID, B.ROWID B_ROWID, A.COL1, A.COL2||B.COL2 COL2 
82. 3 FROM TEST A, TEST1 B
83. 4 WHERE A.COL2 = B.COL2;
84. FROM TEST A, TEST1 B
85. *
86. ERROR 位于第 3 行:
87. ORA-12015: cannot create a fast refresh materialized view from a complex query
90. SQL> TRUNCATE TABLE MV_CAPABILITIES_TABLE;
92. 表已截掉。
94. SQL> BEGIN
95. 2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT A.COL1, A.COL2||B.COL2 COL2 FROM TEST A, TEST1 B
96. 3 WHERE A.COL2 = B.COL2');
97. 4 END;
98. 5 /
100. PL/SQL 过程已成功完成。
102. SQL> SELECT MSGNO, MSGTXT FROM MV_CAPABILITIES_TABLE 
103. 2 WHERE CAPABILITY_NAME = 'REFRESH_FAST_AFTER_INSERT';
105. MSGNO MSGTXT
106. ---------- ---------------------------------------------------------------
107. 2113 expression in select list references multiple tables or views
108. 2061 one or more joins present in mv
110. 这个解释描述的也比较清楚，一个表达式同时参考了多个表。对于同一个中的字段是可以进行‘||’操作的。
112. SQL> CREATE MATERIALIZED VIEW MV_TEST_TEST1 REFRESH FAST AS
113. 2 SELECT A.ROWID A_ROWID, B.ROWID B_ROWID, A.COL1, 
114. 3 A.COL2||A.COL1 COL2, B.COL1||B.COL2 COL3
115. 4 FROM TEST A, TEST1 B
116. 5 WHERE A.COL2 = B.COL2;
118. 实体化视图已创建。
121. 但是，同样Oracle在文档中并没有说明这一点，上面的两个MSGNO号码在文档中也是找不到对应关系的。怀疑Oracle的msgno是连续的或者基本上是连续的，也就是说，还有很多类似的原因会导致Oracle的物化视图无法刷新，只是不清楚Oracle为什么没有写在文档中。