Re: Tuning Question......

Version of Oracle is 9.2.0.4 running on Linux.

This query is called as part of a batch process. If i comment out this query the process runs in under 10 seconds. Leave it in and it takes nearly 45 minutes. Each batch run this query is called around 20,000 times and the amount of data it selects is very small but the tables themselves are quite large in 12,000,000 in TRANSACTIONS and 19,000,000 in the TRANSACTION_DETAILS

You are correct in assuming that there are not many TRAD_LINE_NO's per TRAD_TRANS_NO. Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=42)    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANSACTION_DETAILS' (
          Cost=3 Card=1 Bytes=26)

   3    2       NESTED LOOPS (Cost=9 Card=1 Bytes=42)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TRANSACTIONS' (Cos
          t=6 Card=1 Bytes=16)

   5    4           INDEX (RANGE SCAN) OF 'TRA_CODE_TYPE' (NON-UNIQUE)
           (Cost=3 Card=5)

   6    3         INDEX (RANGE SCAN) OF 'TRAD_PK' (UNIQUE) (Cost=2 Car
          d=2)

Statistics

          0  recursive calls
          0  db block gets
         47  consistent gets
          0  physical reads
          0  redo size
        326  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed