Skip to Main Content

SQL Developer Data Modeler

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query Slow performance

SadasivaJul 19 2022

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.

Comments

Post Details

Added on Jul 19 2022
0 comments
128 views