sql – Oracle查询在索引号列上使用“like”,性能不佳

前端之家收集整理的这篇文章主要介绍了sql – Oracle查询在索引号列上使用“like”,性能不佳前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
查询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 STATEMENT ALL_ROWS
成本:67字节:2,592基数:4
2 COUNT STOPKEY
1表访问全表人
费用: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 STATEMENT ALL_ROWS
费用:11字节:2,620基数:4
5 COUNT STOPKEY
4表格索引索引ROWID TABLE people
费用:3字节数:648基数:1
3个NESTED LOOPS
费用: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 STATEMENT ALL_ROWS
费用:6字节:1,296基数:2
3 COUNT STOPKEY
2表格访问索引ROWID TABLE人
费用:6字节:1,296基数:2
1 INDEX RANGE SCAN INDEX people_IDX4
成本:3基数:2

解决方法

LIKE pattern-matching condition希望将字符类型视为左侧和右侧操作数.遇到NUMBER时,它会将其隐式转换为char.您的查询1基本上是无声改写为:
SELECT a1.*
  FROM people a1
 WHERE TO_CHAR(a1.id) LIKE '119%'
   AND ROWNUM < 5

这在你的情况下发生,这是不好的2个原因:

>对于每一行都执行转换,这是缓慢的;
>由于WHERE谓词中的函数(虽然是隐式的),Oracle无法使用A1.ID列上的索引.

解决它,您需要执行以下操作之一:

>在A1.ID列上创建一个function-based index

CREATE INDEX people_idx5 ON people(TO_CHAR(id));
>如果需要匹配ID列前3个字符的记录,请创建另一个类型为NUMBER的列,其中只包含这3个字符,并在其上使用一个plain = operator.
>创建一个类型为VARCHAR2的单独的列ID_CHAR,并将其填充TO_CHAR(id).在您的WHERE条件下,引用并使用而不是ID.

当然,如果您选择根据现有的ID列创建一个附加列,则需要保持这两个列的同步.您可以批量将其作为单个UPDATE或ON-UPDATE触发器,或将该列添加到适当的您的代码中的INSERT和UPDATE语句.

原文链接:https://www.f2er.com/mssql/74919.html

猜你在找的MsSQL相关文章