理解物化视图（1）快速建立高效的物化视图的方法

来源：互联网发布：无敌数据恢复注册码编辑：程序博客网时间：2024/06/04 23:26

环境：

sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -r2.6.18-308.el5xen

conn / as sysdba

grant advisor to sh;

grant create materialized view to sh;

conn sh/sh

drop table t purge；

create table t as select * from dba_objects

--未建立物化视图前的select代价开销
sh@ORCL> select owner,object_type,count(*) from t group by owner,object_type;

236 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 47235625

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   483 | 7245 |   166   (6)| 00:00:02 |
|   1 | HASH GROUP BY         |      |   483 | 7245 |   166   (6)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T    | 50349 |   737K|   160   (2)| 00:00:02 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          9 recursive calls
          0 db block gets
        712 consistent gets
          0 physical reads
          0 redo size
       7036 bytes sent via SQL*Net to client
        550 bytes received via SQL*Net from client
         17 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
        236 rows processed

--为查询预建立物化视图
declare
v_task varchar2(2000);
begin
v_task :='Task1';
dbms_advisor.tune_mview(v_task,
     'create materialized view mv_t refresh fast with rowid as select owner,object_type,count(*)
                                                               from t
                                                               group by owner,object_type');
end;

--把user_tune_mview里面的advisor建议的语句拿出来执行，以下用其他颜色标识的

select to_char(statement) from user_tune_mview where task_name='Task1';

CREATE MATERIALIZED VIEW LOG ON "SH"."T" WITH ROWID, SEQUENCE ("OWNER","OBJECT_TYPE") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."T" ADD ROWID, SEQUENCE ("OWNER","OBJECT_TYPE") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW SH.MV_T   REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS select owner,object_type,count(*)
                                                               from t
                                                               group by owner,object_type

--建立了物化视图后select的花费
sh@ORCL> select owner,object_type,count(*) from t group by owner,object_type;

236 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360

-------------------------------------------------------------------------------------
| Id | Operation                                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |      |   236 | 9676 |     3   (0)| 00:00:01 |
|   1 | MAT_VIEW REWRITE ACCESS FULL| MV_T |   236 | 9676 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         19 consistent gets
          0 physical reads
          0 redo size
       7036 bytes sent via SQL*Net to client
        550 bytes received via SQL*Net from client
         17 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
        236 rows processed

（166+166+160）/ （3+3），这个比值可见，物化视图提高了百倍的查询效率！