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
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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
