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);
