Re: Query takes too long to execute

Aleks wrote:
> mylesv_at_gmail.com wrote:
> > dbms_stats.gather_system_stats did the trick. The query still does a
> > full scan of ICAS_PRODUCTION, but it finishes in seconds instead of
> > minutes. I'm not sure I understand this, but I'm happy it's back to
> > normal!
> >
> > Thanks,
> > Myles
>
> Try this:
>
> SELECT
> (select C.CREATE_DATE
> from ICAS_WMS_CARD_REQUEST C
> 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
> ) create_date
> , P.PRODUCTION_ID, P.CARD_STATE, P.BATCH_ID
> FROM ICAS_PRODUCTION P
> ORDER BY 1
I was wrong. The first thought is wrong,You don't looking for all rows:

SELECT

       (select CREATE_DATE
          from ICAS_WMS_CARD_REQUEST
          where WMS_CARD_REQUEST_ID = P.WMS_CARD_REQUEST_ID
        ) create_date
     , P.PRODUCTION_ID, P.CARD_STATE, P.BATCH_ID

                   in (select WMS_CARD_REQUEST_ID
                          from ICAS_WMS_CARD_REQUEST C
                          where C.CREATE_DATE BETWEEN trunc(sysdate -
3) AND sysdate
                           and C.CARD_DELIVERY_CODE = 'M' )

ORDER BY 1 Received on Fri Nov 17 2006 - 17:52:11 CST