This discussion is archived
2 Replies Latest reply: Nov 15, 2012 9:20 AM by Hussein Sawwan-Oracle RSS

QUERY TOO SLOW

user615979 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points