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

Check out the EXPLAIN PLAN for both. They generate the same execution plan, so to the database, they're the same thing.

You would use = to test for equality, not similarity. If you're controlling the comparison value as well, then it doesn't make much of a difference. If that's being submitted by a user, then 'apple' and 'apple%' would give you much different results.

Answer 2

Have you tried it? Testing is the only sure way to know.

As an aside, none of these statements are certain to return the same rows. Try out:

insert into some_table (some_field) values ('some_value');
insert into some_table (some_fieled) values ('1some_value2');
insert into some_table (some_field) values ('some1value');

SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE '%some_value%';

SELECT * FROM SOME_TABLE WHERE SOME_FIELD = 'some_value';

SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE 'some_value';

In terms of clarity and to avoid subtle bugs, it's best to never use LIKE unless you need it's wildcard functionality. (Obviously, when doing ad-hoc queries, it's probably alright.)

Answer 3

LIKE '%WHATEVER%' will have to do a full index scan.

If there is not percent, then it acts like an equals.

If the % is on one end, then the index can be a range scan.

I'm not sure how the optimizer handles bound fields.

Answer 4

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.

Answer 5

If that's true, my question is, why ever use "=" ?

A better question: If that's true, why use "LIKE" to test for equality? You get to save hitting the shift key, and everyone who reads the script gets to be confused.

Answer 6

Like is formally the same if you do not have characters like $% etc so it is not a big surprise to find that it has the same cost.

I find David Aldridge answer interesting as your application should be using bind variables. With a like '%foobar' you cannot make use of ordering in the index. If the query is precompiled It will lead to more index or table full Scans.

Furthermore, I find it dangerous as it can lead to SQL Injections and stange bugs ( for example, if there is a user called john a hacker can create a user named 'joh$' and try to log in)

why take the risk? '=' is clearer and has none of thouse problems.