any ideas for better performance of this query ?

Hi all,

I have the following query which is current taking 2 + mins

select m.member_id, m.primary_email, m.second_name, m.first_name, s.status, s.download_complete from member m,subscriptiondetails b,subtransaction s where
s.from_state = 3201
and m.member_id = b.sub_id
and b.subscription_id = s.subscription_id

10000 rows selected.

Elapsed: 00:02:145.49

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBTRANSACTION'
   4    3         INDEX (RANGE SCAN) OF 'SUBTRANS_FROMSTATE' (NON-UNIQ
          UE)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIPTIONDETAILS'
   6    5         INDEX (UNIQUE SCAN) OF 'SYS_C007122' (UNIQUE)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER'
   8    7       INDEX (UNIQUE SCAN) OF 'SYS_C007068' (UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
      51453  consistent gets
         23  physical reads
          0  redo size
     451148  bytes sent via SQL*Net to client
      44937  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

The table subtransaction contains around 15,00,000 records followed by subscriptiondetails containing 1000 records and member table containing 100 records .

I have created index on all the fields of the where clause but still there is not much difference in the performance

can any one give me some ideas as to in what direction should i focus for better timing of the query ?

Thanks & REgards
~Parvinder Received on Wed Oct 10 2001 - 09:00:26 CDT