Hi Helen and Svein

I checked all the things you spoke about Helen, and will the the
left join to JobMast out, but the rest is still a mystery. Here is
all the information Svein asked for. Thanks for your help.

PURCHREQTAGS has 87602 rows
PURCHREQUISITION has 87379 rows

/* Table: PURCHREQUISITION */

CREATE TABLE PURCHREQUISITION (
INC_REQN TINC NOT NULL,
SITE TSITE,
PARTNO TPARTNO,
PARTPARAM TPARTPARAM,
OPERATION TPARTOP,
FULFILFROM TSTOCKTYPE,
FULFILDEPT TPRODDEPT,
FULFILSUPPNO TCOMPNO,
FULFILINC_COST TINC,
FULFILUNITSQTY TLEVEL,
ISPARTSATISFIED TBOOLEAN Default 'N',
SATISFYUNITSQTY TLEVEL,
ISSUEDBY TUSERNO,
ACTIONED TBOOLEAN DEFAULT "N",
WASBOUND TBOOLEANN Default 'N',
HASENQUIRY TBOOLEAN DEFAULT "N",
ACTIONBY TUSERNO,
ACTIONDTE TDATE,
ACTIONEDBY TUSERNO,
ACTIONEDDTE TDATETIME,
ACTIONEDREF TTRANNO,
REQUIREDDTE TDATE,
REQUESTERTYPE TSTOCKTYPEREQ,
REQUESTERNO TTRANNO15,
INC_REQUEST TINC,
INC_DEPENDANCY TINC,
DESTINATION TDESC50,
ISITEMINASSEMBLY TBOOLEAN Default 'N',
DESTINDEX TDESC20,
CREATEDDTE TDATETIME Default "Now",
CREATEDBY TUSERNO,
ALTEREDDTE TDATETIME,
ALTEREDBY TUSERNO);



/* Primary keys definition */

ALTER TABLE PURCHREQUISITION ADD PRIMARY KEY (INC_REQN);


/* Indices definition */

CREATE INDEX PURCHREQ_BYACTIONBY ON PURCHREQUISITION (ACTIONBY);
CREATE INDEX PURCHREQ_BYACTIONDTE ON PURCHREQUISITION (ACTIONDTE);
CREATE INDEX PURCHREQ_BYDESTINDEX ON PURCHREQUISITION (DESTINDEX);
CREATE INDEX PURCHREQ_BYFULFILFROM ON PURCHREQUISITION (FULFILFROM);
CREATE INDEX PURCHREQ_BYPART ON PURCHREQUISITION (PARTNO, PARTPARAM,
OPERATION);
CREATE INDEX PURCHREQ_BYREQUEST ON PURCHREQUISITION (INC_REQUEST);
CREATE INDEX PURCHREQ_BYREQUESTER ON PURCHREQUISITION
(REQUESTERTYPE, REQUESTERNO);
CREATE INDEX PURCHREQ_BYREQUIREDDTE ON PURCHREQUISITION
(REQUIREDDTE);
CREATE INDEX PURCHREQ_BYSITEACTION ON PURCHREQUISITION (SITE,
ACTIONED);


/* Table: PURCHREQTAGS */

CREATE TABLE PURCHREQTAGS (
INC_REQNT TINC NOT NULL,
INC_REQNB INTEGER,
INC_REQN TINC,
CREATEDBY TUSERNO,
CREATEDDTE TDATETIME Default "NOW");



/* Primary keys definition */

ALTER TABLE PURCHREQTAGS ADD PRIMARY KEY (INC_REQNT);


/* Indices definition */

CREATE INDEX PURCHREQTAGS_BYREQN ON PURCHREQTAGS (INC_REQN);
CREATE INDEX PURCHREQTAGS_BYREQNB ON PURCHREQTAGS (INC_REQNB);


select RDB$RELATION_NAME, RDB$INDEX_NAME, RDB$STATISTICS
from RDB$INDICES
where RDB$RELATION_NAME in( 'PURCHREQUISITION', 'PURCHREQTAGS' )

returns :

RDB$RELATION_NAME RDB$INDEX_NAME RDB$STATISTICS
PURCHREQTAGS RDB$PRIMARY54 0.000
PURCHREQTAGS PURCHREQTAGS_BYREQN 0.000
PURCHREQTAGS PURCHREQTAGS_BYREQNB 0.000
PURCHREQUISITION RDB$PRIMARY47 0.000
PURCHREQUISITION PURCHREQ_BYSITEACTION 0.143
PURCHREQUISITION PURCHREQ_BYFULFILFROM 0.167
PURCHREQUISITION PURCHREQ_BYPART 0.000
PURCHREQUISITION PURCHREQ_BYACTIONBY 0.031
PURCHREQUISITION PURCHREQ_BYACTIONDTE 0.000
PURCHREQUISITION PURCHREQ_BYREQUIREDDTE 0.003
PURCHREQUISITION PURCHREQ_BYREQUESTER 0.008
PURCHREQUISITION PURCHREQ_BYREQUEST 0.000
PURCHREQUISITION PURCHREQ_BYDESTINDEX 0.000



Here are the queries ( simplified ) :

select pr.Inc_Reqn, prt.Inc_Reqn
from PurchReqTags prt
left join PurchRequisition pr on ( pr.Inc_Reqn = prt.Inc_Reqn )
where prt.Inc_ReqnB = :AiInc_ReqnB
and pr.Actioned = 'N'
and pr.FulFilSuppNo = :sSuppNo

PLAN JOIN (PRT INDEX (PURCHREQTAGS_BYREQNB),PR INDEX (INTEG_117))

and

select pr.Inc_Reqn, prt.Inc_Reqn
from PurchReqTags prt
inner join PurchRequisition pr on ( pr.Inc_Reqn = prt.Inc_Reqn )
where prt.Inc_ReqnB = :AiInc_ReqnB
and pr.Actioned = 'N'
and pr.FulFilSuppNo = :sSuppNo

PLAN JOIN (PR NATURAL,PRT INDEX (PURCHREQTAGS_BYREQNB,
PURCHREQTAGS_BYREQN))

Now what makes this worse is if I use this query ( take out the
params, and hard code the values ) :

select pr.Inc_Reqn, prt.Inc_Reqn
from PurchReqTags prt
inner join PurchRequisition pr on ( pr.Inc_Reqn = prt.Inc_Reqn )
where prt.Inc_ReqnB = 22372
and pr.Actioned = 'N'
and pr.FulFilSuppNo = 'WYK003'

PLAN JOIN (PRT INDEX (PURCHREQTAGS_BYREQNB),PR INDEX (INTEG_117))

It now uses the smae plan as the left join.

I think this is all the information you asked for.

I hope you could shed some light on this for me.

Thanks
Andrew Monteith