Hi All, This is Wasim Ahmed. I basically wanted a kick start to tune my SQL Queries for performance. I have a huge query which is taking an average of 30seconds for records in thousands. I want to improve the efficiency of the Query. Can any one help me in this. I am pasting my SQL query. I would be greatfull if you can mail me your personal email id to wase_ahmed@hotmail.com so that i can share my PLSql procedure.. Thank you. Best Regards Wasim Ahmed. SELECT * FROM (SELECT ohs.ohs_order_no "SOH_ORDER_NO", ohs.cus_code_dlvy "CUS_CODE_DLVY", cus.cus_name "CUS_NAME", ohs.ohs_doc_ref "SOH_CUSTOMER_DOC_REF", NVL (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format), ' ') "SOH_CUSTOMER_ORDER_DATE", ohs.rep_entity_id "REP_ENTITY_ID", ohs.rev_entity_id "REV_ENTITY_ID", ohs.ohs_status "SOH_STATUS", cm.acm_code_desc "SOH_STATUS_DESCRIPTION", pos.prm_id "PRM_ID", prm.prm_name "PRM_NAME", ohs.ohs_transaction_type "OHS_TRANSACTION_TYPE", ' ' "ACCESSLEVEL", ods.sup_code "SUP_CODE", ohs.ohs_order_classification "SOH_ORDER_CLASSIFICATION" FROM ORDER_HEADER_SCRATCH ohs, CUSTOMER_MASTER cus, CODE_MASTER cm, (SELECT MAX (prm_id) AS prm_id, pos_order_no FROM PROCESS_ORDER_STATUS GROUP BY pos_order_no) pos, PROCESS_MASTER prm, (SELECT ods.ohs_order_no, ods.sup_code FROM ORDER_HEADER_SCRATCH ohs, ORDER_DETAIL_SCRATCH ods WHERE ohs.ohs_order_no(+) = ods.ohs_order_no AND ohs.ohs_status <> 1 GROUP BY ods.ohs_order_no, ods.sup_code) ods WHERE ohs.cus_code_dlvy = cus.cus_code AND ohs.ohs_status LIKE l_orderstatus AND ohs.ohs_order_no LIKE l_orderno AND UPPER (ohs.ohs_transaction_type) = 'SO' AND ohs.ohs_status = cm.acm_code_value AND cm.acm_code_fldname = 'SO_HEADER_STATUS' AND ohs.ohs_order_no = pos.pos_order_no(+) AND prm.prm_id(+) = pos.prm_id -- Dummy order number to suppress the order when despatch note number is given AND ohs.ohs_order_no LIKE l_dummyordernumber -- New Requirment for Simple Search for Sup Code AND ohs.ohs_order_no = ods.ohs_order_no(+) AND UPPER (NVL (ods.sup_code, ' ')) LIKE UPPER (l_supcode) AND ohs.rev_entity_id IN (SELECT t_reventityid FROM TEMP_REVENTITY) AND ohs.rep_entity_id IN (SELECT t_repentityid FROM TEMP_REPENTITY) AND UPPER (NVL (ohs.cus_code_dlvy, ' ')) LIKE UPPER (l_cuscode) AND UPPER (NVL (cus.cus_name, ' ')) LIKE UPPER (l_cusname) AND UPPER (NVL (ohs.ohs_doc_ref, ' ')) LIKE UPPER (l_cusrefno) AND ( (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format)) >= l_orderdatefrom AND ohs.ohs_order_date IS NOT NULL ) AND ( (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format)) <= l_orderdateto AND ohs.ohs_order_date IS NOT NULL ) UNION ALL (SELECT DISTINCT soh.soh_order_no "SOH_ORDER_NO", soh.cus_code_dlvy "CUS_CODE_DLVY", cus.cus_name "CUS_NAME", soh.soh_customer_doc_ref "SOH_CUSTOMER_DOC_REF", NVL (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format), ' ' ) "SOH_CUSTOMER_ORDER_DATE", soh.rep_entity_id_orig "REP_ENTITY_ID", soh.rev_entity_id "REV_ENTITY_ID", soh.soh_status "SOH_STATUS", cm.acm_code_desc "SOH_STATUS_DESCRIPTION", pos.prm_id "PRM_ID", prm.prm_name "PRM_NAME", 'SO' "OHS_TRANSACTION_TYPE", ' ' "ACCESSLEVEL", sod.sup_code "SUP_CODE", soh.soh_order_classification "SOH_ORDER_CLASSIFICATION" FROM SO_HEADER soh, CUSTOMER_MASTER cus, CODE_MASTER cm, (SELECT MAX (prm_id) AS prm_id, pos_order_no FROM PROCESS_ORDER_STATUS GROUP BY pos_order_no) pos, PROCESS_MASTER prm, (SELECT sod.soh_order_no, sod.sup_code, sod.rev_entity_id FROM SO_HEADER soh, SO_DETAIL sod WHERE soh.soh_order_no(+) = sod.soh_order_no AND soh.rev_entity_id = sod.rev_entity_id AND soh.soh_status <> 1 GROUP BY sod.soh_order_no, sod.sup_code, sod.rev_entity_id) sod, (SELECT pic.soh_order_no, pic_picklist, pic.rev_entity_id FROM PICKLIST_MASTER pic WHERE (TO_CHAR (date_last_modified, 'YYYY-MM-DD HH:MI:SS PM')) IN ( SELECT MAX (TO_CHAR (date_last_modified, 'YYYY-MM-DD HH:MI:SS PM')) FROM PICKLIST_MASTER pic1 WHERE pic1.soh_order_no = pic.soh_order_no AND pic1.sod_line_number = pic.sod_line_number AND pic1.rev_entity_id = pic.rev_entity_id GROUP BY pic1.pic_picklist) GROUP BY pic.pic_picklist, pic.soh_order_no, pic.rev_entity_id) pic WHERE soh.cus_code_dlvy = cus.cus_code AND soh.soh_status LIKE l_orderstatus AND soh.soh_order_no LIKE l_orderno AND UPPER (soh.soh_transaction_type) = 'SO' AND soh.soh_status = cm.acm_code_value AND cm.acm_code_fldname = 'SO_HEADER_STATUS' AND soh.soh_order_no = pos.pos_order_no(+) AND pos.prm_id = prm.prm_id(+) -- Despatch Note Conditions AND pic.soh_order_no(+) = sod.soh_order_no -- New Simple Search change Sup Code AND soh.soh_order_no = sod.soh_order_no(+) AND soh.rev_entity_id = sod.rev_entity_id AND UPPER (NVL (sod.sup_code, ' ')) LIKE UPPER (l_supcode) AND soh.rev_entity_id IN (SELECT t_reventityid FROM TEMP_REVENTITY) AND soh.rep_entity_id_orig IN (SELECT t_repentityid FROM TEMP_REPENTITY) AND UPPER (NVL (soh.cus_code_dlvy, ' ')) LIKE UPPER (l_cuscode) AND UPPER (NVL (cus.cus_name, ' ')) LIKE UPPER (l_cusname) AND UPPER (NVL (soh.soh_customer_doc_ref, ' ')) LIKE UPPER (l_cusrefno) -- Despatch note AND NVL (pic.pic_picklist, ' ') LIKE l_despatchnotenumber AND ( (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format)) >= l_orderdatefrom AND soh.soh_customer_order_date IS NOT NULL ) AND ( (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format)) <= l_orderdateto AND soh.soh_customer_order_date IS NOT NULL )) ORDER BY soh_order_no) WHERE ROWNUM < Agsoracleconstant.l_record_count;

Software/Hardware used: