I have been trying to speed up the following query but havent been successfull.
I ran the timings on the following query using the first_rows hint and without it. The elapsed time was same ,. The query did not bring back the results till more than an

hour. We have a requirement that the query should response within 1 minutes or a timeout will happen.Im my case First_rows hint is not working. I've tried using all the

possible indexes to avoid the full table scan. When I do that the cost goes up and it takes more time to run. Please help
The query is as follows:


SELECT TO_CHAR(payment.SettlementDt, 'MM/DD/YYYY') AS PmtDate,
SUM(DECODE(payment.ProvId, 1, 1, 0)) as ACHCount,
SUM(DECODE(payment.ProvId,1, PmtAmt, 0)) as ACHSubTot,
SUM(DECODE(payment.ProvId, 1, 0, 1)) as CCCount,
SUM(DECODE(payment.ProvId,1, 0, PmtAmt)) as CCSubTot,
COUNT(payment.PmtAmt) AS Count,SUM(payment.PmtAmt) as Total
FROM users, payment,subsidy, account
WHERE users.Masterbillerid = 3 and
payment.LfcyclCd='SN' and
payment.PmtId=WF_PmtAux.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt

Table # of Rows
payment 10,000,000
account 1,000,000
users 108
subsidy 10,000,000

cost:96045

12 SELECT STATEMENT
11 SORT [GROUP BY]
10 HASH JOIN
1 USERS TABLE ACCESS [FULL]
9 MERGE JOIN
6 SORT [JOIN]
5 NESTED LOOPS
2 Subsidy TABLE ACCESS [FULL]
4 Payment TABLE ACCESS [BY INDEX ROWID]
3 XPKpayment INDEX [UNIQUE SCAN] -- on pmtid
8 SORT [JOIN]
7 Account TABLE ACCESS [FULL]

Description Cost Est.Rows Ret Est. KBytes Returned
1 This plan step retrieves all rows from table USERS 1 3 0.015
2 This plan step retrieves all rows from table WF_PMTAUX. 21,762 24,831 581.977
3 This plan step retrieves a single ROWID from the B*-tree index XPKpayment. 1 2,498,909 --
4 This plan step retrieves rows from table Payment through ROWID(s) returned by an index. 2 2,498,909 82,971.588
5 Nested Loops 71,424 24,831 1,406.443
6 This plan step Sort-join operation. 72,137 24,831 1,406.443
7 This plan step retrieves all rows from table account. 2,880 1,037,774 20,269.023
8 This plan step accepts a row set (its only child) and sorts it in preparation for a merge-join 23,890 1,037,774 20,269.023
9 Merge Join. 96,027 5,968 454.594
10 Hash Join 96,037 484 39.23
11 Sort GROUP BY clause. 96,045 478 38.744
12 This plan step designates this statement as a SELECT statement.

To show the indexes on the tables that can be used plan With the rule hint( BTW there are a lot more indexes on these tables)

13 SELECT STATEMENT
12 SORT [GROUP BY]
11 NESTED LOOPS
8 NESTED LOOPS
5 NESTED LOOPS
2 Payment TABLE ACCESS [BY INDEX ROWID]
1 XIF115BCPMT INDEX [RANGE SCAN] --on lfcyclcd column
4 Account TABLE ACCESS [BY INDEX ROWID]
3 ACCT1 INDEX [UNIQUE SCAN] --on account column
7 Subsidy TABLE ACCESS [BY INDEX ROWID]
6 XPKWF_subsidy INDEX [UNIQUE SCAN] -- on pmtid
10 Users TABLE ACCESS [BY INDEX ROWID]
9 XIF241users INDEX [RANGE SCAN -- ON MASTERBILLERID column


Please help.

Thanks