在 Oracle 中使用 LIKE 'string' vs = 'string' 对性能有什么影响？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/134456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Any performance impact in Oracle for using LIKE 'string' vs = 'string'?
提问by JosephStyons
This
这个
SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE '%some_value%';
is slower than this
比这慢
SELECT * FROM SOME_TABLE WHERE SOME_FIELD = 'some_value';
but what about this?
但是这个呢？
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 "=" ?
我的测试表明第二个和第三个示例完全相同。如果这是真的，我的问题是，为什么要使用 "=" ？
回答by David Aldridge
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.
使用绑定变量时有明显的区别，您应该在 Oracle 中将其用于数据仓库或其他批量数据操作以外的任何操作。
Take the case of:
以以下案例为例：
SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE :b1
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.
Oracle 无法知道 :b1 的值是 '%some_value%' 或 'some_value' 等，直到执行时间，因此它将基于启发式估计结果的基数，并提出适当的计划，要么可能适合也可能不适合 :b 的各种值，例如 '%A'、'%'、'A' 等。
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.
类似的问题可以应用于等式谓词，但可能导致的基数范围更容易根据列统计信息或唯一约束的存在来估计，例如。
So, personally I wouldn't start using LIKE as a replacement for =. The optimizer is pretty easy to fool sometimes.
所以，我个人不会开始使用 LIKE 作为 = 的替代品。优化器有时很容易被愚弄。
回答by Wayne
Check out the EXPLAIN PLANfor both. They generate the same execution plan, so to the database, they're the same thing.
查看两者的解释计划。它们生成相同的执行计划，因此对于数据库而言，它们是相同的。
You would use = to test for equality, not similarity. If you're controlling the comparison value as well, then it doesn't make much of a difference. If that's being submitted by a user, then 'apple' and 'apple%' would give you much different results.
您将使用 = 来测试相等性，而不是相似性。如果您还控制比较值，则不会有太大区别。如果这是由用户提交的，那么 'apple' 和 'apple%' 会给你带来很大不同的结果。
回答by James Curran
If that's true, my question is, why ever use "=" ?
如果这是真的，我的问题是，为什么要使用 "=" ？
A better question: If that's true, why use "LIKE" to test for equality? You get to save hitting the shift key, and everyone who reads the script gets to be confused.
一个更好的问题：如果这是真的，为什么要使用“LIKE”来测试相等性？您可以节省按 Shift 键，并且每个阅读脚本的人都会感到困惑。
回答by Jon Ericson
Have you tried it? Testing is the only sure way to know.
你试过吗？测试是唯一确定的方法。
As an aside, none of these statements are certain to return the same rows. Try out:
顺便说一句，这些语句中没有一个肯定会返回相同的行。试用：
insert into some_table (some_field) values ('some_value');
insert into some_table (some_fieled) values ('1some_value2');
insert into some_table (some_field) values ('some1value');
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';
In terms of clarity and to avoid subtle bugs, it's best to never use LIKEunless you need it's wildcard functionality. (Obviously, when doing ad-hoc queries, it's probably alright.)
就清晰度和避免细微错误而言，最好不要使用LIKE，除非您需要它的通配符功能。（显然，在进行临时查询时，可能没问题。）
回答by Greg Ogle
LIKE '%WHATEVER%' will have to do a full index scan.
LIKE '%WHATEVER%' 将不得不做一个完整的索引扫描。
If there is not percent, then it acts like an equals.
如果没有百分比，那么它就像一个等号。
If the % is on one end, then the index can be a range scan.
如果 % 在一端，则索引可以是范围扫描。
I'm not sure how the optimizer handles bound fields.
我不确定优化器如何处理绑定字段。
回答by borjab
likeis formally the same if you do not have characters like $%etc so it is not a big surprise to find that it has the same cost.
like如果您没有诸如$%etc 之类的字符，则形式上是相同的，因此发现它具有相同的成本也就不足为奇了。
I find David Aldridge answer interesting as your application should be using bind variables. With a like '%foobar'you cannot make use of ordering in the index. If the query is pre-compiled It will lead to more index or table full Scans.
我发现 David Aldridge 的回答很有趣，因为您的应用程序应该使用绑定变量。使用 alike '%foobar'您不能使用索引中的排序。如果查询是预编译的，会导致更多的索引或表全扫描。
Furthermore, I find it dangerous as it can lead to SQL Injections and strange bugs ( for example, if there is a user called john a hacker can create a user named 'joh$'and try to log in)
此外，我发现它很危险，因为它可能导致 SQL 注入和奇怪的错误（例如，如果有一个名为 john 的用户，黑客可以创建一个名为的用户'joh$'并尝试登录）
why take the risk? '='is clearer and has none of those problems.
为什么要冒险？'='更清晰，没有这些问题。
回答by user3260035
1) % and = are intended to be used in different scenarios. Even though we can search with exact value in like clause and get the desired result, one should still be using = in all such scenarios. Therefore whenever we have exact value to be searched we always should be using =.
1) % 和 = 旨在用于不同的场景。即使我们可以在 like 子句中使用精确值进行搜索并获得所需的结果，但在所有此类场景中仍应使用 =。因此，每当我们有要搜索的确切值时，我们总是应该使用 =。
2) performance of like and = whenever no % is provided in search clause: In all such cases, query optimizer automatically converts like cluase to =. And this can be evident from query plan (Please see screenshots attached) . Therefore performance should be exactly the same in this case. Thanks to query optimizer!
2) 当搜索子句中没有提供 % 时，like 和 = 的性能：在所有这些情况下，查询优化器会自动将 like 子句转换为 =。这可以从查询计划中明显看出（请参阅所附的屏幕截图）。因此，在这种情况下，性能应该完全相同。感谢查询优化器！
I am providing the screenshots of the execution plans of both the queries i.e. query with like clause but without % and query with like clause and with %.
我提供了两个查询的执行计划的屏幕截图，即使用 like 子句查询但没有 % 以及使用 like 子句和 % 查询。
