Execute time of Query 1 < Query 2.

Database is Firebird 2.0.1


Query 1
-------

SELECT
CA_OTHER_PAYMENT_SUB.ITEM_CODE AS ACCOUNT_CODE
,M_ACC_CHART.ACCOUNT_NAME
,CA_OTHER_PAYMENT.OTHERPAY_DATE AS DOC_DATE
,SUM(CA_OTHER_PAYMENT_SUB.ITEM_AMOUNT) AS
ACCOUNT_AMOUNT

FROM CA_OTHER_PAYMENT

INNER JOIN CA_OTHER_PAYMENT_SUB ON

CA_OTHER_PAYMENT.COMP_ID||CA_OTHER_PAYMENT.OTHERPAY_COD
E

=CA_OTHER_PAYMENT_SUB.COMP_ID||CA_OTHER_PAYMENT_SUB.OTH
ERPAY_CODE

INNER JOIN M_ACC_CHART ON

CA_OTHER_PAYMENT_SUB.COMP_ID||CA_OTHER_PAYMENT_SUB.ITEM_
CODE
=M_ACC_CHART.COMP_ID||M_ACC_CHART.ACCOUNT_CODE

WHERE
CA_OTHER_PAYMENT.COMP_ID = '1'
AND CA_OTHER_PAYMENT.OTHERPAY_DATE BETWEEN '01-SEP-2007'
AND '30-SEP-2007'

GROUP BY 1,2,3

Plan
----
PLAN SORT (MERGE (SORT (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT))
, SORT (MERGE (SORT (M_ACC_CHART NATURAL), SORT
(CA_OTHER_PAYMENT_SUB NATURAL)))))

Adapted Plan
------------
PLAN SORT (MERGE (SORT (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT))
, SORT (MERGE (SORT (M_ACC_CHART NATURAL), SORT
(CA_OTHER_PAYMENT_SUB NATURAL)))))



Query 2
-------

SELECT
CA_OTHER_PAYMENT_SUB.ITEM_CODE AS ACCOUNT_CODE
,M_ACC_CHART.ACCOUNT_NAME
,CA_OTHER_PAYMENT.OTHERPAY_DATE AS DOC_DATE
,SUM(CA_OTHER_PAYMENT_SUB.ITEM_AMOUNT) AS
ACCOUNT_AMOUNT

FROM CA_OTHER_PAYMENT

INNER JOIN CA_OTHER_PAYMENT_SUB ON
CA_OTHER_PAYMENT.COMP_ID=CA_OTHER_PAYMENT_SUB.COMP_ID
AND
CA_OTHER_PAYMENT.OTHERPAY_CODE=CA_OTHER_PAYMENT_SUB.OT
HERPAY_CODE

INNER JOIN M_ACC_CHART ON
CA_OTHER_PAYMENT.COMP_ID=M_ACC_CHART.COMP_ID
AND
CA_OTHER_PAYMENT_SUB.ITEM_CODE=M_ACC_CHART.ACCOUNT_COD
E

WHERE
CA_OTHER_PAYMENT.COMP_ID = '1'
AND CA_OTHER_PAYMENT.OTHERPAY_DATE BETWEEN '01-SEP-2007'
AND '30-SEP-2007'
GROUP BY 1,2,3

Plan
----
PLAN SORT (JOIN (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT)
, M_ACC_CHART INDEX (PK_M_ACC_CHART),
CA_OTHER_PAYMENT_SUB INDEX (FK_CA_OTHER_PAYMENT_SUB_1,
CA_OTHER_PAYMENT_SUB_IDX1)))

Adapted Plan
------------
PLAN SORT (JOIN (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT)
, M_ACC_CHART INDEX (PK_M_ACC_CHART),
CA_OTHER_PAYMENT_SUB INDEX (FK_CA_OTHER_PAYMENT_SUB_1,
CA_OTHER_PAYMENT_SUB_IDX1)))