使用LIKE'string'vs ='string'对Oracle的性能有何影响? [英] Any performance impact in Oracle for using LIKE 'string' vs = 'string'?

问题描述

此

SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE '%some_value%';

比这慢

SELECT * FROM SOME_TABLE WHERE SOME_FIELD = 'some_value';

那又如何呢?

SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE 'some_value';

我的测试表明第二个和第三个示例完全相同.如果是这样，我的问题是，为什么要使用"="?

My testing indicates the second and third examples are exactly the same. If that's true, my question is, why ever use "=" ?

推荐答案

使用绑定变量时有明显的区别，您应该在Oracle中将其用于数据仓库或其他批量数据操作以外的其他任何事情.

There is a clear difference when you use bind variables, which you should be using in Oracle for anything other than data warehousing or other bulk data operations.

以以下情况为例:

SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE :b1

Oracle在执行之前无法知道:b1的值为'％some_value％'或'some_value'等，因此它将根据启发式方法估计结果的基数，并提出适当的方法计划是否适合:b的各种值，例如'％A'，'％'，'A'等.

Oracle cannot know that the value of :b1 is '%some_value%', or 'some_value' etc. until execution time, so it will make an estimation of the cardinality of the result based on heuristics and come up with an appropriate plan that either may or may not be suitable for various values of :b, such as '%A','%', 'A' etc.

类似的问题可以用相等谓词来应用，但是例如，基于列统计信息或唯一约束的存在，可能更容易得出基数的范围.

Similar issues can apply with an equality predicate but the range of cardinalities that might result is much more easily estimated based on column statistics or the presence of a unique constraint, for example.

因此，就我个人而言，我不会开始使用LIKE来代替=.有时候，优化器很容易被愚弄.

So, personally I wouldn't start using LIKE as a replacement for =. The optimizer is pretty easy to fool sometimes.

这篇关于使用LIKE'string'vs ='string'对Oracle的性能有何影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！