materialized view(1)

最新推荐文章于 2024-01-29 13:53:03 发布

转载最新推荐文章于 2024-01-29 13:53:03 发布 · 3.4k 阅读

文章标签：

#oracle #文档 #table #insert

数据库专栏收录该内容

19 篇文章

订阅专栏

本文探讨了Oracle中物化视图无法快速刷新的两个特殊原因：一是通过同义词引用基表，二是从涉及多表连接且含有复杂表达式的查询创建物化视图。

这两天偶然发现两个导致物化视图无法快速刷新的原因刷新，这两个原因在Oracle的文档上并没有明确给出，在MV_CAPABILITIES_TABLE中给出的msgno在文档中也找不到对应的说明。
下面做两个简单的例子重新这两个错误：
SQL> CREATE TABLE TEST AS SELECT MOD(ROWNUM, 5) COL1, ROWNUM COL2 FROM ALL_OBJECTS;
表已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON TEST WITH ROWID, SEQUENCE (COL1, COL2) INCLUDING NEW VALUES;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
2 FROM TEST GROUP BY COL1;
实体化视图已创建。
上面建立的这个简单的物化视图显然是可以快速刷新的，下面做点变化，建立一个指向基表的同义词，然后建立物化视图的时候指向同义词。
SQL> CREATE SYNONYM C_TEST FOR TEST;
同义词已创建。
SQL> DROP MATERIALIZED VIEW MV_TEST;
实体化视图已删除。
SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
2 FROM C_TEST GROUP BY COL1;
FROM C_TEST GROUP BY COL1
*
ERROR 位于第 2 行:
ORA-12015: cannot create a fast refresh materialized view from a complex query
现在出现错误了，检查一下错误原因：
SQL> BEGIN
2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
3 FROM C_TEST GROUP BY COL1');
4 END;
5 /
PL/SQL 过程已成功完成。
SQL> COL MSGTXT FORMAT A80
SQL> SELECT MSGNO, MSGTXT FROM MV_CAPABILITIES_TABLE 
2 WHERE CAPABILITY_NAME = 'REFRESH_FAST_AFTER_INSERT';
MSGNO MSGTXT
---------- ---------------------------------------------------------------
2101 mv references a synonym in the FROM list
很显然是由于引用了同义词造成的，但是这个错误很奇怪，首先Oracle的文档里并没有明确说明不能引用同义词（也可能是我没有看到），而且Oracle的文档中也没有看到2101的错误。
对这个错误的解决方法就是不要使用同义词，直接使用基表，当然如果非要利用同义词来达到隐藏基表的目的，可以考虑使用视图代替，入下例，当然，视图和同义词还是有很多差别的，这里就不再讨论了。
SQL> CREATE VIEW V_TEST AS SELECT * FROM TEST;
视图已建立。
SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT COL1, SUM(COL2), COUNT(COL2), COUNT(*)
2 FROM V_TEST GROUP BY COL1;
实体化视图已创建。
下面看第二个问题：
SQL> CREATE TABLE TEST1 AS SELECT * FROM TEST;
表已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON TEST1 WITH ROWID, SEQUENCE (COL1, COL2) INCLUDING NEW VALUES;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_TEST_TEST1 REFRESH FAST AS
2 SELECT A.ROWID A_ROWID, B.ROWID B_ROWID, A.COL1, A.COL2||B.COL2 COL2 
3 FROM TEST A, TEST1 B
4 WHERE A.COL2 = B.COL2;
FROM TEST A, TEST1 B
*
ERROR 位于第 3 行:
ORA-12015: cannot create a fast refresh materialized view from a complex query
SQL> TRUNCATE TABLE MV_CAPABILITIES_TABLE;
表已截掉。
SQL> BEGIN
2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT A.COL1, A.COL2||B.COL2 COL2 FROM TEST A, TEST1 B
3 WHERE A.COL2 = B.COL2');
4 END;
5 /
PL/SQL 过程已成功完成。
SQL> SELECT MSGNO, MSGTXT FROM MV_CAPABILITIES_TABLE 
2 WHERE CAPABILITY_NAME = 'REFRESH_FAST_AFTER_INSERT';
MSGNO MSGTXT
---------- ---------------------------------------------------------------
2113 expression in select list references multiple tables or views
2061 one or more joins present in mv
这个解释描述的也比较清楚，一个表达式同时参考了多个表。对于同一个中的字段是可以进行‘||’操作的。
SQL> CREATE MATERIALIZED VIEW MV_TEST_TEST1 REFRESH FAST AS
2 SELECT A.ROWID A_ROWID, B.ROWID B_ROWID, A.COL1, 
3 A.COL2||A.COL1 COL2, B.COL1||B.COL2 COL3
4 FROM TEST A, TEST1 B
5 WHERE A.COL2 = B.COL2;
实体化视图已创建。
但是，同样Oracle在文档中并没有说明这一点，上面的两个MSGNO号码在文档中也是找不到对应关系的。怀疑Oracle的msgno是连续的或者基本上是连续的，也就是说，还有很多类似的原因会导致Oracle的物化视图无法刷新，只是不清楚Oracle为什么没有写在文档中。