当WHERE子句对某一列使用函数时除非利用这个简单的技术强制索引否则Oracle优化器不能在查询中使用索引
通常情况下如果在WHERE子句中不使用诸如UPPERREPLACE 或SUBSTRD等函数就不能对指定列建立特定的条件但如果使用了这些函数则会出现一个问题这些函数会阻碍Oracle优化器对列使用索引因而与采用索引的情况相比较查询会花费更多的时间
庆幸的是如果在使用函数的这些列中包含了字符型数据可以用这样一种方法修改查询语句以达到强制性使用索引更有效地运行查询这篇文章介绍了涉及的技术并说明了在两种典型情况下怎样实现
大小写混合情况
在讨论由于函数修改了列的内容如何强制使用索引前让我们首先看看为什么Oracle优化器在这种情况下不能使用索引假定我们要搜寻包含了大小写混合的数据如ADDRESS表的NAME列因为数据是用户输入的我们无法使用已经统一改为大写的数据为了找到每一个名为john的地址我们使用包含了UPPER子句的查询语句如下所示
SQL> select address from address where upper(name) like JOHN;
在运行这个查询语句前如果我们运行了命令set autotrace on 将会得到下列结果其中包含了执行过程
ADDRESS
cleveland
row selected
Execution Plan
SELECT STATEMENT
TABLE ACCESS FULL ADDRESS
可以看到在这种情况下Oracle优化器对ADDRESS 表作了一次完整的扫描而没有使用NAME 列的索引这是因为索引是根据列中数据的实际值建立的而UPPER 函数已经将字符转换成大写即修改了这些值因此该查询不能使用这列的索引优化器不能与索引项比较JOHN没有索引项对应于JOHN只有john
值得庆幸的是如果在这种情况下想要强制使用索引有一种简便的方法只要在WHERE 子句中增加一个或多个特定的条件用于测试索引值并减少需要扫描的行但这并没有修改原来SQL 编码中的条件以下列查询语句为例
SQL> select address from address where upper(name) like JO% AND (name
like J% or name like j%);
使用这种查询语句（已设置AUTOTRACE）可得到下列结果
ADDRESS
cleveland
row selected
Execution Plan
SELECT STATEMENT
CONCATENATION
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
现在优化器为WHERE 子句中AND 联结的两个语句中每一个语句确定的范围进行扫描第二个语句没有引用函数因而使用了索引在两个范围扫描后将运行结果合并
在这个例子中如果数据库有成百上千行可以用下列方法扩充WHERE 子句进一步缩小扫描范围
select address from address where
upper(name) like JOHN AND (name like JO%
or name like jo% or name like Jo or name like jO );
得到的结果与以前相同但是其执行过程如下所示表明有个扫描范围
Execution Plan
SELECT STATEMENT
CONCATENATION
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
TABLE ACCESS BY INDEX ROWID ADDRESS
INDEX RANGE SCAN ADDRESS_I
如果试图进一步提高查询速度我们可以在特定的name like条件中指明个或更多的字符然而这样做会使得WHERE子句十分笨重因为需要大小写字符所有可能的组合joh JohjOhjoH等等除此之外指定一个或两个字符已足以加快查询的运行速度了
现在让我们看看当我们引用不同的函数时怎样运用这个基本技术
使用REPLACE的情况
正如名字不总是以大写输入一样电话号码也会以许多格式出现 如 （） 等等
如果在列名为 PHONE_NUMBER中搜寻上述号码时可能需要使用函数REPLACE以保证统一的格式如果在PHONE_NUMBER列中只包含空格连字符和数字where 子句可以如下所示
WHERE replace(replace(phone_number ) ) =
WHERE子句两次使用REPLACE 函数去掉了连字符和空格保证了电话号码是简单的数字串然而该函数阻止了优化器在该列使用索引因此我们按如下方法修改WHERE子句以强制执行索引
WHERE replace(replace(phone_number ) ) =
AND phone_number like % 如果我们知道数据中可能包含圆括号WHERE 子句会稍微复杂一点我们可以再增加REPLACE 函数（去掉圆括号连字符和空格）按如下所示扩充增加的条件
WHERE replace(replace(replace(replace
(phone_number ) )( )
) ) = AND
(phone number like %
or phone_number like (% )
该例强调了巧妙地选用WHERE 子句条件的重要性而且这些条件不会改变查询结果你的选择应基于完全了解该列中存在的信息类型在该例中我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式这样我们能够修改WHERE 子句而不会影响查询结果
正确的条件
以后当你遇到包含CHARACTER 数据修改函数列的WHERE 子句时应考虑怎样利用增加一个或两个特定的条件迫使优化器使用索引适当地选择一组特定的条件能减少扫描行并且强制使用索引不会影响查询结果但却提高了查询的执行速度
