Wow, that does make a huge difference. That is as fast as I had expected
it would be..

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?

Could you please enlighten me. I really don't understand this deterministic
way
of converting a number???????

Thanks
Michael Horne



_____

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



Michael Horne wrote:

>
> I have a table with indexes defined as such:
>
> CREATE TABLE ORDERSM (
> OM_ID integer NOT NULL,
> OM_CUS_ID VARCHAR(6) CHARACTER SET NONE COLLATE NONE,
> OM_INVNUM integer,
> OM_COMPANY varchar(30));
>
> ALTER TABLE ORDERSM ADD CONSTRAINT ORDERSMBYID PRIMARY KEY (OM_ID);
>
> CREATE UNIQUE DESC INDEX ORDERSMBYCUSTNUM3 ON ORDERSM(OM_CUS_ID,OM_ID);
>
> Running the following query which returns only 1 record reads the entire
> file, just as if the plan was natural. But the plan reported
> is : "PLAN (ORDERSM ORDER ORDERSMBYCUSTNUM3)"
>
> SELECT *
> FROM ORDERSM
> where om_cus_id = 2000
> order by om_cus_id desc, om_id desc
>
> This seems like a bug to me?

If you'd use '2000' (string) instead of 2000 (number), it would make a
huge difference. In your case, an index scan cannot be used, because
there's no deterministic way to convert a number into a string (while
it's possible vice versa).

Dmitry







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