Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query takes too long to execute

Re: Query takes too long to execute

From: Jim Kennedy <jim>
Date: Wed, 15 Nov 2006 19:16:52 -0800
Message-ID: <sMWdneiFUezZSsbYnZ2dnUVZ_vmdnZ2d@comcast.com>

<mylesv_at_gmail.com> wrote in message
news:1163644072.103180.85040_at_e3g2000cwe.googlegroups.com...
> I have a query that's begun to take an unacceptable amount of time to
> complete:
>
>
>
> SELECT C.CREATE_DATE, P.PRODUCTION_ID, P.CARD_STATE, P.BATCH_ID
> FROM ICAS_WMS_CARD_REQUEST C, ICAS_PRODUCTION P
> WHERE C.CREATE_DATE BETWEEN trunc(sysdate - 3) AND sysdate
> AND C.CARD_DELIVERY_CODE = 'M'
> AND P.WMS_CARD_REQUEST_ID = C.WMS_CARD_REQUEST_ID
> ORDER BY C.CREATE_DATE
>
>
>
> Here's what "explain plan" shows:
>
>
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 331 K 45357
>
> SORT ORDER BY 331 K 8 M 45357
> FILTER
> HASH JOIN 331 K 8 M 43601
> TABLE ACCESS BY INDEX ROWID ICAS_WMS_CARD_REQUEST 331 K 4 M
> 24476
> INDEX RANGE SCAN ICAS_CR_CRD_IDX 119 K 452
>
> TABLE ACCESS FULL ICAS_PRODUCTION 13 M 189 M 13569
>
>
>
>
> "WMS_CARD_REQUEST_ID" is indexed in table "ICAS_PRODUCTION", yet Oracle
> is finding that a table scan is more efficient that using an index?
> This is a non-unique index, yet there are no duplicate values in this
> column. Would making this a unique index help? Sorry about the line
> wrap in the "explain plan" results.
>
> Oracle 9i EE (9.2.0.1.0)
>
> Thanks!
> M
>

1. If you take the order by off does the query take a lot less time? If so then the sort is going to disk. The time difference would be the cost of sorting the results. You could make the sort area size larger if that is the issue.
2. Are the tables and indexes analyzed? Received on Wed Nov 15 2006 - 21:16:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US