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