Performance of IN operator

[Christine Johnson]

Hello, fellow Derby users   I have a table like this:   CREATE TABLE Things (             thingIdx             BIGINT  NOT NULL GENERATED ALWAYS AS IDENTITY,             thingName         VARCHAR(80) NOT NULL,             ...         // bunch of other columns not relevant to my problem             CONSTRAINT Things_PK PRIMARY KEY (thingIdx) )   I find that when I want to select a bunch of non-contiguous values, for example:   SELECT thingIdx, thingName FROM Things WHERE thingIdx IN (?, ?, ?, ?, ... [100 values scattered throughout index])   performance is pretty slow -- a bit over a minute for 100 records.  The same SQL for the same data runs in less than one second on SQL Server, Oracle, and DB2.  If the values are contiguous (e.g., thingIdx IN (1, 2, 3, 4, 5, 6, ...) Derby's performance is comparable to that of the other databases.  But if I were looking for contiguous records, I'd use BETWEEN, not IN.   Has anyone encountered this before, and can you suggest how to improve performance when using IN?   Regards, Chris Johnson [EMAIL PROTECTED]