Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

LIKE without wildcard versus = operator 2

Status
Not open for further replies.

bean1234

Technical User
Nov 14, 2006
45
US
Hello Experts,

I have a scenario where I need to compare multiple fields using the like operator in a SP and the input can have the wild card operator '%' or it might not have it , in either case I use the LIKE operator (like input_variable) in SQL.

I would like to know if using the LIKE operator without the wildcard operator would be as efficient as using the equal operator.

For example the following two queries return same result.

Code:
select * from S where s.name like 'somename';

select * from S where s.name='somename';
But for DB2 and would they be translated into same Access Path , I mean would they be same efficiency wise or would the equal operator be more efficient.

I would like to know how DB2 implements this internally.

Thanks in advance.

 
LIKE is an indexable predicate, so if a.name is an index, s.name like "somename" will use index.

However if you use a wildcard as "%somename" it wont.

Also, and im not sure on this one now so please try it, it is possible that the following will not allow for the best access path to be used.

ix1
(name
,address
,postal_code)

if you do
select...
from ...
where name like 'somename'
and address = 'this_address'
and postal_code = 'that_code'

it is possible that only name will be used on the access path, and address and postal_code will only be considered sargable predicates. As i said, test it.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
If you have trailing spaces i.e. 'somename ' (notice the spaces, although the performance are going to be identical between the two queries.
Code:
select * from S where s.name='somename';
Will ignore the trailing spaces and return all matching rows.

Code:
select * from S where s.name like 'somename';
will return only return rows with the exact word.




 
For example
Code:
with s as 
(
select 'no trailing spaces' as istrailing, 'somename' as name from sysibm.sysdummy1
UNION ALL
select 'has trailing spaces' as istrailing, 'somename ' as name from sysibm.sysdummy1
)
select * from s where s.name like 'somename'

ISTRAILING	        NAME
no trailing spaces	somename

Code:
with s as 
(
select 'no trailing spaces' as istrailing, 'somename' as name from sysibm.sysdummy1
UNION ALL
select 'has trailing spaces' as istrailing, 'somename ' as name from sysibm.sysdummy1
)
select * from s where s.name = 'somename'

ISTRAILING              NAME
has trailing spaces     somename 
no trailing spaces      somename
 

Thank you guys for your replies (fredericofonseca & HanbingL ), from what I could infer from you posts like can be as efficient as the = operator if not used with the wild card operator.

HanbingL that was interesting to know that = operator would pick up the trailing spaces as well.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top