oraclelike上强制使⽤索引
当where⼦句对某⼀列使⽤函数时，除⾮利⽤这个简单的技术强制索引，否则Oracle优化器不能在查询中使⽤索引。
通常情况下，如果在WHERE⼦句中不使⽤诸如UPPER、REPLACE 或SUBSTRD等函数，就不能对指定列建⽴特定的条件。但如果使⽤了这些函数，则会出现⼀个问题：这些函数会阻碍Oracle优化器对列使⽤索引，因⽽与采⽤索引的情况相⽐较，查询会花费更多的时间。
庆幸的是，如果在使⽤函数的这些列中包含了字符型数据，可以⽤这样⼀种⽅法修改查询语句，以达到强制性使⽤索引，更有效地运⾏查询。这篇⽂章介绍了涉及的技术，并说明了在两种典型情况下怎样实现。
⼤⼩写混合情况
在讨论由于函数修改了列的内容，如何强制使⽤索引前，让我们⾸先看看为什么Oracle优化器在这种情况下不能使⽤索引。假定我们要搜寻包含了⼤⼩写混合的数据，如在addressbook表的name列。因为数据是⽤户输⼊的，我们⽆法使⽤已经统⼀改为⼤写的数据。为了到每⼀个名为john的地址，我们使⽤包含了upper⼦句的查询语句。如下所⽰：
oracle游标的使用
使⽤这种查询语句（已设置AUTOTRACE），可得到下列结果：（以下是在PL/SQL Developer中查看执⾏计划的效果：）
可以看到，在这种情况下，Oracle优化器对addressbook表作了⼀次完整的扫描，⽽没有使⽤name列的索引。这是因为索引是根据列中数据的实际值建⽴的，⽽upper函数已经将字符转换成⼤写，即修改了这些值，因此该查询不能使⽤这列的索引。优化器不能与索引项⽐较”JOHN”，没有索引项对应于”JOHN”-只有”john” 。
值得庆幸的是，如果在这种情况下想要强制使⽤索引，有⼀种简便的⽅法：只要在where⼦句中增加⼀个或多个特定的条件，⽤于测试索引值，并减少需要扫描的⾏，但这并没有修改原来SQL 编码中的条件。以下列查询语句为例：
使⽤这种查询语句，可得到下列结果：
现在，优化器为WHERE ⼦句中AND 联结的两个语句中每⼀个语句确定的范围进⾏扫描—-第⼆个语句没有引⽤函数，因⽽使⽤了索引。在两个范围扫描后，将运⾏结果合并。(我个⼈感觉是：对于WHWERE⼦句中通过AND连接的各条件，如果某些条件可以使⽤索引，则oracle会优先使⽤这些条件，这样就可以快速定位符合条件的记录，然后在过滤之后的记录中再应⽤其他不能使⽤索引的条件。)
在这个例⼦中，如果数据库有成百上千⾏，可以⽤下列⽅法扩充WHERE ⼦句，进⼀步缩⼩扫描范围：
得到的结果与以前相同，但是，其执⾏过程如下所⽰，表明有4个扫描范围。
如果试图进⼀步提⾼查询速度，我们可以在特定的”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 函数去掉了连字符和空格，保证了电话号码是简单的数字串。然⽽，该函数阻⽌了优化器在该列使⽤索引。因此，我们按如下⽅法修改WHERE⼦句，以强制执⾏索引。
如果我们知道数据中可能包含圆括号，WHERE ⼦句会稍微复杂⼀点。我们可以再增加REPLACE 函数（去掉圆括号、连字符和空格），按如下所⽰扩充增加的条件：
该例强调了巧妙地选⽤WHERE ⼦句条件的重要性，⽽且，这些条件不会改变查询结果。你的选择应基于完全了解该列中存在的信息类型。在该例中，我们需要知道 PHONE_NUMBER 数据中存在⼏种不同的格式，这样，我们能够修改WHERE ⼦句⽽不会影响查询结果。
正确的条件
以后当你遇到包含CHARACTER 数据修改函数列的WHERE ⼦句时，应考虑怎样利⽤增加⼀个或两个特定的条件，迫使优化器使⽤索引。适当地选择⼀组特定的条件能减少扫描⾏，并且强制使⽤索引不会影响查询结果—-但却提⾼了查询的执⾏速度。
动⼿测试了⼀下并对原⽂例⼦进⾏了⼀点点修改。另外对于本⽂所述情况我更倾向于使⽤。
测试脚本：
from：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。