理解物化视图（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），这个比值可见，物化视图提高了百倍的查询效率！