Re: Query takes too long to execute

mylesv_at_gmail.com wrote:
> 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

Did you change optimizer_index_cost_adj from its default 100 to 40? Did you change optimizer_index_caching from 0 to 90? Or did you calculate system stats using dbms_stats.gather_system_stats. This behavior is quite common for non-driving tables with default values for those parameters.
As the value of the key can't be gathered from the statement, default cardinality is assumed.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA