理解物化视图(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/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值