Re: What does LIKE '%%' actually do in a WHERE clause?

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: norman.dunbar.capgemini@xxxxxxxxxxxxxxxxxxxxxxxxx
  • Date: Wed, 05 Oct 2011 17:01:15 +0200

     Hey, something new. I assume that 'id' is the PK (eg not null) and 
that it's the right type (varchar2 or char).
I suspect that the optimizer:
    1) is smart enough to see that LIKE '%' does nothing and remove it
    2) NOT smart enough to see that LIKE '%%' does nothing more
    3) and as you noticed, goes for indexes instead of scanning tables 
with possibly a large number of rows.

Kind of trick based on a side-effect that is a potential time-bomb ... 
The fact that LIKE '%something' hits an index is just a matter of 
relative cost of scanning the index vs scanning the table. Such things 
can change.

Since you only want 100 rows, what about

from (select * from A where rownum <=100) A,
          (select * from B where rownum <= 100) B,

If you are dead-sure that all tables contain at least 4 rows you could 
even replace 100 by 4 above, but it's taking a risk for a minor benefit 
(especially is 100 rows fit into one block).

Stephane Faroult
RoughSea Ltd <>
Konagora <>
RoughSea Channel on Youtube <>

On 10/05/2011 04:27 PM, Dunbar, Norman (Capgemini) 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 like '%%'
> AND like '%%'
> AND like '%%'
> AND 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


Other related posts: