Martijn,

Well I am no longer surprised that my query didn't work the way I expected.
This is an old table, one of the first put in the database and I thought all
of
the table IDs had been changed to integer (long time ago).

I have become so use to them all being integer that I didn't even realize
that this one is a VARCHAR(6).

Thank you very much for pointing that out.

Michael Horne




_____

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




Hi,

>> 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);
>>

> 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?

You're creating the index on a string field, not an integer field.

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

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene <http://www.upscene.com> com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.database <http://www.databasedevelopmentforum.com>
developmentforum.com



.

<http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId
=98932/stime=1228813061/nc1=4025291/nc2=3848644/nc3=5349273>




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