2 Replies Latest reply: Nov 15, 2012 11:20 AM by Hussein Sawwan-Oracle RSS

    QUERY TOO SLOW

    user615979
      Hi All,

      working in ERP Version :11.5.10.2

      This below query is taking too much time to retrieve data, could anyone help to make it faster or please let me know is there any other condition probably i am missing.

      Need help

      QUERY
      ---------

      select oeh.order_number
      ,(select trx_number from ra_customer_trx_all rac
      where to_char(oeh.order_number) = rac.interface_header_attribute1
      and rac.org_id = oeh.org_id
      and to_char(oel.line_id) = rac.interface_header_attribute6
      and rac.interface_header_context = 'ORDER ENTRY'
      ) invoice_number
      from oe_order_headers_all oeh,
      oe_order_lines_all oel
      where oeh.header_id = oel.header_id
      and oeh.org_id = oel.org_id
      and oeh.creation_date >= to_date(:fdate,'RRRR/MM/DD HH24:MI:SS')
      and oeh.creation_date <= to_date(:tdate,'RRRR/MM/DD HH24:MI:SS')

      Regards
      Vijay
        • 1. Re: QUERY TOO SLOW
          shreevat
          Change this

          and oeh.creation_date >= to_date(:fdate,'RRRR/MM/DD HH24:MI:SS')
          and oeh.creation_date <= to_date(:tdate,'RRRR/MM/DD HH24:MI:SS')

          to

          and oeh.creation_date BETWEEN to_date(:fdate,'RRRR/MM/DD HH24:MI:SS') AND to_date(:tdate,'RRRR/MM/DD HH24:MI:SS')

          Additionally confirm if you have index created on the column creation_date on oe_order_headers_all table and the table is analyzed regularly.

          Thanks
          Shree
          • 2. Re: QUERY TOO SLOW
            Hussein Sawwan-Oracle
            working in ERP Version :11.5.10.2

            This below query is taking too much time to retrieve data, could anyone help to make it faster or please let me know is there any other condition probably i am missing.
            https://forums.oracle.com/forums/search.jspa?threadID=&q=Slow+AND+Query&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001
            https://forums.oracle.com/forums/search.jspa?threadID=&q=Performance+AND+Query&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

            Thanks,
            Hussein