Re: Is there a difference between LIKE and = when no wildcards are used?

Ken Denny wrote:
>
> I am writing a stored procedure in PL/SQL. One of the arguments being
> passed may contain a % character to be used as a wildcard which I then
> use in a cursor definition:
>
> PROCEDURE get_stuff(site_in VARCHAR2)
> IS
> CURSOR eq_by_site IS
> SELECT site, prod, release, qty FROM site_equip
> WHERE site LIKE site_in ORDER BY site, prod, release;
>
> My question is: Would I benefit from defining another cursor in which
> the WHERE clause said "WHERE site = site_in", then examine site_in to
> see whether it contained a % or not to decide which cursor to use?
>
> Please email replies
> Thanks
> Ken Denny
> kendenny_at_bnr.ca (work)
> kdenny_at_interpath.com (home)

When no wild card is present, and the statement contains a literal value (column LIKE 'XXX') LIKE is treated the same as "=". However, using a variable (rather than a literal) means that the statement is parsed (and the execution plan is established) *before* the variable value is known, so the parser takes a pessimistic view of what the variable might contain when deciding on access path. I believe the cost-based optimiser assumes that 50% of rows might be retrieved and makes its decision accordingly.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"