ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10G开始Oracle区分物化视图和表

10G开始Oracle区分物化视图和表

原创 Linux操作系统 作者:yangtingkun 时间:2012-05-09 23:47:35 0 删除 编辑

9i以前,很多功能都是不区分表和物化视图的区别的,到了10g以后。很多功能会将表和物化视图区分对待。

 

 

原本通用的COMMENT ON TABLE语句,对物化视图不再有效,必须要使用COMMENT ON MATERIALIZED VIEW语句代替。

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 (ID NUMBER, TYPE VARCHAR2(30), NUM NUMBER);

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 'COMMENT ON A MATERIALIZED VIEW ';
COMMENT ON TABLE MV_BASE IS 'COMMENT ON A MATERIALIZED VIEW '
*
ERROR at line 1:
ORA-12098: cannot comment on the materialized view


SQL> COMMENT ON MATERIALIZED VIEW MV_BASE IS 'COMMENT ON A MATERIALIZED VIEW ';

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也对其进行细化,将物化视图的扫描和全表扫描区分开:

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
-----
   - 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 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

9i以前,很难从执行计划中区分扫描的是表还是物化视图,但是现在一目了然了。

总的来说,这种改进还是很有意义的,用户可以更清楚的了解处理的对象到底是表还是物化视图。

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10488742