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

环境：

sys@ORCL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> !uname -r
2.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），这个比值可见，物化视图提高了百倍的查询效率！

转载于:http://blog.itpub.net/26515977/viewspace-1207934/