在索引编号列上使用'like'的Oracle查询性能较差

 

在查询1上，即使id是索引列，也正在执行全表扫描。 查询2达到相同的结果，但速度更快。 如果查询1运行返回一个索引列，那么它会很快返回，但如果返回非索引列或整个行则查询需要更长的时间。

在查询3中，它运行速度很快，但“代码”列是VARCHAR2（10）而不是NUMBER（12），其索引方式与'id'相同。

为什么Query 1没有提到它应该使用索引？ 是否应该更改某些内容以允许索引编号列更快地执行？

[查询1] 

select a1.*
from people a1
where a1.id like '119%' 
and rownum < 5

解释计划
SELECT语句ALL_ROWS
成本：67字节：2,592基数：4
2 COUNKE STOPKEY
1 TABLE ACCESS FULL TABLE people
费用：67字节：3,240基数：5

[查询2] 

select a1.*
from people a1, people a2
where a1.id = a2.id
and a2.id like '119%' 
and rownum < 5

解释计划
SELECT语句ALL_ROWS
费用：11字节：2,620基数：4
5 COUNKE STOPKEY
4 TABLE ACCESS BY INDEX ROWID TABLE people
成本：3字节：648基数：1
3窝窝
费用：11字节：2,620基数：4
1索引快速全面扫描索引people_IDX3
费用：2字节：54,796基数：7,828
2 INDEX RANGE SCAN INDEX people_IDX3
成本：2基数：1

[查询3] 

select a1.*
from people a1
where a1.code like '119%' 
and rownum < 5

解释计划
SELECT语句ALL_ROWS
费用：6字节：1,296基数：2
3 COUNKE STOPKEY
2 TABLE ACCESS BY INDEX ROWID TABLE people
费用：6字节：1,296基数：2
1 INDEX RANGE SCAN INDEX people_IDX4
成本：3基数：2

LIKE模式匹配条件期望将字符类型视为左侧和右侧操作数。 当它遇到一个NUMBER时，它会将其隐式转换为char。 您的查询1基本上默默地​​被重写为： 

SELECT a1.*
  FROM people a1
 WHERE TO_CHAR(a1.id) LIKE '119%'
   AND ROWNUM < 5

这发生在你的情况，这是不好的原因有两个：

  • 每行都执行转换，速度很慢;
  • 由于WHERE谓词中的函数（尽管是隐含的），因此Oracle无法使用A1.ID列上的索引。

    • 为了解决它，您需要执行以下任一操作：

  • 在A1.ID列创建一个基于函数的索引：

    CREATE INDEX people_idx5 ON people (TO_CHAR(id));

  • 如果您需要匹配ID列的前3个字符的记录，请创建另一个NUMBER类型的列，其中只包含这3个字符，并在其上使用plain =运算符。

  • 创建一个类型为VARCHAR2的单独列ID_CHAR ，并用TO_CHAR(id)填充它。 在你的WHERE条件中索引它并使用它来代替ID 。

    当然，如果您选择基于现有ID列创建额外的列，则需要保持这两个同步。您可以批量执行该操作，作为单个UPDATE或ON-UPDATE触发器，或将该列添加到适当的列INSERT和UPDATE语句在你的代码中。

    • LIKE是一个字符串函数，因此数字索引不能轻松使用。 在数字索引中，您将有119,120,130，..，1191,1192,1193 ...，11921,11922等等。即所有以'119'开头的行都不在同一个地方，所以整个索引必须被读取（因此FAST FULL SCAN）。 在基于字符的索引中，它们将会在一起（例如'119'，'1191'，'11911'，'120'，...），因此可以使用更好的RANGE SCAN。

    如果您正在查找特定范围内的id值（例如，119000到119999），则将其指定为谓词（id在119000和119999之间）。

    Optimizer认为执行表扫描会更快，这很可能是由于实际记录的数量较少。

    此外，你应该知道，非精确匹配总是比精确的方式更差。 如果你的位置是“a1.id ='123456'”，那么它很可能会使用索引。 但是，再次，即使索引需要两次读取（首先在索引中查找记录，然后从表中读取块），对于非常小的表，它可以决定进行表扫描。

    链接地址: http://www.djcxy.com/p/60719.html

    上一篇: Oracle query using 'like' on indexed number column, poor performance

    下一篇: 8 encoding issue