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

Asked 2008-09-25T16:46:35.850

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

sql performance oracle-database

Edit Report

1 Answer

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.

answered 2008-09-25T16:59:41.263