Have you tried using the EXPLAIN PLAN FOR on that query to make sure the
right indexes are being used?
<gcm281p_at_my-deja.com> wrote in message news:82jf28$hh8$1_at_nnrp1.deja.com...
> I'm having a problem with a query in a stored
> procedure. When I run the query in SQL*Plus or
> any other tool it runs in about 1 second. When I
> put the query in a stored procedure as a cursor
> the execution time jumps to about an hour.
> Anybody have any suggestions on where to look?
>
> Thanks,
> Greg
>
> Here's the query just in case...
>
> SELECT LC.HSC_ID HSC_ID,
> OT.ORDER_ID ORDER_ID,
> OT.ORDER_QTY ORDER_QTY,
> OT.DAY_DT TRANSACTION_DT,
> nvl(-1*(ORR.TRANSACTION_AMT +
> OS.LIABILITY_KASH_AMT),0) POINTS_QTY,
> OT.ITEM_ID ITEM_ID,
> ORR.TRANSACTION_AMT TRANSACTION_AMT,
> OS.LIABILITY_KASH_AMT LIABILITY_KASH_AMT
> FROM IWADMIN.ORDER_TRANSACTION_at_iwprod OT,
> IWADMIN.ORDER_RETURN_at_iwprod ORR,
> IWADMIN.ORDER_SHIPMENT_at_iwprod OS,
> LOYADMIN.LOY_CUSTOMER_at_obsprod LC,
> IWADMIN.CUSTOMER_NUMBER_XREF_at_iwprod XR
> WHERE OT.CUSTOMER_ID = XR.CUSTOMER_ID
> AND LC.HSC_ID BETWEEN v_low_limit AND v_up_limit
> AND LC.HSC_ID = XR.SUBSIDIARY_CUSTOMER_ID
> AND OT.ORDER_ID = OS.ORDER_ID
> AND OT.ORDER_ID = ORR.ORDER_ID(+)
> AND (ORR.TRANSACTION_AMT+OS.LIABILITY_KASH_AMT)>0
> AND OT.DAY_DT >= LC.START_DT
> AND OT.DAY_DT <= v_end_dt
> AND OT.STATUS_CODE = 'P'
> AND (ORR.RETURN_REASON_CODE IS NULL OR
> ORR.RETURN_REASON_CODE IN
> (SELECT RETURN_REASON_CODE
> FROM iwadmin.RETURN_REASON_REF_at_iwprod))
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 08 1999 - 12:14:22 CST
