使用ORACLE高级查询重写

在无法修改sql的情况下，有以下几种方法可以调整sql:
sql outline,
sql profile,
advanced rewrite

这里主要说明高级查询重写的功能。
首先建立测试表格和数据：
create table t1(c1, c2, c3, c4)
as
select to_char(level), to_char(level), to_char(level), to_char(level)
from dual
connect by level <= 1000000
;

create index t1_n1 on t1(c1); -- c1 has index;

这时查看下列语句的执行计划，可以发现是走了全表扫描，
fy@ORCL> set autot traceonly explain
fy@ORCL> select * from t1 where c1 like '%11%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 1318K| 1257 (2)| 00:00:16 |
|* 1 | TABLE ACCESS FULL| T1 | 50000 | 1318K| 1257 (2)| 00:00:16 |
--------------------------------------------------------------------------

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

1 - filter("C1" LIKE '%11%')

将查询重写为：

fy@ORCL> select /*+ leading(x) use_nl(x t1) */ t1.* from (select /*+ index_ffs(t
1) */ rowid as row_id, c1 from t1 where c1 like '%11%') x,t1 where t1.rowid = x.
row_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2525156207

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 2246K| 50704 (1)| 00:10:09 |
| 1 | NESTED LOOPS | | 50000 | 2246K| 50704 (1)| 00:10:09 |
|* 2 | INDEX FAST FULL SCAN | T1_N1 | 50000 | 927K| 688 (2)| 00:00:09 |
| 3 | TABLE ACCESS BY USER ROWID| T1 | 1 | 27 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - filter("C1" LIKE '%11%')

这时可以看出，这个查询走了索引，虽然效果并不好。

赋予相应的权限和修改会话属性：

grant execute on dbms_advanced_rewrite to fy;
grant create materialized view to fy;
alter session set query_rewrite_integrity = trusted;

建立高级查询重写：
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
name => 'rewrite2',
source_stmt =>
'select * from t1 where c1 like ''%11%''',
destination_stmt =>
'select /*+ leading(x) use_nl(x t1) */ t1.* from (select /*+ index_ffs(t1) */ rowid as row_id, c1 from t1 where c1 like ''%11%'') x,t1 where t1.rowid = x.row_id',
validate => false,
rewrite_mode => 'text_match');
end;
/

然后再执行第一条语句：
fy@ORCL> select * from t1 where c1 like '%11%';

Execution Plan
----------------------------------------------------------
Plan hash value: 2525156207

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 1660K| 50704 (1)| 00:10:09 |
| 1 | NESTED LOOPS | | 50000 | 1660K| 50704 (1)| 00:10:09 |
|* 2 | INDEX FAST FULL SCAN | T1_N1 | 50000 | 341K| 688 (2)| 00:00:09 |
| 3 | TABLE ACCESS BY USER ROWID| T1 | 1 | 27 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - filter("C1" LIKE '%11%')

最后，删除查询重写：
begin
sys.dbms_advanced_rewrite.drop_rewrite_equivalence( name=> 'rewrite2');
end;
/

不过我本来是想将查询重写成全文索引的，但是如果重写成全文索引，就会报错：
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5280
Session ID: 170 Serial number: 8


不知道是为什么，准备问问OTN上的高人。