物化视图测试

创建测试数据

create table all_obj as select OWNER, OBJECT_ID, OBJECT_NAME, CREATED from all_objects;

alter table all_obj add primary key (object_id);

SQL> create table owners (owner VARCHAR2(30), USER_ID NUMBER, CREATED DATE);

Table created.

SQL> insert into owners select * from all_users;

27 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from all_obj;

  COUNT(*)
----------
     40704

SQL> select count(*) from all_user;

  COUNT(*)
----------
     27

基本的语句：

CREATE MATERIALIZED VIEW XXXXXXX
PCTFREE 0 TABLESPACE XXXXX
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT ..............................

创建物化视图前，估算物化视图大小

set serverout on
declare
vrows number;
vbytes number;
begin
DBMS_MVIEW.ESTIMATE_MVIEW_SIZE (
   stmt_id       => 'est1',
   select_clause => 'SELECT o.owner , u.owner, count(*) FROM all_obj o, owners u WHERE o.owner = u.owner group by o.owner , u.owner order by count(*)',
   num_rows  =>   vrows,
   num_bytes =>  vbytes   );
dbms_output.put_line(vrows || ', ' || vbytes);
end;
/

输出：

12, 984

别名列表

如果select子句存在相同的字段名，如select a.name, b.name，就需要起别名a.name aname, b.name bname。但是这样的话，full text match query rewrite就会失败。因此，需要把别名卸载CREATE MATERIALIZED VIEW后面，避免在sql语句里写别名。

CREATE MATERIALIZED VIEW obj_mv ( obj_owner, username, obj_count)
ENABLE QUERY REWRITE AS
SELECT  o.owner , u.owner, count(*)
FROM all_obj o, owners u
WHERE o.owner = u.owner
group by o.owner , u.owner
order by count(*);

这个order by 只影响创建物化视图时数据的顺序，并不是物化视图的一部分，并且刷新过后顺序就变了。

测试query rewrite正常

SQL> set autotrace on
SQL> SELECT o.owner , u.owner, count(*)
FROM all_obj o, owners u
WHERE o.owner = u.owner
and   o.owner = 'SYS'
group by o.owner , u.owner
  6  order by count(*);

OWNER                          OWNER                          COUNT(*)
------------------------------ ------------------------------ ----------
SYS                            SYS                            17596


Execution Plan
----------------------------------------------------------
Plan hash value: 1909091144

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    47 |     4     (25)| 00:00:01 |
|   1 |  SORT ORDER BY                |        |     1 |    47 |     4     (25)| 00:00:01 |
|*  2 |    MAT_VIEW REWRITE ACCESS FULL| OBJ_MV |     1 |    47 |     3     (0)| 00:00:01  |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJ_MV"."OBJ_OWNER"='SYS')

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


Statistics
----------------------------------------------------------
     13  recursive calls
       0  db block gets
     80  consistent gets
       0  physical reads
       0  redo size
     531  bytes sent via SQL*Net to client
     385  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       3  sorts (memory)
       0  sorts (disk)
       1  rows processed

诊断QUERY REWRITE

查找无法query rewrite的原因

1 执行utlxrw.sql，创建REWRITE_TABLE

2 执行存储过程，提供一条SQL，看看为什么不能query rewrite

begin
DBMS_MVIEW.EXPLAIN_REWRITE (
    query           => 'SELECT o.owner , u.owner FROM all_obj o, owners u WHERE o.owner = u.owner ',
    mv              => 'scott.obj_mv',
    statement_id    => '');
end;

SQL> select MESSAGE from rewrite_table;

MESSAGE
------------------------------------------------------------------------------------------------------------------------

QSM-01150: query did not rewrite
QSM-01050:  roll-up check failed for materialized view, OBJ_MV

获取query rewrite相关建议

1 执行顾问程序

declare
v_task_name VARCHAR2(30);
begin
DBMS_ADVISOR.TUNE_MVIEW
(
     task_name => v_task_name,
     mv_create_stmt  => 'CREATE MATERIALIZED VIEW obj_mv (obj_owner, username, obj_count) ENABLE QUERY REWRITE AS SELECT o.owner , u.owner, count(*) FROM all_obj o, owners u WHERE o.owner = u.owner group by o.owner , u.owner order by count(*)'
);
end;
/

2 获取建议

下面是它推荐的创建物化视图日志和创建物化视图的语句。如果用户的物化视图没有发生query rewrite，就可以参考DBMS_MVIEW.EXPLAIN_REWRITE给出的原因和DBMS_ADVISOR.TUNE_MVIEW推荐的写法。

SQL> select SCRIPT_TYPE, substr(STATEMENT, 0, 500) str from USER_TUNE_MVIEW where TASK_NAME='advis1';

SCRIPT_TYPE    STR
-------------- ------------------------------------------------------------------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SCOTT"."OWNERS" WITH ROWID, SEQUENCE ("OWNER")     INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."OWNERS" ADD ROWID, SEQUENCE ("OWNER")  INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SCOTT"."ALL_OBJ" WITH ROWID, SEQUENCE ("OWNER")  INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."ALL_OBJ" ADD ROWID, SEQUENCE ("OWNER")  INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW SCOTT.OBJ_MV (obj_owner, username, obj_count)  REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS
            SELECT o.owner , u.owner, count(*) FROM all_obj o, owners u WHERE o.owner = u.owner group by o.owner , u.owner order by
            count(*)

UNDO            DROP MATERIALIZED VIEW SCOTT.OBJ_MV

6 rows selected.

query rewrite的限制

所有物化视图的限制：

1 sql不能包含不可重复的内容，如sysdate，rownum。

2 sql不能包含RAW，LANG RAW及对象型数据。

query rewrite的限制

1 当sql包含本地表和远程表时，只有本地表可以被rewrite。

2 物化视图和物化视图的表都不能属于SYS。

3 group by字句中的内容必须同样出现在select字句。

4 聚合函数只能出现在表达式或函数调用的最外层。类似AVG(AVG(x))和AVG(x)+AVG(x)都是不允许的。

5 sql中不能出现connect by。

刷新选项

刷新选项包含三类：

     如何刷新：ON DEMAND（默认），ON COMMIT。

     刷新类型：FORCE（默认）,COMPLETE，FAST，NEVER。

     使用受信任的约束条件：ENFORCE（默认），TRUSTED。

如果指定了REFRESH USING TRUSTED CONSTRAINTS，并且QUERY_REWRITE_INTEGRITY = TRUSTED，怎oracle在刷新物化视图的时候会信任未验证的依赖约束，如主外键。

查看物化视图的能力

1 执行脚本utlxmv.sql，创建MV_CAPABILITIES_TABLE表

2 执行存储过程

SQL> exec DBMS_MVIEW.EXPLAIN_MVIEW ('obj_mv', 'exp1');

PL/SQL procedure successfully completed.
2 查看结果
SQL> select STATEMENT_ID, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT from MV_CAPABILITIES_TABLE;

STATEMENT_ID                 CAPABILITY_NAME                P RELATED_TEXT                 MSGTXT
------------------------------ ------------------------------ - ------------------------------ ----------------------------------------------------------------------------------------------------
exp1                      PCT                     N
exp1                      REFRESH_COMPLETE            Y
exp1                       REFRESH_FAST                N
exp1                       REWRITE                     Y
exp1                      PCT_TABLE                N ALL_OBJ                  relation is not a partitioned table
exp1                      PCT_TABLE                N OWNERS                      relation is not a partitioned table
exp1                      REFRESH_FAST_AFTER_INSERT      N SCOTT.ALL_OBJ                 the detail table does not have a materialized view log
exp1                      REFRESH_FAST_AFTER_INSERT      N SCOTT.OWNERS                 the detail table does not have a materialized view log
exp1                      REFRESH_FAST_AFTER_ONETAB_DML  N                       see the reason why REFRESH_FAST_AFTER_INSERT is disabled
exp1                      REFRESH_FAST_AFTER_ANY_DML     N                       see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
exp1                      REFRESH_FAST_PCT            N                       PCT is not possible on any of the detail tables in the materialized view

STATEMENT_ID                 CAPABILITY_NAME                P RELATED_TEXT                 MSGTXT
------------------------------ ------------------------------ - ------------------------------ ----------------------------------------------------------------------------------------------------
exp1                      REWRITE_FULL_TEXT_MATCH           Y
exp1                      REWRITE_PARTIAL_TEXT_MATCH     Y
exp1                      REWRITE_GENERAL                Y
exp1                      REWRITE_PCT                N                       general rewrite is not possible or PCT is not possible on any of the detail tables
exp1                      PCT_TABLE_REWRITE           N ALL_OBJ                  relation is not a partitioned table
exp1                      PCT_TABLE_REWRITE           N OWNERS                      relation is not a partitioned table

17 rows selected.

快速刷新的限制

快速刷新物化视图的共性限制：

1 sql不能包含不可重复的内容，如sysdate，rownum。

2 sql不能包含RAW，LANG RAW及对象型数据。

3 在select子句中不能包含子查询。

4 在select子句中不能包含分析函数，如RANK。

5 不能包含MODEL子句。

6 不能包含HAVING子句。

7 不能包含ANY，ALL，NOT EXISTS。

8 ON COMMIT物化视图不能包含远程基表。

9 嵌套的物化视图必须包含join或者aggregate。

10 基表必须存放在一起。

只包含join的快速刷新物化视图的限制：

1 快速刷新物化视图的共性限制。

2 不能包含aggregates和group by。

3 from子句中所有表的rowid必须出现在select子句中。

4 from子句中所有表的rowid必须出现在各自的物化视图日志中。

包含聚合函数的快速刷新物化视图的限制：

1 快速刷新物化视图的共性限制。

2 所有基表必须包含物化视图日志，并且物化视图日志必须满足：

     必须包含物化视图中出现的所有列。

     包含with ROWID和INCLUDING NEW VALUES。

     如果基表将会发生复杂的增删改，则需要with SEQUENCE。

3 聚合函数只支持SUM，COUNT，AVG，STDDEV，VARIANCE，MIN，MAX。

4 select子句中必须包含count(*)。

5 对任何一个avg(xxx)，必须有相对应的count(xxx)，并且建议制定sum(xxx)。

6 如果VARIANCE(xxx)或STDDEV(xxx)出现，则count(xxx)和sum(xxx)也必须出现，建议还制定sum(xxx*xxx)。

7 select子句不能包含由多个表的字段组成的复合表达式。

8 select子句必须包含group by的所有列。

9 如果一个物化视图包含min(xxx),max(xxx)，或包含sum(xxx)但是没有count(xxx)，或者不包含count(*)，则快速刷新只支持最基本的插入操作。

  这种物化视图又叫insert-only物化视图。

包含union all的快速刷新物化视图的限制：

1 union的每个子查询必须满足快速刷新物化视图的共性限制。

2 union all操作必须发生在最外层。唯一的例外是，用union all创建一个试图，然后在物化视图里用select * from ()把该试图抱起来：

     CREATE VIEW view_with_unionall AS

(SELECT ....
UNION ALL
SELECT ....');
CREATE MATERIALIZED VIEW unionall_inside_view_mv
REFRESH FAST ON DEMAND AS
SELECT * FROM view_with_unionall;

3 union all物化视图不支持outer join, insert-only aggregate。

4 不支持Partiton Change Tracking (PCT)。

5 compatibility必须设置为9.2.0以上。

将上面的物化视图变成快速刷新

create materialized view log on all_obj WITH  SEQUENCE, ROWID (OWNER, OBJECT_ID, OBJECT_NAME, CREATED)  INCLUDING NEW VALUES;

create materialized view log on owners WITH  SEQUENCE, ROWID (OWNER, USER_ID, CREATED)  INCLUDING NEW VALUES;

由于物化视图日志晚于最后一次刷新，因此需要先执行一次完整刷新

SQL> exec dbms_mview.refresh( 'OBJ_MV', ' COMPLETE' );
PL/SQL procedure successfully completed.

然后就可以快速刷新了

SQL> exec dbms_mview.refresh( 'OBJ_MV', ' FAST' );
PL/SQL procedure successfully completed.

查看物化视图定义

set long 9999
set pages 9999
select substr(DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'OBJ_MV', 'SCOTT'), 0, 2000) mv_ddl from dual;

MV_DDL
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."OBJ_MV"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  ENABLE QUERY REWRITE
  AS SELECT o.owner , u.owner, count(*)
FROM all_obj o, owners u
WHERE o.owner = u.owner
group by o.owner , u.owner

测试快速刷新失败

drop materialized view obj_mv;
CREATE MATERIALIZED VIEW obj_mv (obj_owner, username, mx, countall)
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT o.owner , u.owner,  max(object_id), count(*)
FROM all_obj o, owners u
WHERE o.owner = u.owner
group by o.owner , u.owner;

由于包含max(object_id)，因此快速刷新只支持insert，如果有update就无法快速刷新了。

必须真的有数据修改后，执行快速刷新才会看出失败。没有数据改变的情况下，快速刷新永远会“成功”。

SQL> exec dbms_mview.refresh( 'OBJ_MV', 'FAST' );
BEGIN dbms_mview.refresh( 'OBJ_MV', 'FAST' ); END;

*
ERROR at line 1:
ORA-12057: materialized view "SCOTT"."OBJ_MV" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

但是如果只是插入新数据，就可以快速刷新：

SQL> select * from obj_mv where username = 'SCOTT';

OBJ_OWNER                 USERNAME                      MX   COUNTALL
------------------------------ ------------------------------ ---------- ----------
SCOTT                      SCOTT                       52549     16

SQL> insert into all_obj values('SCOTT',  90000, 'X', SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from obj_mv where username = 'SCOTT';

OBJ_OWNER                 USERNAME                      MX   COUNTALL
------------------------------ ------------------------------ ---------- ----------
SCOTT                      SCOTT                        90000     16

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

转载于:http://blog.itpub.net/26239116/viewspace-1075985/