Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query takes too long to execute
<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