Forum Stats

  • 3,872,916 Users
  • 2,266,490 Discussions
  • 7,911,383 Comments

Discussions

Query Slow performance

hi All,

Need your help to rewrite this query:


SELECT DISTINCT ooh.order_number,

           ooh.orig_sys_document_ref,

           ooh.cust_po_number,

           ooh.header_id

    FROM oe_order_headers_all ooh

    WHERE   ooh.order_number = NVL ( :ordernumber, ooh.order_number)

      AND ooh.orig_sys_document_ref = NVL ( :orgsysref, ooh.orig_sys_document_ref)

       AND NVL(ooh.cust_po_number,'X') = NVL ( :custponum, NVL(ooh.cust_po_number,'X'))


This query when given only the order_number parameter takes a lot of time compared to when the below join is commented:

AND ooh.orig_sys_document_ref = NVL ( :orgsysref, ooh.orig_sys_document_ref).

Need to understand the reason why and how to rectify this.

Tagged: