Query takes too long to execute

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 Received on Wed Nov 15 2006 - 20:27:52 CST