Thiết kế website giá rẻ

Question

I have the below CTE/Query running on Oracle database.

WITH Txn_Cost as
(SELECT * FROM 
                 (SELECT A.INVENTORY_ITEM_ID
                          ,C.SUBINVENTORY_CODE,
                          TO_CHAR(A.REC_TRXN_ID) REC_TRXN_ID,
                          TO_CHAR(B.TRANSACTION_ID) TRANSACTION_ID,
                          TO_CHAR(B.COST_DATE) COST_DATE,
                          TO_CHAR(A.QUANTITY_ONHAND) QUANTITY_ONHAND,
                          B.TRANSACTION_COST
                      FROM CST_ONHAND_V A,
                           CST_ITEM_COST_HISTORY_V B,
                           CST_TXN_LAYER_DTLS_V C
                     WHERE 1=1
                       AND A.INVENTORY_ITEM_ID= :p_inv_num
                       AND A.REC_TRXN_ID=B.TRANSACTION_ID
                   
                       AND A.COST_ORG_ID = B.COST_ORG_ID
                       AND A.COST_BOOK_ID = B.COST_BOOK_ID
                       AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID
                       --AND B.COST_DATE=(SELECT MAX(COST_DATE) FROM CST_ITEM_COST_HISTORY_V B_COST WHERE 
                       --B_COST.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
                       --TRANSACTION_ID=B.TRANSACTION_ID
                       --)
                     --  AND B.TRANSACTION_ID = C.TRANSACTION_ID
                       AND A.REC_TRXN_ID=C.REC_TRXN_ID
                    --AND C.SUBINVENTORY_CODE = IOQD.secondary_inventory     
    ORDER BY B.COST_DATE DESC NULLS LAST
    )
    WHERE ROWNUM = 1    

)
        
Select * from 
(( 
SELECT item_number,item_DESCRIPTION   
      ,decode(substr(subinventory_code,1,3),'11C','11CCL'
              ,'11O','11OPR'
              ,'18O','18OPR'
              ,'41S','24000'
              ,'41O','41OPR'
              ,'60O','60OPR'
              ,'70O','70OPR'
              ,'70C','70CCL'
              ,subinventory_code
             ) subinventory_code
      ,p_inv,
       round(Cost,5) Cost,Patient_Chargeable,Chargeable_code,
       patient_charges,STOCK_ENABLED_FLAG,INV_LOCATOR ,rownum ivt_seqnum 
       ,               CONSIGNED_FLAG
FROM (
SELECT DISTINCT ESI.item_number,
                replace(replace(ESI.LONG_DESCRIPTION,chr(10)),chr(13))                  item_DESCRIPTION,
                IOQD.subinventory_code,
                IOQD.inventory_item_id                 p_inv,
                TC.TRANSACTION_COST   Cost,
                ESI.attribute5
                Patient_Chargeable,
                ESI.attribute2
                Chargeable_code,
                ESI.attribute3
                patient_charges,
                ESI.STOCK_ENABLED_FLAG,
(select SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5
                   from inv_item_locations
                  where inventory_location_id= ioqd.locator_id and length(Segment1) < 3
and length(Segment1) >= 1
                ) INV_LOCATOR,
        CASE WHEN ESI.CONSIGNED_FLAG = '1' THEN 'Y'
                           WHEN ESI.CONSIGNED_FLAG = '2' THEN 'N'
                          END AS CONSIGNED_FLAG
FROM   inv_onhand_quantities_detail IOQD,
       egp_system_items ESI, 
       Txn_Cost TC
WHERE  1 = 1
       AND IOQD.inventory_item_id = ESI.inventory_item_id
       AND IOQD.organization_id = ESI.organization_id
       AND ESI.inventory_item_id = :p_inv_num
        AND TC.subinventory_code(+) = IOQD.subinventory_code
       AND IOQD.subinventory_code IN (select distinct SECONDARY_INVENTORY_NAME from inv_secondary_inventories where attribute1='Y'  ))
)  --)

UNION ALL

(SELECT DISTINCT ESI.item_number,
                replace(replace(ESI.LONG_DESCRIPTION,chr(10)),chr(13))                  item_DESCRIPTION
--                IOQD.secondary_inventory subinventory_code,
,decode(substr(IOQD.secondary_inventory,1,3),'11C','11CCL'
              ,'11O','11OPR'
              ,'18O','18OPR'
              ,'41S','24000'
              ,'41O','41OPR'
              ,'60O','60OPR'
              ,'70O','70OPR'
              ,'70C','70CCL'
              ,IOQD.secondary_inventory
             ) subinventory_code,
                IOQD.inventory_item_id
                p_inv,
                TC.TRANSACTION_COST   Cost,
                ESI.attribute5
                Patient_Chargeable,
                ESI.attribute2
                Chargeable_code,
                ESI.attribute3
                patient_charges,
                ESI.STOCK_ENABLED_FLAG,
case when 
 (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_item_loc_defaults iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.locator_id       = iil.inventory_location_id
            AND rownum= 1 and length(Segment1) >= 1 ) is not null 
then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_item_loc_defaults iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.locator_id       = iil.inventory_location_id
            AND rownum= 1 and length(Segment1) >= 1 )   

when (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_secondary_locators iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
            AND rownum = 1 and length(Segment1) >= 1 ) is not null 
        Then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
           FROM inv_secondary_locators iild, 
                inv_item_locations iil
          WHERE iild.inventory_item_id = esi.inventory_item_id
            AND iild.subinventory_code = IOQD.secondary_inventory
            AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
            AND rownum = 1 and length(Segment1) >= 1 )

Else 'AAAAAAA'
END as      INV_LOCATOR, 
 rownum ivt_seqnum
        , CASE WHEN ESI.CONSIGNED_FLAG = '1' THEN 'Y'
                           WHEN ESI.CONSIGNED_FLAG = '2' THEN 'N'
                          END AS CONSIGNED_FLAG

FROM   INV_ITEM_SUB_INVENTORIES IOQD,
       egp_system_items ESI,
       Txn_Cost TC
WHERE  1 = 1
       AND IOQD.inventory_item_id = ESI.inventory_item_id
       AND IOQD.organization_id = ESI.organization_id
       AND ESI.inventory_item_id = :p_inv_num
       AND IOQD.secondary_inventory IN (select distinct SECONDARY_INVENTORY_NAME from inv_secondary_inventories where attribute1='Y')
       AND NOT EXISTS (select 1 
                         FROM inv_onhand_quantities_detail
                        WHERE subinventory_code = IOQD.secondary_inventory
                          AND inventory_item_id  = ESI.inventory_item_id
                      ) 
      AND TC.subinventory_code(+) = IOQD.secondary_inventory

) )results 

order by subinventory_code, INV_LOCATOR

The above runs in an acceptable amount of time, however when I add this bit of code below it takes significantly longer. Below is the exact code added, and then (below that) I have it incorporated into the entire query as well.

    UNION 
          SELECT ITM.INVENTORY_ITEM_ID,              
                SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
                '',
                '',
                '',
                '',
                round((LN.UNIT_PRICE / UOM.CONVERSION_RATE),3) TRANSACTION_COST 
            FROM INV_ITEM_SUB_INVENTORIES INV
               ,inv_secondary_inventories SEC
                 ,PO_LINES_ALL LN
                ,PO_HEADERS_ALL HDR
                ,INV_UOM_CONVERSIONS UOM
                ,EGP_SYSTEM_ITEMS ITM           
                             
            WHERE SEC.ASSET_INVENTORY = 2
             AND SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
             AND SEC.organization_id = INV.organization_id
              AND LN.ITEM_ID = INV.INVENTORY_ITEM_ID
              AND (LN.EXPIRATION_DATE IS NULL OR LN.EXPIRATION_DATE > sysdate)
              AND LN.LINE_STATUS <> 'CANCELED'
              AND HDR.PO_HEADER_ID = LN.PO_HEADER_ID
              AND HDR.type_lookup_code = 'BLANKET'
              AND UOM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
              AND UOM.UOM_CODE = LN.UOM_CODE
              AND ITM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
              AND ITM.organization_id = 300000526890796
              AND SUBSTR(SEC.SECONDARY_INVENTORY_NAME,1,2) = '70'
              AND ITM.INVENTORY_ITEM_ID = :p_inv_num

Entire Query with the above section added that is causing performance issue:

WITH Txn_Cost as
    (SELECT * FROM 
                     (SELECT A.INVENTORY_ITEM_ID
                              ,C.SUBINVENTORY_CODE,
                              TO_CHAR(A.REC_TRXN_ID) REC_TRXN_ID,
                              TO_CHAR(B.TRANSACTION_ID) TRANSACTION_ID,
                              TO_CHAR(B.COST_DATE) COST_DATE,
                              TO_CHAR(A.QUANTITY_ONHAND) QUANTITY_ONHAND,
                              B.TRANSACTION_COST
                          FROM CST_ONHAND_V A,
                               CST_ITEM_COST_HISTORY_V B,
                               CST_TXN_LAYER_DTLS_V C
                         WHERE 1=1
                           AND A.INVENTORY_ITEM_ID= :p_inv_num
                           AND A.REC_TRXN_ID=B.TRANSACTION_ID
                       
                           AND A.COST_ORG_ID = B.COST_ORG_ID
                           AND A.COST_BOOK_ID = B.COST_BOOK_ID
                           AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID
                           --AND B.COST_DATE=(SELECT MAX(COST_DATE) FROM CST_ITEM_COST_HISTORY_V B_COST WHERE 
                           --B_COST.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
                           --TRANSACTION_ID=B.TRANSACTION_ID
                           --)
                         --  AND B.TRANSACTION_ID = C.TRANSACTION_ID
                           AND A.REC_TRXN_ID=C.REC_TRXN_ID
                        --AND C.SUBINVENTORY_CODE = IOQD.secondary_inventory     
        ORDER BY B.COST_DATE DESC NULLS LAST
        )
        WHERE ROWNUM = 1
 --BEGIN CODE CHANGES!!!!-----------------------------------   
    UNION 
      SELECT ITM.INVENTORY_ITEM_ID,              
            SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
            '',
            '',
            '',
            '',
            round((LN.UNIT_PRICE / UOM.CONVERSION_RATE),3) TRANSACTION_COST 
        FROM INV_ITEM_SUB_INVENTORIES INV
           ,inv_secondary_inventories SEC
             ,PO_LINES_ALL LN
            ,PO_HEADERS_ALL HDR
            ,INV_UOM_CONVERSIONS UOM
            ,EGP_SYSTEM_ITEMS ITM                   
                                     
        
         WHERE SEC.ASSET_INVENTORY = 2
         AND SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
         AND SEC.organization_id = INV.organization_id
          AND LN.ITEM_ID = INV.INVENTORY_ITEM_ID
          AND (LN.EXPIRATION_DATE IS NULL OR LN.EXPIRATION_DATE > sysdate)
          AND LN.LINE_STATUS <> 'CANCELED'
          AND HDR.PO_HEADER_ID = LN.PO_HEADER_ID
          AND HDR.type_lookup_code = 'BLANKET'
          AND UOM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
          AND UOM.UOM_CODE = LN.UOM_CODE
          AND ITM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
          AND ITM.organization_id = 300000526890796
          AND SUBSTR(SEC.SECONDARY_INVENTORY_NAME,1,2) = '70'
          AND ITM.INVENTORY_ITEM_ID = :p_inv_num  
    --END CODE CHANGES!!!!----------------------------------- 
    )
                   
    
    Select * from 
    (( 
    SELECT item_number,item_DESCRIPTION       
          ,decode(substr(subinventory_code,1,3),'11C','11CCL'
                  ,'11O','11OPR'
                  ,'18O','18OPR'
                  ,'41S','24000'
                  ,'41O','41OPR'
                  ,'60O','60OPR'
                  ,'70O','70OPR'
                  ,'70C','70CCL'
                  ,subinventory_code
                 ) subinventory_code
          ,p_inv,
           round(Cost,5) Cost,Patient_Chargeable,Chargeable_code,
           patient_charges,STOCK_ENABLED_FLAG,INV_LOCATOR ,rownum ivt_seqnum 
           ,               CONSIGNED_FLAG
    FROM (
    SELECT DISTINCT ESI.item_number,
                    replace(replace(ESI.LONG_DESCRIPTION,chr(10)),chr(13))                  item_DESCRIPTION,
                    IOQD.subinventory_code,
                    IOQD.inventory_item_id                 p_inv,
                    TC.TRANSACTION_COST   Cost,
                    ESI.attribute5
                    Patient_Chargeable,
                    ESI.attribute2
                    Chargeable_code,
                    ESI.attribute3
                    patient_charges,
                    ESI.STOCK_ENABLED_FLAG,
    (select SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5
                       from inv_item_locations
                      where inventory_location_id= ioqd.locator_id and length(Segment1) < 3
    and length(Segment1) >= 1
                    ) INV_LOCATOR,
            CASE WHEN ESI.CONSIGNED_FLAG = '1' THEN 'Y'
                               WHEN ESI.CONSIGNED_FLAG = '2' THEN 'N'
                              END AS CONSIGNED_FLAG
    FROM   inv_onhand_quantities_detail IOQD,
           egp_system_items ESI, 
           Txn_Cost TC
    WHERE  1 = 1
           AND IOQD.inventory_item_id = ESI.inventory_item_id
           AND IOQD.organization_id = ESI.organization_id
           AND ESI.inventory_item_id = :p_inv_num
            AND TC.subinventory_code(+) = IOQD.subinventory_code
           AND IOQD.subinventory_code IN (select distinct SECONDARY_INVENTORY_NAME from inv_secondary_inventories where attribute1='Y'  ))
    )  --)
    
    UNION ALL
    
    (SELECT DISTINCT ESI.item_number,
                    replace(replace(ESI.LONG_DESCRIPTION,chr(10)),chr(13))                  item_DESCRIPTION
    --                IOQD.secondary_inventory subinventory_code,
    ,decode(substr(IOQD.secondary_inventory,1,3),'11C','11CCL'
                  ,'11O','11OPR'
                  ,'18O','18OPR'
                  ,'41S','24000'
                  ,'41O','41OPR'
                  ,'60O','60OPR'
                  ,'70O','70OPR'
                  ,'70C','70CCL'
                  ,IOQD.secondary_inventory
                 ) subinventory_code,
                    IOQD.inventory_item_id
                    p_inv,
                    TC.TRANSACTION_COST   Cost,
                    ESI.attribute5
                    Patient_Chargeable,
                    ESI.attribute2
                    Chargeable_code,
                    ESI.attribute3
                    patient_charges,
                    ESI.STOCK_ENABLED_FLAG,
    case when 
     (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
               FROM inv_item_loc_defaults iild, 
                    inv_item_locations iil
              WHERE iild.inventory_item_id = esi.inventory_item_id
                AND iild.subinventory_code = IOQD.secondary_inventory
                AND iild.locator_id       = iil.inventory_location_id
                AND rownum= 1 and length(Segment1) >= 1 ) is not null 
    then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
               FROM inv_item_loc_defaults iild, 
                    inv_item_locations iil
              WHERE iild.inventory_item_id = esi.inventory_item_id
                AND iild.subinventory_code = IOQD.secondary_inventory
                AND iild.locator_id       = iil.inventory_location_id
                AND rownum= 1 and length(Segment1) >= 1 )   
    
    when (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
               FROM inv_secondary_locators iild, 
                    inv_item_locations iil
              WHERE iild.inventory_item_id = esi.inventory_item_id
                AND iild.subinventory_code = IOQD.secondary_inventory
                AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
                AND rownum = 1 and length(Segment1) >= 1 ) is not null 
            Then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
               FROM inv_secondary_locators iild, 
                    inv_item_locations iil
              WHERE iild.inventory_item_id = esi.inventory_item_id
                AND iild.subinventory_code = IOQD.secondary_inventory
                AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
                AND rownum = 1 and length(Segment1) >= 1 )
    
    Else 'AAAAAAA'
    END as      INV_LOCATOR, 
     rownum ivt_seqnum
            , CASE WHEN ESI.CONSIGNED_FLAG = '1' THEN 'Y'
                               WHEN ESI.CONSIGNED_FLAG = '2' THEN 'N'
                              END AS CONSIGNED_FLAG
    
    FROM   INV_ITEM_SUB_INVENTORIES IOQD,
           egp_system_items ESI,
           Txn_Cost TC
    WHERE  1 = 1
           AND IOQD.inventory_item_id = ESI.inventory_item_id
           AND IOQD.organization_id = ESI.organization_id
           AND ESI.inventory_item_id = :p_inv_num
           AND IOQD.secondary_inventory IN (select distinct SECONDARY_INVENTORY_NAME from inv_secondary_inventories where attribute1='Y')
           AND NOT EXISTS (select 1 
                             FROM inv_onhand_quantities_detail
                            WHERE subinventory_code = IOQD.secondary_inventory
                              AND inventory_item_id  = ESI.inventory_item_id
                          ) 
          AND TC.subinventory_code(+) = IOQD.secondary_inventory
    --Order by addition 
    ) )results 
    
    order by subinventory_code, INV_LOCATOR

I did change some of the Subqueries in the WHERE clause to be fully joined tables, and also re-worked the INV_LOCATOR CASE statements but that did not improve performance. I am looking for assistance in how I can improve performance from the section of code I added. Thank you.

Thiết kế website giá rẻ

Danh mục

Query performance issue when adding UNION to CTE/Query