2 Replies Latest reply: Nov 14, 2012 3:54 PM by SomeoneElse RSS

    query is slow...need tuning

    967148
      Hi all
      I am using 11g database and this query is a part of a cursor declaration...Is there any scope for optimisation in this query? I appreciate your answers
       WITH
               ESN_GENEALOGY AS
              (SELECT DISTINCT G.SerialNo FROM  GENEALOGY G
                                          INNER JOIN PRODUCT P ON G.PRODUCTID=P.ID
                                          WHERE G.LASTARCHIVEDBY=I_SerialNo AND G.ACTIVE=1 AND P.PRODUCTINVENTORYTYPE=102 UNION SELECT I_SerialNo AS SerialNo FROM DUAL)
              SELECT ListParentProductID, ListCompProductNo, ListCompProductID, ListCompQuantity AS SBQty,SERIALNO,WOPRODUCTID,WORKORDERNO
              FROM
              (
              SELECT BM.PARENTPRODUCTID ListParentProductID,BM.PRODUCTNO ListCompProductNo,BM.PRODUCTID ListCompProductID, 
              BM.QUANTITY ListCompQuantity,BM.SERIALNO,SN.PRODUCTID WOPRODUCTID,SN.WORKORDERNO,I_SerialNo SN
              FROM
              (SELECT PARENTPRODUCTID,PRODUCTNO,PRODUCTID,SUM(QUANTITY) AS QUANTITY,SERIALNO
              FROM
              (
              SELECT PARENTPRODUCTID,PRODUCTNO,PRODUCTID,QUANTITY,SERIALNO
              FROM
               (
              SELECT 
              (CASE WHEN PC1.LASTARCHIVEDBY IS NULL THEN P1.ID ELSE P3.ID END) AS  PARENTPRODUCTID,
              P2.PRODUCTNO AS PRODUCTNO, 
              P2.ID AS PRODUCTID, 
              SUM(C1.QUANTITY) AS QUANTITY,
              (CASE WHEN PC1.LASTARCHIVEDBY IS NULL THEN I_SerialNo ELSE CG.SERIALNO END) AS  SERIALNO
              FROM WIP_ORDER WO
              INNER JOIN PRODUCT_COMPONENT PC ON (
              PC.PRODUCTID = WO.PRODUCTID 
              AND wo.wiporderno =I_WipOrderNo
              )
              INNER JOIN COMPONENT C on C.ID = PC.COMPONENTID
              INNER JOIN PRODUCT P1 on (
              P1.ID = C.PRODUCTID 
              AND C.EFFECTIVEDATE <= WO.SCHEDULEDSTARTDATE
              AND (C.DISCONTINUEDATE > WO.SCHEDULEDSTARTDATE or C.DISCONTINUEDATE is null)
              )
              INNER JOIN PRODUCT P on WO.PRODUCTID = P.ID 
              INNER JOIN PRODUCT_COMPONENT PC1 on  PC1.PRODUCTID = C.PRODUCTID
              INNER JOIN COMPONENT C1 on ( C1.ID = PC1.COMPONENTID
              AND C1.EFFECTIVEDATE <= WO.SCHEDULEDSTARTDATE
              AND (C1.DISCONTINUEDATE > WO.SCHEDULEDSTARTDATE OR C1.DISCONTINUEDATE is null)
              )
              INNER JOIN PRODUCT P2 on P2.ID = C1.PRODUCTID
              LEFT JOIN PRODUCT P3 ON PC1.LASTARCHIVEDBY=P3.PRODUCTNO
              LEFT JOIN  (SELECT DISTINCT G.SERIALNO, G.PRODUCTID FROM  GENEALOGY G
                                          INNER JOIN PRODUCT P ON G.PRODUCTID=P.ID
                                          WHERE G.LASTARCHIVEDBY=I_SerialNo AND G.ACTIVE=1 AND P.PRODUCTINVENTORYTYPE=102) CG ON  P3.ID=CG.PRODUCTID
              WHERE  (CASE WHEN PC1.LASTARCHIVEDBY IS NULL THEN I_SerialNo ELSE CG.SERIALNO END) IS NOT NULL
              GROUP BY   (CASE WHEN PC1.LASTARCHIVEDBY IS NULL THEN P1.ID ELSE P3.ID END) , P2.PRODUCTNO, P2.ID, 
                              (CASE WHEN PC1.LASTARCHIVEDBY IS NULL THEN I_SerialNo ELSE CG.SERIALNO END)
              ) set1
              UNION ALL
              SELECT PARENTPRODUCTID,PRODUCTNO,PRODUCTID,QUANTITY*(-1) AS QUANTITY,SERIALNO
              FROM
              (
              SELECT   A.ParentProductID,
              A.ProductNO,
              A.ProductID,
              SUM(A.Quantity) AS Quantity,
              A.SERIALNO
              FROM (
              SELECT  G.PARENTPRODUCTID,
              P.PRODUCTNO, 
              G.PRODUCTID, 
              G.QUANTITY,
              G.ID,
              G.LASTARCHIVEDBY AS SERIALNO
              FROM GENEALOGY G
              INNER JOIN PRODUCT P ON G.PRODUCTID=P.ID
              WHERE   G.LASTARCHIVEDBY IN(SELECT SERIALNO FROM  ESN_GENEALOGY) AND G.ACTIVE=1
              UNION 
              SELECT 
              ParentPartID PARENTPRODUCTID,
              P.PRODUCTNO, 
              ORIGINALPARTID PRODUCTID,
              QUANTITY QUANTITY, 
              P.ID,
              BDH.SERIALNO AS SERIALNO
              FROM COB_T_BOM_DEVIATION_HISTORY BDH
              INNER JOIN PRODUCT p ON P.id = BDH.ORIGINALPARTID
              WHERE BDH.SERIALNO IN(SELECT SERIALNO FROM  ESN_GENEALOGY)
              UNION            
              SELECT 
              ParentPartID PARENTPRODUCTID, 
              P.PRODUCTNO, 
              DEVIATIONPARTID PRODUCTID,
              -1 * QUANTITY QUANTITY,
              P.ID,
              BDH.SERIALNO AS SERIALNO 
              FROM COB_T_BOM_DEVIATION_HISTORY BDH
              INNER JOIN PRODUCT p ON P.id = BDH.DEVIATIONPARTID
              WHERE BDH.SERIALNO IN(SELECT SERIALNO FROM  ESN_GENEALOGY)
              union
              SELECT ESB.PARENTPRODUCTID PARENTPRODUCTID,
              COMPONENTPARTNUMBER PRODUCTNO, 
              COMPONENTPRODUCTID PRODUCTID, 
              QUANTITY,
              ESB.ID,
              ESB.SERIALNO AS SERIALNO
              FROM COB_T_ENGINE_SHORT_BUILD ESB
              where  ESB.SERIALNO IN(SELECT SERIALNO FROM  ESN_GENEALOGY)
              ) A
              WHERE QUANTITY <> 0 AND SERIALNO IS NOT NULL
              GROUP BY  PARENTPRODUCTID, PRODUCTNO, PRODUCTID,SERIALNO    
              ) set2
              ) GROUP BY PARENTPRODUCTID,PRODUCTNO,PRODUCTID,SERIALNO
              ) BM
              LEFT JOIN  COB_T_SERIAL_NO SN  ON  BM.SERIALNO=SN.SERIALNO
              )BOM
              WHERE ListCompQuantity <> 0;