I have the following query:

SELECT
RETURN_TYPE, RETURN_CODE, ORIG_DOCUMENT
FROM
RETURN_SALE
WHERE
(DEL_DATE = '3/8/2003') AND (TRACER_NBR = '30462815') AND
(ACCOUNT_ID = 1) AND (RETURN_TYPE <> '2')

Which results in a 3ms execution time and the following plan:
PLAN (RETURN_SALE INDEX (RDB$PRIMARY1,RETURN_SALE_DELDATE_IDX))

if I add this to the where clause:

AND (XRDS644_BUSINESS_DATE <= '3/10/2003')

The execution time jumps to 254ms, and the following plan:
PLAN (RETURN_SALE INDEX
(RDB$PRIMARY1,RETURN_SALE_DELDATE_IDX,RETURN_SALE_BATCH_DATE_IDX))


The table DDL looks like this:

create table return_sale(
TRACER_NBR varchar(20) not null,
BATCH_ID integer not null,
ACCOUNT_ID integer not null,
DOC_TYPE char(1),
RETURN_TYPE char(1),
RETURN_SOURCE char(1),
ORIG_RET_CODE char(2),
FINAL_RET_CODE char(2),
DEL_DATE date,
XRDS644_BUSINESS_DATE date,
RETURN_CODE char(1))

alter table return_sale add constraint PK_RETURN_SALE primary key
(TRACER_NBR, BATCH_ID);
create index RETURN_SALE_DELDATE_IDX on return_sale(DEL_DATE, TRACER_NBR);
create index RETURN_SALE_BATCH_DATE_IDX on
return_sale(XRDS644_BUSINESS_DATE, TRACER_NBR);
create index RETURN_SALE_BATCH_ID on return_sale(BATCH_ID, ACCOUNT_ID);


I think the obvious choice here is to remove the RETURN_SALE_BATCH_DATE_IDX
index. However, that is an absolutely necessary index for other parts of
the application. Is there any other option available to me?


Using FB1 on W2k server, as well as IB6 on NT4.


tia,

Bob M..