sql - Any performance impact in Oracle for using LIKE 'string' vs = 'string'?

Question

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 "=" ?

Answer 1

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.

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.

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.