oracle非绑定变量忽略,避开绑定变量的弱点的一个经典解决方案——《Oracle高效设计》学习笔记...

假如我们在应用程序中对数据库进行查询时，经常会对某个字段使用Like语句，而数据库是对这个字段添加了索引的，如果查询的sql中like

的内容是以%开头的，那么索引很可能会被忽略，但当like的内容不是以%开头的，索引会被利用。假设这个查询会被经常使用，我们希望对

于这些查询使用绑定变量，这样可以减少硬解析的次数，但使用绑定变量的代价是，在查询时我们很可能使用错误的查询计划。

对于这个问题《Oracle高效设计》中给出了一个很经典的解决方案，使得我们在使用绑定变量同时，也能够使用正确的查询计划。

先做一个实验：

create table t

as

select a.* from all_objects a;

create index t_idx on t(object_name);

analyze table t compute statistics

for table for all indexes for all indexed columns;

variable x varchar2(50)

alter session set sql_trace=true;

set termout off

exec :x := '%';

select * from t x_was_percent where object_name like :x;

exec :x := 'Y%';

select * from t x_was_NOT_PCT where object_name like :x;

使用tkprof工具，查看跟踪结果：

BEGIN :x := '%'; END;

select *

from

t x_was_percent where object_name like :x

Misses in library cache during parse: 1Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5

Rows     Row Source Operation

-------  ---------------------------------------------------

49654  TABLE ACCESS FULL T (cr=3950 pr=0 pw=0 time=198752 us)

BEGIN :x := 'Y%'; END;

select *

from

t x_was_NOT_PCT where object_name like :x

Misses in library cache during parse: 1Optimizer mode: ALL_ROWS

Parsing user id: 5

Rows     Row Source Operation

-------  ---------------------------------------------------

1  TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=69 us)

1   INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=31 us)(object id 52917)

从测试结果可以看出，两个查询都使用了正确的查询计划。由于Oracle会在第一次执行绑定变量的sql时，

使用绑定变量窥视技术(根据绑定变量实际值确定查询计划)，又因为，我们对于两个查询做了点改动(注意黑体，

第一条sql的t被命名为x_was_percent ，第二条sql的t被命名为x_was_NOT_PCT )，

使得Oracle把这两个相同的查询做为不同查询来看待，每个查询都解析了一遍，这就使得两个查询都使用了正确的

查询计划，只要保证程序，在后续的类似查询凡是以%号开头的，都使用第一条sql，如果不以%开头的都使用第二

条sql，就可以保证虽然使用了绑定变量，但每次查询都使用了正确的查询计划。