10G开始Oracle区分物化视图和表在9i以前，很多功能都是不区分表和物化视图的区别的，到了10g以后。很多功能会将表和物化视图区分对待。原本通用的COMMENTONTABLE语句，对物化视图不再有效，必须要使用COMMEN...SyntaxHighlighter.all();
在9i以前，很多功能都是不区分表和物化视图的区别的，到了10g以后。
很多功能会将表和物化视图区分对待。
原本通用的COMMENT ON TABLE语句，对物化视图不再有效，必须要使用
COMMENT ON MATERIALIZED VIEW语句代替。 www.2cto.com
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> CREATE TABLE T_BASE (tbID NUMBER, TYPE VARCHAR2(30), NUM NUMBER);
www.2cto.com
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON T_BASE
2 WITH ROWID, SEQUENCE (TYPE, NUM)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW MV_BASE
2 REFRESH FAST ENABLE QUERY REWRITE AS
3 SELECT TYPE, SUM(NUM) SUM_NUM, COUNT(NUM) CNT_NUM, COUNT(*) CNT
4 FROM T_BASE
5 GROUP BY TYPE;
Materialized view created.
SQL> COMMENT ON TABLE MV_BASE IS &＃39;COMMENT ON A MATERIALIZED VIEW &＃39;;
COMMENT ON TABLE MV_BASE IS &＃39;COMMENT ON A MATERIALIZED VIEW &＃39;
* www.2cto.com
ERROR at line 1:
ORA-12098: cannot comment on the materialized view
SQL> COMMENT ON MATERIALIZED VIEW MV_BASE IS &＃39;COMMENT ON A MATERIALIZED VIEW &＃39;;
Comment created.
SQL> COL COMMENTS FOR A60
SQL> SELECT * FROM USER_MVIEW_COMMENTS;
MVIEW_NAME COMMENTS
------------------------------ ------------------------------------------------------------
MV_BASE COMMENT ON A MATERIALIZED VIEW
其实不只是COMMENT发生了变化，关于物化视图的执行计划Oracle也对其进行细化，将物化视图的扫描和全表扫描区分开：
www.2cto.com
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM MV_BASE;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3034976462
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MAT_VIEW ACCESS FULL| MV_BASE | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Note www.2cto.com
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ REWRITE */ TYPE, COUNT(*) FROM T_BASE GROUP BY TYPE;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1008429399
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_BASE | 1 | 30 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------- www.2cto.com
Note
-----
- dynamic sampling used for this statement
在9i以前，很难从执行计划中区分扫描的是表还是物化视图，但是现在一目了然了。
总的来说，这种改进还是很有意义的，用户可以更清楚的了解处理的对象到底是表还是物化视图。
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有