_____

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

.

<http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId
=98929/stime=1228811796/nc1=4025338/nc2=3848641/nc3=5349272>




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