Re: Help improving the performance of a query - please

On Tue, 30 Nov 1999 21:33:03 GMT, michael_bialik_at_my-deja.com wrote:

>Hi.
>
> It's a wild guess without enough info ( too many possibilities ).
> Is it possible for you to post:
> 1. TKPROF output of PROD ( to see where from all these rows
> are fetched ).
> 2. Output of
> SELECT table_name, index_name, column_name
> FROM user_ind_columns
> WHERE table_name IN ('RECEIVED_RECORDS','FILE_TYPES',
> 'RECEIVED_FILES' )
> ORDER BY 1,2,column_position;
> 3. Mark UNIQUE indexes for each table.
>
> Anyway, I'm going to make a guess:
> If RECEIVED_FILE_ID field is a primary key ( or unique identifier )
> of 'RECEIVED_FILES table then you may eliminate multiple joins to
> RECEIVED_RECORDS table by using:
>
> SELECT RF.RECEIVED_FILE_ID
> FROM
> FILE_TYPES FT,
> RECEIVED_FILES RF
> WHERE
> 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 EXISTS ( SELECT 1 FROM RECEIVED_RECORDS RR
> WHERE
> RF.RECEIVED_FILE_ID = RR.RECEIVED_FILE_ID
> 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
>
> Check index 'RF_FS_FK' selectivity is good.
>
> HTH. Michael.
>
>

Thank you so much. The reason I didn't post tkprof from prod is that the client didn't supply it - everything was zero. They do this to make it more interesting for me.

I did finally get around to thinking of that split. In fact I think I might have found a was to remove file_types from the query entirely. Thank you to everyone who replied

Jo

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