Help improving the performance of a query

From: <JOC>
Date: Tue, 30 Nov 1999 16:46:13 GMT
Message-ID: <3843fc6b.26819086@NEWS>

My brain is obviousky in off mode today, so I was wondering if any bright sparks out there could help - I'd really appreciate it. I need to tune the following statement. This is on Oracle 7.3.4 and the optimiser is rule-based (well choose, but nothing is analysed), on HP-UX.
Table rr has about 55 million rows in it, rf about 67 thousand (both dynamic), and ft is static with about 100 rows. (this query is not used for a batch job, and the user is currently sitting at the computer for 3 - 4 minutes an update). -

SELECT DISTINCT RF.RECEIVED_FILE_ID
FROM

 	RECEIVED_RECORDS RR,
 	FILE_TYPES FT,
	RECEIVED_FILES RF  
WHERE 
  	RF.RECEIVED_FILE_ID = RR.RECEIVED_FILE_ID  
	AND RF.FILE_TYPE_ID = FT.FILE_TYPE_ID 
	 AND RF.SENDING_PARTICIPANT_ID = :b1  
	AND RF.FILE_STATE = 'PP'  
	AND FT.PARTICIPANT_ROLE_ID LIKE  NVL(:b2,'%')  
	AND FT.DATA_FLOW_ID = :b3  
	AND RR.REJECTION_REASON_ID = :b4  
	AND RR.RECORD_STATE_ID = :b5 
	 AND RR.REJECTION_DATA = TO_CHAR(:b6) 
	ORDER BY RF.RECEIVED_FILE_ID

and the explain plan looks something like

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      0   SORT (UNIQUE)
      0    NESTED LOOPS
    107     NESTED LOOPS
   1399      TABLE ACCESS (BY ROWID) OF 'RECEIVED_FILES'
   1400       INDEX (RANGE SCAN) OF 'RF_FS_FK' (NON-UNIQUE)
   1139      TABLE ACCESS (BY ROWID) OF 'FILE_TYPES'
   1139       INDEX (UNIQUE SCAN) OF 'FT_PK' (UNIQUE)
      6     TABLE ACCESS (BY ROWID) OF 'RECEIVED_RECORDS'
    113      INDEX (RANGE SCAN) OF 'RL_PK' (UNIQUE)

(this done on our small db, the times on the client are

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 69.96 1098.91 20735 1799134 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 10 69.96 1098.91 20735 1799134 0 0

Thanks for any help

The usual - anything I say reflects on me, and not upon my current employer.

Received on Tue Nov 30 1999 - 10:46:13 CST

Help improving the performance of a query - please