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