SUBSTR在Oracle中是更快还是更快?

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

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

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

如果6是一个变量(例如,您有时希望搜索前6个字符,有时希望搜索不同的数字),则可能无法找到支持该查询的基于函数的索引。在这种情况下,使用类似的公式,您可能更好地处理优化器决策的变幻莫测。

在提供的两个选项中,肯定是喜欢的。必须对表中的所有行执行子字符串方法。使用like将允许使用索引。

要检查我的答案,只需分析结果。天晴了。

如果我的域上有索引,那么like可能更快。做你自己的基准。

如果你有 无索引 比没有区别。因为Oracle正在进行全表扫描,并为每一行计算表达式。 您可以在列上放置索引,以加快两个查询的速度。

CREATE INDEX my_like_idx
ON my_table( my_field );

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

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

此索引使用SUBSTR加速查询。但是索引非常特殊,只能比较前6个字符。

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

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

这里有两个问题:

1. Oracle将为哪一个提供更准确的基数和成本估算?
2. 在潜在的访问方法方面,哪种方法更灵活?

这可能因版本而异,但两者都很容易测试,这样您就可以确定您的版本和数据具有最佳的信息。

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

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

select * from table(dbms_xplan.display);

和

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

select * from table(dbms_xplan.display);

您可能会看到执行计划中的差异,这取决于索引等的存在,但也会将基数估计值与您从中获得的实际结果进行比较:

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

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

如果两者都不准确 和 该查询预计会运行一段非常长的时间,然后考虑使用动态采样来改进估计,因为使用错误的基数估计,优化器无论如何可能会选择次优的访问方法。

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

select * from table(dbms_xplan.display);

就索引用法而言,两种方法都可以使用基于索引的访问方法。like谓词可能对索引更友好,可以使用范围扫描或快速完整索引扫描。SUBSTR方法当然可以使用快速完整索引扫描,但是优化器是否会考虑范围扫描最好在您自己的版本上进行测试——我记得它不会,但谁会说SUBSTR(我的专栏, 一 ,n)如果不是现在,将来不会被视为特殊情况?

我会同时介绍这两种情况。但我想“like”会快得多,因为它使用索引上的二进制搜索(如果字段是索引的)。如果您使用SUBSTR方法,您将得到一个完整的表扫描,因为Oracle必须逐行处理函数。