Miggins wrote:
> 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
> 5 | INDEX RANGE SCAN | TRA_CODE_TYPE
> 6 | INDEX RANGE SCAN | TRAD_PK
In addition to the suggestions others have offered about re-working the
query, I'm struck by the fact that it "is called around 20,000 times
and the amount of data it selects is very small " Is it *really*
necessary to call it that many times? Can the query be moved outside
of whatever loop is executing 20k times, and the results stored in
PL/SQL variables?
Received on Tue Mar 07 2006 - 07:38:38 CST
