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.
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.
Regards,
Ian
-----Original Message-----
From: info-ingres-bounces@[EMAIL PROTECTED]
On Behalf Of Roy
Hann
Sent: 08 July 2008 08:42
To: info-ingres@[EMAIL PROTECTED]
[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.
_______________________________________________
Info-Ingres mailing list
Info-Ingres@[EMAIL PROTECTED]
|
