tbwshc

在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 (tbID 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以前，很難從執行計劃中區分掃描的是表還是物化視圖，但是現在一目了然了。

總的來說，這種改進還是很有意義的，用戶可以更清楚的了解處理的對象到底是表還是物化視圖。