Oracle中的INSTR和LIKE有什么区别? [英] What is the difference between INSTR and LIKE in Oracle?

问题描述

有人可以告诉我Oracle中INSTR和LIKE之间的区别吗?

Can some one tell me the difference between INSTR and LIKE in Oracle?

在Oracle10g中哪个更快?

Which one is faster in Oracle10g?

推荐答案

这取决于数据和模式.如果使用like 'a%'，则Oracle可以使用BTree索引来查找匹配项，因为它可以从模式的开头搜索btree，然后仅考虑子树.

That depends on the data and on the pattern. If you use like 'a%', then Oracle can use a BTree indexes to look up the matches because it can search the btree with the start of the pattern and then consider only the subtree.

这不适用于LIKE '%a'，但是您可以通过创建一个计算列来解决此问题，该列将要搜索的列中的所有值都反转(这样您就可以得到上面的模式).

This doesn't work for LIKE '%a' but you can work around this by creating a calculated column which reverses all values from the column you want to search (so you get the pattern above).

如果使用散列索引，则Oracle几乎无能为力，只能扫描整个索引.当只有几个不同的值时，它可能会更快.

If you use hashed indexes, there is little that Oracle can do but scan the whole index. It might sill be faster when there are only few different values.

我不确定INSTR是否可以使用索引，因为它没有固定的锚点.

I'm not sure whether INSTR can ever use an index because it has no fixed anchor.

就像所有性能问题一样:

So like with all performance questions:

1. 用一些实际的测试数据填充数据库并运行一些测试.
2. 始终以一种可以在以后了解瓶颈的方式轻松优化的方式编写代码
3. 永远不要猜测可能会变慢.您有90％的时间会错.始终测量.

1. Fill the database with some realistic test data and run some tests.
2. Always write your code in a way that it can be optimized easily later, when you know about the bottlenecks
3. Never guess what might be slow. You'll be wrong 90% of the time. Always measure.

这篇关于Oracle中的INSTR和LIKE有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！