CODEWITHSUNDEEP
sqlperformanceoracle-database
JosephStyons
JosephStyons

Reputation: 58685

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

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

Upvotes: 17

Views: 27465

Answers (7)

user3260035
user3260035

Reputation: 124

1) % and = are intended to be used in different scenarios. Even though we can search with exact value in like clause and get the desired result, one should still be using = in all such scenarios. Therefore whenever we have exact value to be searched we always should be using =.

2) performance of like and = whenever no % is provided in search clause: In all such cases, query optimizer automatically converts like cluase to =. And this can be evident from query plan (Please see screenshots attached) . Therefore performance should be exactly the same in this case. Thanks to query optimizer!

I am providing the screenshots of the execution plans of both the queries i.e. query with like clause but without % and query with like clause and with %.

like Cluase with % query execution plan

like clause without % query execution plan

Upvotes: 0

borjab
borjab

Reputation: 11655

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 pre-compiled It will lead to more index or table full Scans.

Furthermore, I find it dangerous as it can lead to SQL Injections and strange 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 those problems.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52336

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.

Upvotes: 21

James Curran
James Curran

Reputation: 103485

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.

Upvotes: 3

oglester
oglester

Reputation: 6670

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.

Upvotes: 1

Jon Ericson
Jon Ericson

Reputation: 21485

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.)

Upvotes: 2

Wayne
Wayne

Reputation: 39868

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.

Upvotes: 5

Related Questions