Is substr or LIKE faster in Oracle?

Question

Would

WHERE substr(my_field,1,6) = 'search' 

or

WHERE my_field LIKE 'search%' 

be faster in Oracle, or would there be no difference?

Answer 1

Assuming maximum performance is the goal, I would ideally choose SUBSTR(my_field,1,6) and create a function-based index to support the query.

CREATE INDEX my_substr_idx     ON my_table( substr( my_field,1,6 ) ); 

As others point out, SUBSTR(my_field,1,6) would not be able to use a regular index on MY_FIELD. The LIKE version might use the index, but the optimizer's cardinality estimates in that case are generally rather poor so it is quite likely to either not use an index when it would be helpful or to use an index when a table scan would be preferable. Indexing the actual expression will give the optimizer far more information to work with so it is much more likely to pick the index correctly. Someone smarter than I am may be able to suggest a way to use statistics on virtual columns in 11g to give the optimizer better information for the LIKE query.

If 6 is a variable (i.e. you sometimes want to search the first 6 characters and sometimes want to search a different number), you probably won't be able to come up with a function-based index to support that query. In that case, you're probably better off with the vagaries of the optimizer's decisions with the LIKE formulation.

Answer 2

Of the two options provided, definitely LIKE. The substring method will have to be executed against all rows in the table. Using LIKE will allow the use of indexes.

To check my answer, just profile the results. It should be clear as day.