I have a query that involves two tables that form a master-detail
relationship, using FB1.5. When just those two tables are involved, I get
indexed reads on both tables with the proper indices being used. However,
when I add a third table to query, the foreign key index of the detail
table no longer gets used in the query - a natural scan is performed. As
well, the correct index is no longer used in the master table either.

Here's the first query that used the correct indices:

select
m.id, m.sale_id, m.sku, m.line_nbr, sum(m.qty) as qty
from
salescheck sc
join sale_mdse m on (m.sale_id = sc.id)
where
(sc.del_date = '1/24/2004') and
(sc.region = 10)
group by
m.id, m.sale_id, m.sku, m.line_nbr

PLAN SORT (JOIN (SC INDEX (SALESCHECK_DEL_DATE_IDX),M INDEX (RDB$FOREIGN174)))


In this second query, I need to pull some information from a third table:

select
m.id, m.sale_id, m.sku, m.line_nbr, sum(m.qty) as qty
from
salescheck sc
join sale_mdse m on (m.sale_id = sc.id)
join sku sk on (sk.sku_nbr = m.sku) and (sk.account_id = sc.account_id)
where
(sc.del_date = '1/24/2004') and
(sc.region = 10)
group by
m.id, m.sale_id, m.sku, m.line_nbr

PLAN SORT (JOIN (M NATURAL,SC INDEX (RDB$PRIMARY105),SK INDEX
(RDB$PRIMARY110)))

As you can see from the above plan, the primary index on SC is being used,
which makes no sense whatsoever. Additionally, the table M is not being
joined correctly based on it's foreign key to SC.

Here's some of the table metadata

create table salescheck(
ID integer not null,
TRACER_NBR varchar(20),
DEL_DATE date,
ACCOUNT_ID integer not null,
<snip>);
alter table salescheck add constraint PK_SALESCHECK primary key (ID);
create unique index SALESCHECK_UNQ_IDX on salescheck(ACCOUNT_ID,
TRACER_NBR, DEL_DATE);
create index SALESCHECK_DEL_DATE_IDX on salescheck(DEL_DATE, ID);


create table sale_mdse(
ID integer not null,
SALE_ID integer not null,
SKU varchar(20) not null,
<snip>);
alter table sale_mdse add constraint PK_SALE_MDSE primary key (ID);
alter table sale_mdse add constraint FK_SALE_MDSE foreign key (SALE_ID)
references SALESCHECK(ID) on delete CASCADE;


create table sku(
SKU_NBR varchar(20) not null,
ACCOUNT_ID integer,
<snip>);
alter table sku add constraint PK_SKU primary key (SKU_NBR, ACCOUNT_ID);


Thank you for any tips,

Bob M..