提高查询速度使用materizlized
2010年9月17日 星期五提高查询速度使用materizlized
第一步：授权给用户
SQL> grant create any materialized view to scott;
SQL> grant query rewrite to scott;
第二步：构建存储巨大数量的表并插入大量的数据
select count(*) from all_objects;
create table my_all_objects nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/
SQL> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects
3 /
SQL> commit;
SQL> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects
3 /
SQL> commit;
SQL> select count(*) from my_all_objects;
第三步 分析构建的大表
SQL> analyze table my_all_objects compute statistics;
第四步：查看构建大表所使用的时间已经读取次数
SQL> set autot on
SQL> set timing on
SQL> select owner,count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 10308
TSMSYS 24
DMSYS 2268
PUBLIC 239844
OUTLN 84
CTXSYS 4056
OLAPSYS 8616
HR 408
SYSTEM 5100
EXFSYS 3348
SCOTT 216
OWNER COUNT(*)
------------------------------ ----------
DBSNMP 552
ORDSYS 20028
ORDPLUGINS 120
SYSMAN 15492
OE 1344
PM 108
SH 3588
XDB 4164
IX 552
BI 96
SYS 273948
OWNER COUNT(*)
------------------------------ ----------
WMSYS 2784
SI_INFORMTN_SCHEMA 96
24 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 120 | 1922 (5)| 00:00:24 |
| 1 | HASH GROUP BY | | 24 | 120 | 1922 (5)| 00:00:24 |
| 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 597K| 2915K| 1848 (2)| 00:00:23 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8213 consistent gets
0 physical reads
0 redo size
953 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
第五步：开启query_rewrite的开关
SQL> alter session set query_rewrite_enabled=true;
SQL> alter session set query_rewrite_integrity=enforced;
第六步：创建materialized view
SQL> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from my_all_objects group by owner
7 /
第七步：分析物化视图
SQL> analyze table my_all_objects_aggs compute statistics;
第八步：查看构建好的大表，查询需要了多长的时间与逻辑读取次数以及访问的表
SQL> set autot on
SQL> set timing on
SQL> select owner,count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 10308
TSMSYS 24
DMSYS 2268
PUBLIC 239844
OUTLN 84
CTXSYS 4056
OLAPSYS 8616
HR 408
SYSTEM 5100
EXFSYS 3348
SCOTT 216
OWNER COUNT(*)
------------------------------ ----------
DBSNMP 552
ORDSYS 20028
ORDPLUGINS 120
SYSMAN 15492
OE 1344
PM 108
SH 3588
XDB 4164
IX 552
BI 96
SYS 273948
OWNER COUNT(*)
------------------------------ ----------
WMSYS 2784
SI_INFORMTN_SCHEMA 96
24 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2431068629
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 216 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MY_ALL_OBJECTS_AGGS | 24 | 216 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
133 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
953 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
24 rows processed
得出结论：没有物化视图前： 起初使用的时间是： Elapsed: 00:00:00.26 访问的表是：MY_ALL_OBJECTS 逻辑读取次数是： 8213 consistent gets
创建物化视图后： 后来使用的时间是： Elapsed: 00:00:00.06 访问的表是：MY_ALL_OBJECTS_AGGS 逻辑读取次数是： 18 consistent gets
第九步：通过插入大表数据，时间依然会大大缩短
SQL> insert into my_all_objects (owner,object_name,object_type,object_id) values ('New Owner','New Name','New Type',111111);
第九步：通过查询大表数据，时间依然会大大缩短
SQL> select owner,count(*) from my_all_objects where wner='New Owner' group by owner;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20976446/viewspace-674318/ ，如需转载，请注明出处，否则将追究法律责任。
