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

  • From: Shaun Batterton <shaun.batterton@xxxxxxxxx>
  • To: parkerpg@xxxxxxxxx
  • Date: Thu, 13 Oct 2011 02:45:00 -0400

I agree.  I'm looking at 11.1.0.7.0.  The optimizer is guessing a
fixed 5% for cardinality based on LIKE '%something'.  Like '%' isn't
explicitly transformed into 'is not null', but the cardinality is
guessed correctly to the number of rows.

... like '%';
|   0 | SELECT STATEMENT  |            |  4486 |   573K|    36   (0)| 00:00:01 |

... like 'prefix%';
|   0 | SELECT STATEMENT  |            |     3 |   393 |    36   (0)| 00:00:01 |

... like '%prefix';
|   0 | SELECT STATEMENT  |            |   224 | 29344 |    36   (0)| 00:00:01 |

... like '%%';
|   0 | SELECT STATEMENT  |            |   224 | 29344 |    36   (0)| 00:00:01 |

This is a case where the optimizer is tricked into using a plan that
works best for now.  I think it would be better to hint the optimizer
with helpful information to get the same plan without the strange
where clause.  These tricks become unnecessary as the optimizer
becomes more intelligent with things like cardinality feedback, right?


On Wed, Oct 5, 2011 at 11:03 AM, Paul G Parker <parkerpg@xxxxxxxxx> wrote:
> Seems like the cardinality drops to 5% when using '%%' as opposed to '%' -
> so must be an optimizer anomaly.
> On Wed, Oct 5, 2011 at 10:27, 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.
>>
>>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l


Other related posts: