Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help improving the performance of a query - please
Hi.
It's a wild guess without enough info ( too many possibilities ). Is it possible for you to post:
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.
In article <3843fc6b.26819086_at_NEWS>,
JOC (JOC) wrote:
> 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
>
> Jo
>
> *************************************************
>
> The usual - anything I say reflects on me, and not upon my current
employer.
>
> *************************************************
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 30 1999 - 15:33:03 CST