避开绑定变量的弱点的一个经典解决方案——《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: 1
Misses 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: 1
Optimizer 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，就可以保证虽然使用了绑定变量，但每次查询都使用了正确的查询计划。