I read somewhere (probably on this news group) that Firebird doesn't
like compound indexes.

What if you were to create to separate indexes rather than

create index RETURN_SALE_BATCH_DATE_IDX on
return_sale(XRDS644_BUSINESS_DATE, TRACER_NBR);


Bob Murdoch wrote:

> 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..
>
>
> Yahoo! Groups Sponsor
> ADVERTISEMENT
> <http://rd.yahoo.com/M=245454.3115308.4434529.1728375/D=egroupweb/S=1705115386:HM/A=1457554/R=0/*http://ipunda.com/clk/beibunmaisuiyuiwabei>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/>.

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