Hello, I think this is one to read... but only for people with enough time
to spend. I prefer the explicit join syntax but the point is: a foreign key
makes IB pick the wrong index. I suggested changing a condition of the form
tableA.field = tableB.field
that uses the wrong index to be rewritten as
not tableA.field <> tableB.field
so it gets no priority from the optimizer.

C.

austin <austin@...> wrote in message
<8c2sn0$r9b$1@...>...

> Hi all,
> I removed the foreign key constraint in TxClaimResult, and that made
> Interbase use the index
> instead of the foreign key index RDB$FOREIGN140; ( refer to plan below )
> now the query takes 0.24 secs. However, some tables will need
> foreign key constraints, so is there a way to tell Interbase to use the
> index and not the 'foreign key' index.
> Thanks.
>
>
> austin <austin@...> wrote in message
> news:8c15a0$act$1@......
> > Hi, this is a seemingly simple query, and I was wondering why it should
> take
> > 20 secs. Why in particular does not Interbase use the index for the
> > Patients, Products and Prescriber tables; all have column PkId and they
> are
> > indexed ??
> > Thanks.
> >
> > The Sql and the plan : -
> > --------------------------
> > select TxClaimResult.TxKey, TxClaimResult.PayOrder,
> TxClaimResult.ClaimKey,
> > TxClaimResult.Status, TxClaimResult.Recv, TxClaimResult.PkId,
> > TxClaimResult.PlanKey, Tx.Filldate,
> > Claims.C3301, Patients.FirstName, Patients.LastName, Patients.Birthdate,
> > Tx.RxKey, Claims.D5603, Tx.FillQuantity, Prescribers.License,
> > Claims.C3103 || ' ' || Claims.C3203,
> > Claims.D6403, Claims.C3503,
> > Rx.Selector, Tx.Cost, Tx.UpCharge, Tx.FeeCharged, Tx.Retail,
> > Plans.Name, TxClaimResult.Messages, Claims.C3603, TxClaimResult.Amount,
> > Plans.RealTime,TxClaimResult.ClaimType, Claims.ClaimDate,
> Products.LabelName
> > from Claims, TxClaimResult, Tx, Patients, Prescribers, Products, Rx,

Plans

> > where Claims.ClaimDate >= '3/30/2000' and
> > Claims.ClaimDate <= '3/30/2000' and
> > TxClaimResult.ClaimKey = Claims.PkID and
> > TxClaimResult.PayOrder = Claims.PayOrder and
> > Plans.PkId = TxClaimResult.PlanKey and
> > Tx.PkId = TxClaimResult.TxKey and
> > Rx.PkId = Tx.RxKey and
> > Patients.PkId = Tx.PatientKey and
> > Prescribers.PkId = Tx.PrescriberKey and
> > Products.PkId = Tx.ProductKey
> > order by TxClaimResult.TxKey, TxClaimResult.ClaimKey,
> > TxClaimResult.PayOrder, Claims.ClaimDate;
> >
> > SORT (MERGE (SORT (PATIENTS NATURAL),SORT (MERGE (SORT (PRESCRIBERS
> > NATURAL),SORT (MERGE (SORT (PRODUCTS
> >
> > NATURAL),SORT (JOIN (PLANS NATURAL,TXCLAIMRESULT INDEX

(RDB$FOREIGN140),TX

> > INDEX (RDB$PRIMARY67),RX
> >
> > INDEX (RDB$PRIMARY50),CLAIMS INDEX (RDB$PRIMARY16,I_4_CLAIMS)))))))))
> >
> >
> > Current memory = 1997484
> > Delta memory = 78826
> > Max memory = 3379822
> > Elapsed time= 22.64 sec
> > Buffers = 256
> > Reads = 50070
> > Writes 0
> > Fetches = 882282
> >
> >
>
>
>
>