LIKE without wildcard versus = operator 2

[bean1234]

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.

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.

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[HanbingL]

If you have trailing spaces i.e. 'somename ' (notice the spaces, although the performance are going to be identical between the two queries.

select * from S where s.name='somename';

Will ignore the trailing spaces and return all matching rows.

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

will return only return rows with the exact word.

 

[HanbingL]

For example

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

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

 

[bean1234]

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.