当where子句对某一列使用函数时，除非利用这个简单的技术强制索引，否则oracle优化器不能在查询中使用索引。 通常情况下，如果在where子句中不使用诸如upper、replace 或substrd等函数，就不能对指定列建立特定的条件。但如果使用了这些函数，则会出现一个问题：这些函数会阻碍oracle优化器对列使用索引，因而与采用索引的情况相比较，查询会花费更多的时间。 庆幸的是，如果在使用函数的这些列中包含了字符型数据，可以用这样一种方法修改查询语句，以达到强制性使用索引，更有效地运行查询。这篇文章介绍了涉及的技术，并说明了在两种典型情况下怎样实现。
大小写混合情况 在讨论由于函数修改了列的内容，如何强制使用索引前，让我们首先看看为什么oracle优化器在这种情况下不能使用索引。假定我们要搜寻包含了大小写混合的数据，如在表1中address表的name列。因为数据是用户输入的，我们无法使用已经统一改为大写的数据。为了找到每一个名为john的地址，我们使用包含了upper子句的查询语句。如下所示：
| sql> select address from address where upper(name) like 'john'; |
在运行这个查询语句前，如果我们运行了命令"set autotrace on", 将会得到下列结果，其中包含了执行过程：
| address cleveland 1 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 1 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' ); |
得到的结果与以前相同，但是，其执行过程如下所示，表明有4个扫描范围。
| 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"条件中指明3个或更多的字符。然而，这样做会使得where子句十分笨重。因为需要大小写字符所有可能的组合-joh ,joh,joh,joh等等。除此之外，指定一个或两个字符已足以加快查询的运行速度了。 现在让我们看看，当我们引用不同的函数时，怎样运用这个基本技术。
使用replace的情况 正如名字不总是以大写输入一样，电话号码也会以许多格式出现： 如 123-456-7890， 123 456 7890，（123）456-7890 等等。 如果在列名为 phone_number中搜寻上述号码时，可能需要使用函数replace以保证统一的格式。如果在phone_number列中只包含空格、连字符和数字，where 子句可以如下所示：
| where replace(replace(phone_number , '-' ) , ' ' ) = '1234567890' |
where子句两次使用replace 函数去掉了连字符和空格，保证了电话号码是简单的数字串。然而，该函数阻止了优化器在该列使用索引。因此，我们按如下方法修改where子句，以强制执行索引。
| where replace(replace(phone_number, '-' ) , ' ' ) = '1234567890' |
and phone_number like '123% '如果我们知道数据中可能包含圆括号，where 子句会稍微复杂一点。我们可以再增加replace 函数（去掉圆括号、连字符和空格），按如下所示扩充增加的条件：
| where replace(replace(replace(replace(phone_number , ' - ' ) ,' '), '( ' ) , ' ) ' ) = '1234567890' and (phone number like ' 123% ' or phone_number like ' (123% ' ) ' |
该例强调了巧妙地选用where 子句条件的重要性，而且，这些条件不会改变查询结果。你的选择应基于完全了解该列中存在的信息类型。在该例中，我们需要知道 phone_number 数据中存在几种不同的格式，这样，我们能够修改where 子句而不会影响查询结果。
正确的条件 以后当你遇到包含character 数据修改函数列的where 子句时，应考虑怎样利用增加一个或两个特定的条件，迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行，并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！
点击进行留言回复
我要评论
网友评论