Dmitry,

Thank you very much for clarifying how this worked.

As I mentioned to Martijn, I had not noticed that the
field was a VARCHAR. I thought all of the table keys
in this database had been changed to INTEGER long
ago. So I was blinded by my assumptions.

But I thank you very much for explaining how the
comparisons work. I wouldn't have thought of that
and was having a lot of trouble understanding why
my query was using natural.

I now know how to solve my problem.

Thanks
Michael Horne


_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Yemanov
Sent: Tuesday, December 09, 2008 4:04 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query not using/choosing plan correctly



Michael Horne wrote:

>
> However, why doesn't it work with INTEGERS. It would seem to me that
> an index built on integer fields would work faster than one based on a
> strings?

Possibly. But your column OM_CUS_ID is a varchar(6) one, not integer. So
the index is built on a string, actually.

> Could you please enlighten me. I really don't understand this

deterministic

> way of converting a number???????

Let's look at a number of comparisons:

2000 = '2000'
2000 = '02000'
2000 = '2000.000'
etc

Accordingly to the SQL specification, they all evaluate to TRUE because
a string is converted to a number and not vice versa.

This works perfectly valid for NATURAL scans, but the situation gets
tricky when we start considering index scans. In this case, the value
must be *always* converted to an index key data type, because the index
key data type is fixed and cannot be changed dynamically. For integer
index keys (conversion string->integer) everything works good, same as
for natural scans. For string index keys (conversion integer->string)
the result becomes unpredictable, because the numeric value of 2000 has
a few possible matches inside the string value space (see above) but
they have different index keys, so the equality predicate cannot work
consistently in this case. This is why the optimizer tracks such
situations and downgrades the execution plan to use NATURAL instead,
because, by design, the chosen query plan should never affect the query
result.

Dmitry







[Non-text portions of this message have been removed]