Hi. I'm trying to optimize a query that joins a master table with additional information from 4 other tables, and sorting on a date field.

Here is my query

SELECT
T.TransNo, T.TransDate, T.VoucherNo,
T.PayerNo, P.PayerNumber,
T.PaymentNo, Pm.PaymentName,
T.SupplementText,
T.PayTypeNo, Pt.PayTypeName,
T.DebitAmount, T.CreditAmount,
T.StudentNo, S.StudentNumber
FROM Trans T
LEFT OUTER JOIN Student S ON T.StudentNo = S.StudentNo
JOIN Payer P ON T.PayerNo = P.PayerNo
JOIN Payment Pm ON T.PaymentNo = Pm.PaymentNo
JOIN PayType Pt ON T.PayTypeNo = Pt.PayTypeNo
ORDER BY TransDate

I've got a descending index on TRANSDATE (DDL below) but not an ascending so I was wondering why doing

ORDER BY TransDate ASC

Wasn't faster than doing

ORDER BY TransDate DESC

So I looked at the automatically generated plan (using FlameRobin), realizing that the optimizer didn't use the index on TRANSDATE at all:


PLAN SORT (JOIN (JOIN (T NATURAL, S INDEX (STUDENT_PK)), P INDEX (PAYER_PK), PM INDEX (PAYMENT_PK), PT INDEX (PAYTYPE_PK)))

Either way.


Trying to simplify the query, I remove 2 joins:

SELECT
T.TransNo, T.TransDate, T.VoucherNo,
T.PayerNo, P.PayerNumber,
T.PaymentNo, Pm.PaymentName,
T.SupplementText,
T.PayTypeNo,
T.DebitAmount, T.CreditAmount,
T.StudentNo
FROM Trans T
JOIN Payer P ON T.PayerNo = P.PayerNo
JOIN Payment Pm ON T.PaymentNo = Pm.PaymentNo
ORDER BY TransDate

Now the plan is finally using the TRANS_DATE_IX index:

PLAN JOIN (T ORDER TRANS_DATE_IX, P INDEX (PAYER_PK), PM INDEX (PAYMENT_PK))

I couldn't find any combination of 3 joins, where the TRANS_DATE_IX is utilized. But any combination of 2 joins like above will.

Now trying to change the all joins into left outer joins

SELECT
T.TransNo, T.TransDate, T.VoucherNo,
T.PayerNo, P.PayerNumber,
T.PaymentNo, Pm.PaymentName,
T.SupplementText,
T.PayTypeNo, Pt.PayTypeName,
T.DebitAmount, T.CreditAmount,
T.StudentNo, S.StudentNumber
FROM Trans T
LEFT OUTER JOIN Student S ON T.StudentNo = S.StudentNo
LEFT OUTER JOIN Payer P ON T.PayerNo = P.PayerNo
LEFT OUTER JOIN Payment Pm ON T.PaymentNo = Pm.PaymentNo
LEFT OUTER JOIN PayType Pt ON T.PayTypeNo = Pt.PayTypeNo
ORDER BY TransDate

Gives me the plan I'm after:

PLAN JOIN (JOIN (JOIN (JOIN (T ORDER TRANS_DATE_IX, S INDEX (STUDENT_PK)), P INDEX (PAYER_PK)), PM INDEX (PAYMENT_PK)), PT INDEX (PAYTYPE_PK))

And the query runs 10 times faster! Since the relations I changed are secured by referential integrity, there should be no difference (?) so I'm a surprised, that the optimizer couldn't figure that out.

Should I really change all my joins where the joining relation is secured by referential integrity into left outer joins?

Or is there something I don't understand here?

Thanks!
Jacob


CREATE TABLE TRANS
(
TRANSNO Integer NOT NULL,
TRANSDATE Date NOT NULL,
VOUCHERNO Integer,
SUPPLEMENTTEXT Varchar(30),
PAYMENTNO Integer NOT NULL,
PAYTYPENO Integer NOT NULL,
USERNO Integer,
DEBITAMOUNT Decimal(18,2),
CREDITAMOUNT Decimal(18,2),
STORED Integer DEFAULT 0 NOT NULL,
STUDENTNO Integer,
FITRANSNO Integer,
PBSTRANSNO Integer,
FILENAME Varchar(12),
CONSTRAINT TRANS_PK PRIMARY KEY (TRANSNO)
);
ALTER TABLE TRANS ADD CONSTRAINT TRANS_FITRANS_FK
FOREIGN KEY (FITRANSNO) REFERENCES FITRANS (FITRANSNO);
ALTER TABLE TRANS ADD CONSTRAINT TRANS_PAYER_FK
FOREIGN KEY (PAYERNO) REFERENCES PAYER (PAYERNO);
ALTER TABLE TRANS ADD CONSTRAINT TRANS_PAYMENT_FK
FOREIGN KEY (PAYMENTNO) REFERENCES PAYMENT (PAYMENTNO);
ALTER TABLE TRANS ADD CONSTRAINT TRANS_PAYTYPE_FK
FOREIGN KEY (PAYTYPENO) REFERENCES PAYTYPE (PAYTYPENO);
ALTER TABLE TRANS ADD CONSTRAINT TRANS_PBSTRANS_FK
FOREIGN KEY (PBSTRANSNO) REFERENCES PBSTRANS (PBSTRANSNO);
ALTER TABLE TRANS ADD CONSTRAINT TRANS_STUDENT_FK
FOREIGN KEY (STUDENTNO) REFERENCES STUDENT (STUDENTNO) ON DELETE SET NULL;
ALTER TABLE TRANS ADD CONSTRAINT TRANS_USERS_FK
FOREIGN KEY (USERNO) REFERENCES USERS (USERNO) ON DELETE SET NULL;

CREATE INDEX TRANS_DATE_IX ON TRANS (TRANSDATE);