SQL 在 Oracle 中 substr 或 LIKE 更快吗？

Is substr or LIKE faster in Oracle?

提问by Brian Ramsay

Would

将

WHERE substr(my_field,1,6) = 'search'

or

或者

WHERE my_field LIKE 'search%'

be faster in Oracle, or would there be no difference?

在 Oracle 中更快，还是没有区别？

采纳答案by Justin Cave

Assuming maximum performance is the goal, I would ideally choose SUBSTR(my_field,1,6)and create a function-based index to support the query.

假设最高性能是目标，我最好选择SUBSTR(my_field,1,6)并创建一个基于函数的索引来支持查询。

CREATE INDEX my_substr_idx
    ON my_table( substr( my_field,1,6 ) );

As others point out, SUBSTR(my_field,1,6)would not be able to use a regular index on MY_FIELD. The LIKE version might use the index, but the optimizer's cardinality estimates in that case are generally rather poor so it is quite likely to either not use an index when it would be helpful or to use an index when a table scan would be preferable. Indexing the actual expression will give the optimizer far more information to work with so it is much more likely to pick the index correctly. Someone smarter than I am may be able to suggest a way to use statistics on virtual columns in 11g to give the optimizer better information for the LIKE query.

正如其他人指出的那样，SUBSTR(my_field,1,6)将无法在MY_FIELD. LIKE 版本可能使用索引，但在这种情况下优化器的基数估计通常相当差，因此很有可能要么在有用时不使用索引，要么在表扫描更可取时使用索引。索引实际表达式将为优化器提供更多信息，因此它更有可能正确选择索引。比我更聪明的人可能会建议一种使用 11g 中虚拟列的统计信息的方法，以便为优化器提供更好的 LIKE 查询信息。

If 6 is a variable (i.e. you sometimes want to search the first 6 characters and sometimes want to search a different number), you probably won't be able to come up with a function-based index to support that query. In that case, you're probably better off with the vagaries of the optimizer's decisions with the LIKE formulation.

如果 6 是一个变量（即您有时想搜索前 6 个字符，有时想搜索不同的数字），您可能无法提出基于函数的索引来支持该查询。在这种情况下，您可能会更好地使用 LIKE 公式处理优化器决策的变幻莫测。

回答by beach

Of the two options provided, definitely LIKE. The substring method will have to be executed against all rows in the table. Using LIKE will allow the use of indexes.

在提供的两个选项中，绝对喜欢。必须对表中的所有行执行 substring 方法。使用 LIKE 将允许使用索引。

To check my answer, just profile the results. It should be clear as day.

要检查我的答案，只需分析结果。应该是晴天。

回答by A-K

If you have an index on my_field, then LIKE may be faster. Do your own benchmarks.

如果您在 my_field 上有索引，那么 LIKE 可能会更快。做你自己的基准测试。

回答by Christian13467

If you have no indexthan there is no difference. Because oracle is doing a full table scan and evaluates the expression for each row. You can put an index on the column to speed up both queries.

如果您没有索引，则没有区别。因为oracle 正在做全表扫描，并对每一行的表达式求值。您可以在列上放置索引以加速两个查询。

CREATE INDEX my_like_idx
ON my_table( my_field );

This index is more flexible and speeds up the query using like. It will work for any compare starting with characters and having placeholder (%) at the end. Oracle is doing a index range scan to find all matching rows.

这个索引更灵活，使用like可以加快查询速度。它适用于任何以字符开头并以占位符 (%) 结尾的比较。Oracle 正在执行索引范围扫描以查找所有匹配的行。

CREATE INDEX my_substr_idx
ON my_table( substr( my_field,1,6 ) );

This index speeds up the query with substr. But the index is very special to compare only the first 6 characters.

此索引使用 substr 加快查询速度。但是索引很特殊，只能比较前6个字符。

If you query for a piece of starting in the middle. Creating a function based index will help.

如果您查询从中间开始的一块。创建基于函数的索引会有所帮助。

WHERE substr(my_field,2,5) = 'earch'
WHERE my_field like '%earch%'

回答by David Aldridge

There's really two issues here:

这里真的有两个问题：

1. For which one will Oracle produce the more accurate cardinality and cost estimate?
2. Which method is more flexible in terms of potential access methods?

1. Oracle 将为哪一个生成更准确的基数和成本估算？
2. 就潜在的访问方法而言，哪种方法更灵活？

This may vary by version, but both are pretty easy to test and that way you're sure that you have the best information for your version and your data.

这可能因版本而异，但两者都非常容易测试，这样您就可以确保您拥有适合您的版本和数据的最佳信息。

Run execution plans for both queries using ...

使用...为两个查询运行执行计划

explain plan for 
select ... from ... where my_field LIKE 'search%';

select * from table(dbms_xplan.display);

and

和

explain plan for 
select ... from ... where substr(my_field,1,6) = 'search';

select * from table(dbms_xplan.display);

You may see a difference in the execution plan, depending on the presence of indexes etc., but also compare the cardinality estimates with the actual result that you get from:

您可能会看到执行计划中的差异，具体取决于索引等的存在，但还将基数估计与您获得的实际结果进行比较：

select count(*) from ... where my_field LIKE 'search%';

One of the two methods may be significantly more accurate than the other.

这两种方法中的一种可能比另一种更准确。

If neither of them is very accurate andthis query is expected to run for a non-trivial amount of time then consider using dynamic sampling to improve the estimate, because with the wrong cardinality estimate the optimizer may chose a suboptimal access method anyway.

如果它们都不是非常准确，并且该查询预计会运行一段非平凡的时间，那么请考虑使用动态采样来改进估计，因为对于错误的基数估计，优化器无论如何都可能选择次优访问方法。

explain plan for 
select /*+ dynamic_sampling(4) */ ... from ... where substr(my_field,1,6) = 'search';

select * from table(dbms_xplan.display);

As far as index usage goes, both methods could use an index-based access method. The LIKE predicate is probably more index friendly and could use a range scan or a fast full index scan. The SUBSTR method can certainly use the fast full index scan, but whether the optimizer will consider a range scan is best tested on your own version -- my recollection is that it won't but who's to say that substr(my_column,1,n) won't be recognised as a special case, if not now then in the future?

就索引使用而言，这两种方法都可以使用基于索引的访问方法。LIKE 谓词可能对索引更友好，可以使用范围扫描或快速全索引扫描。SUBSTR 方法当然可以使用快速全索引扫描，但是优化器是否会考虑范围扫描最好在您自己的版本上进行测试 - 我记得它不会但是谁说 substr(my_column, 1,n ) 不会被认为是特例，如果不是现在，那么将来？

回答by Manuel Ferreria

I would profile both. But I would guess the 'LIKE' would be much faster, because it uses the binary search on the index (if the field is indexed). If you use the SUBSTR method, you will end up with a full table scan, as Oracle has to process row by row the function.

我会分析两者。但我猜“LIKE”会快得多，因为它对索引使用二分搜索（如果该字段已编入索引）。如果您使用 SUBSTR 方法，您将以全表扫描结束，因为 Oracle 必须逐行处理该函数。