Ian Kirkham wrote:
> Hi Roy,
> The problem is not really LIKE. In this situation the keybld code
> determines that a match cannot occur and returns key no-match.
> Unfortunately, at the moment that causes just that query plan to be
> dropped instead of doing the better option of saying choose this qp as
> we need not even touch the data.
Well that makes more sense. It isn't an "architectural" problem--which
I took to mean they think "Ingres is deliberately designed to do it that
way"--it is just a flat out bug.
> I think this case is so infrequent that no-one has followed through the
> implications of making use of this strange case - it gets treated as key
> all match hence the table scan. Personally I'd consider this sort of
> problem more of a developer error and unlikely to happen in practice but
> if you think this likely to cause too much hassle then we could look at
> this further.
Thanks Ian. Unfortunately the developer error is deeply buried
because the code is generated "on the fly" by Unicenter. I imagine
dynamically construct queries will only get more common, and it is
probably easy to miss the problem with smaller tables. The reason it
came to attention in this case is because the table being scanned has
4 million 8kb pages, at a site with ~1,000 users likely to query it.
This will need to go through channels (i.e. CA), but you've given me
a much better explanation than they did.
Roy
> -----Original Message-----
> From: info-ingres-bounces@[EMAIL PROTECTED]
> [mailto:info-ingres-bounces@[EMAIL PROTECTED]
On Behalf Of Roy
> Hann
> Sent: 08 July 2008 08:42
> To: info-ingres@[EMAIL PROTECTED]
> Subject: [Info-Ingres] LIKE with oversize pattern forces table scan?
>
> One of my colleagues is dealing with a Unicenter Helpdesk site and has a
> call open with CA (remember them?). The problem is that he's noticed
> that when a query includes a LIKE condition where the pattern contains
> no wild-card characters but the pattern is longer than the declared
> length of the column being tested, Ingres invariably does a table scan,
> even though there's a usable index.
>
> CA tech sup****t have responded "Ingres has always behaved this way, and
> as far as we know, it always will, because of the way the architecture
> is designed."
>
> I can buy the first bit of that, but the second part sounds like
> baloney. Can anyone sup****t or refute that?
>
> (Privately, it seems to me that it would be more correct to re****t an
> error when the pattern is too long to match any data. But that is
> another rant for another day.)
>
> Roy
>
--
UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join
to get on the mailing list.
|
