Last post Feb 14, 2017 06:35 AM by wmec
Member
11 Points
23 Posts
Feb 08, 2017 01:51 PM|ZeRu|LINK
Which method is faster?
SELECT .... FROM TableName WHERE ColumnName LIKE 'value1' OR ColumnName LIKE 'value2'
or
SELECT .... FROM TableName WHERE ColumnName IN ('value1', 'value2')
It's an Oracle database.
All-Star
48660 Points
18169 Posts
Feb 08, 2017 02:54 PM|PatriceSc|LINK
Hi,
I don't expect any difference. The db engine should see this is just the same query and that LIKE is used as =
When simple enough on your side, it's likely best to just try yourself with http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm (for example a more complex plan might depend on which Oracle version is used).
The key point here would be rather to have an index on the ColumnName.
Contributor
6479 Points
5815 Posts
Feb 14, 2017 06:35 AM|wmec|LINK
1. For 1st case, you are not suggested to use like. For like, we usually put like column1 like 'Value%' 2. To 2nd case, it is checking/picking up data by index
Member
11 Points
23 Posts
LIKE vs IN
Feb 08, 2017 01:51 PM|ZeRu|LINK
Which method is faster?
SELECT .... FROM TableName WHERE ColumnName LIKE 'value1' OR ColumnName LIKE 'value2'
or
SELECT .... FROM TableName WHERE ColumnName IN ('value1', 'value2')
It's an Oracle database.
All-Star
48660 Points
18169 Posts
Re: LIKE vs IN
Feb 08, 2017 02:54 PM|PatriceSc|LINK
Hi,
I don't expect any difference. The db engine should see this is just the same query and that LIKE is used as =
When simple enough on your side, it's likely best to just try yourself with http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm (for example a more complex plan might depend on which Oracle version is used).
The key point here would be rather to have an index on the ColumnName.
Contributor
6479 Points
5815 Posts
Re: LIKE vs IN
Feb 14, 2017 06:35 AM|wmec|LINK
1. For 1st case, you are not suggested to use like. For like, we usually put like column1 like 'Value%'
2. To 2nd case, it is checking/picking up data by index
Peter