最近有一個需求，是使用物化檢視來最佳化查詢語句效能。因為不想大規模修改查詢語句，所以想到使用query_rewrite_enabled引數，使查詢語句能使用建好的物化檢視。
在asktom（）找到例子，測試過程如下：

oracle@oracle[/home/oracle]> echo $ORACLE_SID
cnhtm
oracle@oracle[/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 28 09:52:44 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

為了測試方便，將dba角色給了scott使用者

[@more@]

sys@CNHTM> grant dba to scott;

Grant succeeded.

sys@CNHTM> conn scott/tiger
Connected.

建立測試表t，並執行表分析

scott@CNHTM> Create table t as
2 select substr(object_name,1,1) a , b, rpad('x',50,'x') data
3 from all_objects, (select 1 b from dual union all select 2 b from dual union all select 3 b
4 from dual );

Table created.

scott@CNHTM> analyze table t compute statistics;

Table analyzed.

檢視sql語句的執行計劃

scott@CNHTM> EXPLAIN PLAN FOR
2 select distinct a
3 from t
4 where b = 3;

Explained.

scott@CNHTM> select * from table(dbms_xplan.display) dual;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1793979440

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 147 | 297 (5)| 00:00:04 |
| 1 | HASH UNIQUE | | 49 | 147 | 297 (5)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T | 49785 | 145K| 291 (3)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("B"=3)

14 rows selected.

建立物化檢視，並分析物化檢視

scott@CNHTM> create materialized view mv_t
2 enable query rewrite
3 as select distinct a,b from t;

Materialized view created.

scott@CNHTM> analyze table mv_t compute statistics;

Table analyzed.

設定query_rewrite_enabled相關引數

scott@CNHTM> alter session set query_rewrite_enabled=true;

Session altered.

scott@CNHTM> alter session set query_rewrite_integrity=trusted;

Session altered.

再次執行sql語句，並檢視sql語句的執行計劃，注意紅色部分，證明已經對sql語句執行了rewrite。並且Cost大量降低

scott@CNHTM> EXPLAIN PLAN FOR
2 select distinct a
3 from t
4 where b = 3;

Explained.

scott@CNHTM> select * from table(dbms_xplan.display) dual;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3347475089

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 102 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 34 | 102 | 4 (25)| 00:00:01 |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_T | 49 | 147 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("MV_T"."B"=3)

14 rows selected.

--end--

物化檢視和query_rewrite_enabled引數配合提高select查詢效能

相關文章