query rewrite的理解

Query Rewrite 在数据仓库是是一个非常有用的技术, Tom在<<Effective Oracle by Design>>一书中将实体化视图(MView)称为是数据仓库的索引, 这是再贴切不过的了, 在OLTP中当SELECT语句的所有的字段都在索引中时, Oracle可以不从表读数据, 而直接从索引中获得全部信息, 而Query Rewrite则是通过创建中间表, 让Oracle自动从创建的中间表读取数据, 而不需要从原表读取了, 这个中间表可以是预先join好的或预先计算好的中间结果. 他的使用就和一般的索引同理了, 虽然你指定的还是那个大表, 但oracle可以为你自动识别可以从那个"数据仓库索引"中读取数据. 下面我们可以来看一下最简单的例子:

SQL> CREATE TABLE DETAIL_TABLE?????????????????????????????????????
? 2? AS SELECT OWNER,OBJECT_NAME FROM sys.dba_objects;?

Table created.

SQL> DESC DETAIL_TABLE
?Name?????????????????????????????? Null???? Type?
?———————————- —— ————
?OWNER?????????????????????????????????????? VARCHAR2(30)
?OBJECT_NAME???????????????????????????????? VARCHAR2(128)

SQL> CREATE MATERIALIZED VIEW MID_TABLE ENABLE QUERY REWRITE
? 2? AS
? 3? SELECT OWNER,COUNT(*) TABCNT FROM DETAIL_TABLE GROUP BY OWNER;

Materialized view created.

SQL> DESC MID_TABLE
?Name????????????????????????????? Null???? Type
?——————————— —— ————
?OWNER????????????????????????????????????? VARCHAR2(30)
?TABCNT???????????????????????????????????? NUMBER

?

?? 现在假设DETAIL_TABLE是一个非常大的表, MID_TABLE是预先计算好的中间表, 我们来做一个查询测试一下, 要让Oracle使用Query Rewrite,首先要使用CBO,我已经对这个两个表作了分析, 然后将query_rewrite_enabled参数设为True,这个参数可以在session一级更改. 如:

SQL> show parameter query_rewrite_enabled

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ——— ———
query_rewrite_enabled??????????????? string????? FALSE

SQL> SELECT? OWNER,COUNT(*) FROM DETAIL_TABLE
? 2?? WHERE OWNER=’SYSTEM’ GROUP BY OWNER;

OWNER??????????????????????????? COUNT(*)
—————————— ———-
SYSTEM??????????????????????????????? 406

Execution Plan
———————————————————-
?? 0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=4 …)
?? 1??? 0?? SORT (GROUP BY NOSORT) (Cost=4 …)
?? 2??? 1???? TABLE ACCESS (FULL) OF ‘DETAIL_TABLE’ (Cost=4 …

SQL> ALTER SESSION SET query_rewrite_enabled=TRUE;

Session altered.

SQL> SELECT? OWNER,COUNT(*) FROM DETAIL_TABLE
? 2? WHERE OWNER=’SYSTEM’ GROUP BY OWNER;

OWNER??????????????????????????? COUNT(*)
—————————— ———-
SYSTEM??????????????????????????????? 406

Execution Plan
———————————————————-
?? 0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8)
?? 1??? 0?? TABLE ACCESS (FULL) OF ‘MID_TABLE’ (Cost=2 Card=1 Bytes=8)

??? 大家可以看一下上面两个同样的语句在query rewrite时启用和禁用时的plan的区别, 可以看到MID_TABLE的作用就象是一个索引. 除了Query_rewrite_enabled参数外, 还有一个参数query_rewrite_integrity会影响Query Rewrite的使用.