基于Decode/CASE的SQL Join优化：用CTE简化重复转换逻辑

问题背景 #

我需要优化现有SQL的性能，减少Decode/Substr/Case函数的重复调用，把这些转换逻辑迁移到公共表表达式（CTE）中。核心逻辑是：

• 转换subinventory_code列表
• 根据TC.subinventory_code最后3个字符是否为'MMS'，对IOQD.secondary_inventory做条件关联：
  • 满足条件时，使用特定转换规则关联
  • 不满足时，使用与TC.subinventory_code一致的通用转换规则关联

无法创建新数据库表，但可以通过CTE从Dual表生成临时映射表，不清楚如何构建其结构。

原关联逻辑代码 #

AND          
decode(substr(TC.subinventory_code, 1,3),
'11C','11CCL'
,'11O','11OPR'
,'11M','11MMS'
,'18O','18OPR'
,'41S','24000'
,'41O','41OPR'
,'60O','60OPR'
,'70O','70OPR'
,'70C','70CCL'
,'70M', '70OPR'      
,subinventory_code)  = CASE WHEN SUBSTR(TC.subinventory_code,3,3) = 'MMS' THEN 
decode(substr(IOQD.secondary_inventory, 1,3), '11O','11MMS'                                                                                                                               
                                             ,'18', '18MMS'                                                                                                                                  
                                             ,'41', '41MMS'                                                                                                                                  
                                             ,'60', '60MMS'                                                                                                                                  
                                             ,'70', '70MMS')                                                                                                                                 
                                                                                                                              
ELSE decode(substr(IOQD.secondary_inventory,1,3),                                                                                                                                        
'11C','11CCL'                                                                                                                                          
,'11O','11OPR'                                                                                                                                         
,'11M','11MMS'                                                                                                                                         
,'18O','18OPR'                                                                                                                                         
,'41S','24000'                                                                                                                                         
,'41O','41OPR'                                                                                                                                         
,'60O','60OPR'                                                                                                                                           
,'70O','70OPR'                                                                                                                                         
,'70C','70CCL'                                                                                                                                         
,'70M', '70OPR'                                                                                                                                        
,IOQD.secondary_inventory ) END    

完整原SQL（缩写版） #

(SELECT                 
                ESI.item_number,                      
,decode(substr(IOQD.secondary_inventory,1,3),'11C','11CCL'
              ,'11O','11OPR'
           ,'11M', '11MMS'
              ,'18O','18OPR'
              ,'41S','24000'
              ,'41O','41OPR'
              ,'60O','60OPR'
              ,'70O','70OPR'
              ,'70C','70CCL'
            ,'70M', '70OPR'
              ,IOQD.secondary_inventory
             ) subinventory_code,
                          
                TC.TRANSACTION_COST Cost,                     
 
 ROW_NUMBER() OVER (PARTITION BY decode(substr(IOQD.secondary_inventory,1,3),'11C','11CCL'
              ,'11O','11OPR'
           ,'11M', '11MMS'
              ,'18O','18OPR'
              ,'41S','24000'
              ,'41O','41OPR'
              ,'60O','60OPR'
              ,'70O','70OPR'
              ,'70C','70CCL'
            ,'70M', '70OPR'
              ,IOQD.secondary_inventory )   ORDER BY item_number, IOQD.secondary_inventory ) ivt_seqnum 
                

FROM   INV_ITEM_SUB_INVENTORIES IOQD,
       egp_system_items ESI ,
      Txn_Cost TC          
       
WHERE  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   
                                         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
                      )     

      
-------------------JOIN IN QUESTION STARTS BELOW!!!--------------------------------------                                                            
AND 
     
decode(substr(TC.subinventory_code, 1,3),
'11C','11CCL'
,'11O','11OPR'
,'11M','11MMS'
,'18O','18OPR'
,'41S','24000'
,'41O','41OPR'
,'60O','60OPR'
,'70O','70OPR'
,'70C','70CCL'
,'70M', '70OPR'      
,subinventory_code)  = CASE WHEN SUBSTR(TC.subinventory_code,3,3) = 'MMS' THEN 
decode(substr(IOQD.secondary_inventory, 1,3), '11O','11MMS'                                                                                                                               
                                             ,'18', '18MMS'                                                                                                                                  
                                             ,'41', '41MMS'                                                                                                                                  
                                             ,'60', '60MMS'                                                                                                                                  
                                             ,'70', '70MMS')                                                                                                                                 
                                                                                                                              
ELSE decode(substr(IOQD.secondary_inventory,1,3),                                                                                                                                        
'11C','11CCL'                                                                                                                                          
,'11O','11OPR'                                                                                                                                         
,'11M','11MMS'                                                                                                                                         
,'18O','18OPR'                                                                                                                                         
,'41S','24000'                                                                                                                                         
,'41O','41OPR'                                                                                                                                         
,'60O','60OPR'                                                                                                                                           
,'70O','70OPR'                                                                                                                                         
,'70C','70CCL'                                                                                                                                         
,'70M', '70OPR'                                                                                                                                        
,IOQD.secondary_inventory ) END                                                                                                                   
                                                       
      
         AND TC.INVENTORY_ITEM_ID(+) = IOQD.INVENTORY_ITEM_ID  AND TRANSACTION_COST IS NOT NULL  ) 

优化方案：使用CTE构建映射表 #

1. 定义CTE映射表 #

创建两个CTE：一个存储通用转换规则，另一个存储MMS场景的特殊转换规则，避免重复编写Decode逻辑。

WITH 
-- 通用subinventory转换映射表
subinv_mapping AS (
    SELECT '11C' AS src_prefix, '11CCL' AS target_code FROM dual UNION ALL
    SELECT '11O', '11OPR' FROM dual UNION ALL
    SELECT '11M', '11MMS' FROM dual UNION ALL
    SELECT '18O', '18OPR' FROM dual UNION ALL
    SELECT '41S', '24000' FROM dual UNION ALL
    SELECT '41O', '41OPR' FROM dual UNION ALL
    SELECT '60O', '60OPR' FROM dual UNION ALL
    SELECT '70O', '70OPR' FROM dual UNION ALL
    SELECT '70C', '70CCL' FROM dual UNION ALL
    SELECT '70M', '70OPR' FROM dual
),
-- MMS场景特殊转换映射表
mms_subinv_mapping AS (
    SELECT '11O' AS src_prefix, '11MMS' AS target_code FROM dual UNION ALL
    SELECT '18', '18MMS' FROM dual UNION ALL
    SELECT '41', '41MMS' FROM dual UNION ALL
    SELECT '60', '60MMS' FROM dual UNION ALL
    SELECT '70', '70MMS' FROM dual
)

2. 重构主查询 #

在主查询中通过左连接CTE获取转换后的值，替代重复的Decode/Substr调用，同时简化关联条件：

SELECT                 
    ESI.item_number,                      
    COALESCE(sm1.target_code, IOQD.secondary_inventory) AS subinventory_code,
    TC.TRANSACTION_COST AS Cost,                     
    ROW_NUMBER() OVER (
        PARTITION BY COALESCE(sm1.target_code, IOQD.secondary_inventory) 
        ORDER BY item_number, IOQD.secondary_inventory 
    ) AS ivt_seqnum 
FROM   INV_ITEM_SUB_INVENTORIES IOQD
JOIN   egp_system_items ESI 
    ON IOQD.inventory_item_id = ESI.inventory_item_id
    AND IOQD.organization_id = ESI.organization_id
LEFT JOIN Txn_Cost TC 
    ON TC.INVENTORY_ITEM_ID = IOQD.INVENTORY_ITEM_ID
    AND TRANSACTION_COST IS NOT NULL
    -- 核心关联逻辑：用CTE替代Decode/Case
    AND COALESCE(sm2.target_code, TC.subinventory_code) = 
        CASE 
            WHEN SUBSTR(TC.subinventory_code, -3) = 'MMS' THEN COALESCE(mm.target_code, IOQD.secondary_inventory)
            ELSE COALESCE(sm1.target_code, IOQD.secondary_inventory)
        END
-- 关联通用转换表获取IOQD的转换值
LEFT JOIN subinv_mapping sm1 
    ON SUBSTR(IOQD.secondary_inventory, 1, 3) = sm1.src_prefix
-- 关联通用转换表获取TC的转换值
LEFT JOIN subinv_mapping sm2 
    ON SUBSTR(TC.subinventory_code, 1, 3) = sm2.src_prefix
-- 关联MMS特殊转换表
LEFT JOIN mms_subinv_mapping mm 
    ON SUBSTR(IOQD.secondary_inventory, 1, 3) = mm.src_prefix
WHERE  
    ESI.inventory_item_id = :p_inv_num
    AND IOQD.secondary_inventory IN (
        SELECT 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
    )

优化说明 #

• 把重复的转换规则统一放到CTE中，避免多次调用Decode和Substr，提升查询可读性和性能
• 使用COALESCE替代Decode的默认值逻辑，更简洁
• 用SUBSTR(TC.subinventory_code, -3)替代SUBSTR(TC.subinventory_code,3,3)，更准确获取最后3个字符
• 显式使用JOIN语法替代旧的逗号分隔表，提升代码可读性

内容的提问来源于stack exchange，提问作者Nick