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

Norman,
     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,
    etc?

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 <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>





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 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
>



--
http://www.freelists.org/webpage/oracle-l


Other related posts: