在Oracle中，substr或LIKE更快吗? [英] Is substr or LIKE faster in Oracle?

问题描述

会

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

或

WHERE my_field LIKE 'search%'

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

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

推荐答案

假设最大的性能是目标，我理想地选择SUBSTR(my_field,1,6)并创建基于函数的索引来支持查询.

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.

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

正如其他人指出的那样，SUBSTR(my_field,1,6)将无法在MY_FIELD上使用常规索引. LIKE版本可能使用索引，但是在这种情况下，优化器的基数估计通常很差，因此很有可能不使用索引(如果有帮助的话)，或者使用索引(最好是表扫描).索引实际表达式将为优化器提供更多信息，以便更正确地选择索引.比我聪明的人也许可以建议一种使用11g中虚拟列统计信息的方法，为优化器提供有关LIKE查询的更好信息.

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.

如果6是一个变量(即您有时要搜索前6个字符，有时又要搜索其他数字)，则可能无法提出基于函数的索引来支持该查询.在这种情况下，使用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.

这篇关于在Oracle中，substr或LIKE更快吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！