物化视图和query_rewrite_enabled参数配合提高select查询性能

最近有一个需求，是使用物化视图来优化查询语句性能。因为不想大规模修改查询语句，所以想到使用query_rewrite_enabled参数，使查询语句能使用建好的物化视图。
在asktom（http://asktom.oracle.com）找到例子，测试过程如下：

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--

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22049049/viewspace-1030037/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22049049/viewspace-1030037/