Tuning Question......

Hi,

Have the following query producing the plan shown. This query is being executed upwards of 20,000 times each run. The TRANSACTIONS table contains 12,000,000 rows and the TRANSACTION_DETAILS table 19,000,000. Its taking way too long to run.

Any suggestions on improving the efficiency of the query would be greatly appreciated.

The primary key on TRANSACTIONS is on
TRA_CODE
TRA_TYPE The primary key on TRANSACTION_DETAILS is on TRAD_TRANS_NO
TRAD_LINE_NO select sum(nvl(trad_debit,0) - nvl(trad_credit,0)) from transactions tra,
transaction_details trd
where tra.tra_type = v_type_tab(si)

and tra.tra_code = v_code_tab(si)
and tra.origin = '7'
and tra.trans_no = trd.trad_trans_no
and trd.trad_product_code = v_journal_code
and trd.trad_start_date <= p_issue_date
and trd.trad_period_end_date >= p_issue_date;

0 | SELECT STATEMENT |
1 | SORT AGGREGATE |
2 | TABLE ACCESS BY INDEX ROWID | TRANSACTION_DETAILS
3 | NESTED LOOPS |
4 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS