Re: What does LIKE '%%' actually do in a WHERE clause?
- From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
- To: norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx
- Date: Wed, 5 Oct 2011 20:41:04 +0530
hello Norman,
and if we are going to use rownum then it will be always faster I suppose..
a*, b*, c*, d* will include all columns hence default index will work and if
I remember correctly then if rownum is used then the default won't work,
rowid or objectid will work..
not very sure randomly guess may be '%%' will ignore nulls..
thanks..subodh
On 5 October 2011 19:57, Dunbar, Norman (Capgemini) <
norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Afternoon all,
>
> a developer is asking me what the exact function of "LIKE '%%'" in a WHERE
> clause is. I'm stumped, and cannot find anything on Google or in the Oracle
> docs.
>
> The puzzler is this:
>
> There is a SELECT on 4 tables using a cartesian join of all 4 and returning
> only the first 100 rows:
>
> SELECT A.*, B.*, C.*, D.*
> FROM A, B, C, D
> WHERE ...
> AND ROWNUM < 101;
>
> The WHERE clause simply joins the 4 tables, there is no actual record
> selection.
>
> As expected, this takes a wee while and results in a plan that hash joins
> the full table scan results for all tables.
>
> Now, the developer has added to the where clause something like the
> following:
>
> AND a.id like '%%'
> AND b.id like '%%'
> AND c.id like '%%'
> AND d.id like '%%'
>
> This time, the query runs in seconds and uses indexes on the ID columns of
> all the tables.
>
> The cardinality comes down to 1 for each table in the plan, as opposed to
> the previous 3 million or greater, but the results are exactly the same.
>
> If '%%' is replaced by '%' it goes back to the original plan of hash
> joining the 4 full table spaces.
>
> Google seems to ignore the %% in a search as does Oracle, whether in single
> or double quotes.
>
> It's puzzling me (and a colleague) and we don't like being puzzled. All the
> developer knows is that he heard about this "trick" somewhere, but he can't
> remember where and he wants to know how it works and what it is doing!
>
>
> Cheers,
> Norm.
>
> Norman Dunbar
> Contract Senior Oracle DBA
> Capgemini Database Build Team
> Internal : 7 28 2051
> External : 0113 231 2051
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for litigation.
> Email messages and attachments sent to or from any Environment Agency
> address may also be accessed by someone other than the sender or recipient,
> for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506. Find
> out more about the Environment Agency at www.environment-agency.gov.uk
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
==============================
DO NOT FORGET TO SMILE TODAY
==============================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
