Oracle SQL优化：条件执行UNION内联视图及NVL用法疑问

问题概述 #

现有两段SQL作为内联视图，原本通过UNION从任一表获取数据，需求是优先执行第一段SQL，若返回结果则无需执行第二段，使用NVL包裹结果集时报错，同时咨询通过priority字段结合FETCH FIRST ROW WITH TIES优化的可行性。

一、NVL用法报错原因及修正 #

NVL函数仅用于处理单个标量值，无法直接接收整个结果集作为参数，这是报错的核心原因。如果一定要用类似逻辑，需将结果集转换为单行记录（如聚合），但这种方式不适用于多行结果场景，因此更推荐以下高效替代方案。

二、高效条件执行方案 #

方案1：WITH子句+EXISTS判断（明确优先逻辑） #

通过CTE先缓存第一段查询的结果，再判断是否存在数据，有则直接返回，无则执行第二段：

WITH first_query AS (
    SELECT A.INVENTORY_ITEM_ID
          ,C.SUBINVENTORY_CODE
          ,TO_CHAR(B.TRANSACTION_COST,'FM99990.90') TRANSACTION_COST
          ,ROW_NUMBER() OVER (PARTITION BY A.INVENTORY_ITEM_ID, C.SUBINVENTORY_CODE ORDER BY A.INVENTORY_ITEM_ID, B.COST_DATE DESC) rownum1                               
          ,'' SECONDARY_INVENTORY
    FROM CST_ONHAND_V A
         JOIN CST_ITEM_COST_HISTORY_V B 
             ON 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                           
         JOIN CST_TXN_LAYER_DTLS_V C
             ON A.REC_TRXN_ID=C.REC_TRXN_ID
    WHERE A.INVENTORY_ITEM_ID= :p_inv_num
    ORDER BY B.COST_DATE DESC NULLS LAST
),
first_filtered AS (
    SELECT * FROM first_query WHERE rownum1 = 1 AND TRANSACTION_COST IS NOT NULL
)
SELECT * FROM first_filtered
UNION ALL
SELECT * FROM (
    SELECT INV.INVENTORY_ITEM_ID
          ,SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE
          ,NVL(TO_CHAR((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),TO_CHAR(LN.UNIT_PRICE,'FM99990.90')) TRANSACTION_COST 
          ,ROW_NUMBER() OVER(PARTITION BY INV.INVENTORY_ITEM_ID , DECODE(SUBSTR(SEC.SECONDARY_INVENTORY_NAME,1,3),
                                                              '70O','70OPR'
                                                              ,'70M','70OPR'
                                                              ,SEC.SECONDARY_INVENTORY_NAME ) 
                           ORDER BY INV.INVENTORY_ITEM_ID, SEC.SECONDARY_INVENTORY_NAME ) rownumber
          ,INV.SECONDARY_INVENTORY 
    FROM INV_ITEM_SUB_INVENTORIES INV
         JOIN inv_secondary_inventories SEC 
             ON SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
             AND SEC.organization_id = INV.organization_id
         JOIN PO_LINES_ALL LN
             ON LN.ITEM_ID = INV.INVENTORY_ITEM_ID
         JOIN PO_HEADERS_ALL HDR
             ON HDR.PO_HEADER_ID = LN.PO_HEADER_ID
         LEFT JOIN INV_UOM_CONVERSIONS UOM
             ON UOM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
             AND UOM.UOM_CODE = LN.UOM_CODE
    WHERE SEC.ASSET_INVENTORY = 2
      AND (LN.EXPIRATION_DATE IS NULL OR LN.EXPIRATION_DATE > SYSDATE)
      AND (HDR.END_DATE IS NULL OR HDR.END_DATE > SYSDATE)
      AND LN.LINE_STATUS <> 'CANCELED'
      AND HDR.type_lookup_code = 'BLANKET'
      AND INV.INVENTORY_ITEM_ID = :p_inv_num
      AND SEC.attribute1='Y'
) WHERE rownumber = 1
AND NOT EXISTS (SELECT 1 FROM first_filtered);

优势：逻辑清晰，Oracle会先执行first_filtered，若有数据则跳过第二段查询，避免不必要的表扫描。

方案2：UNION ALL + 优先级排序（简洁高效） #

给两段查询分别标记优先级，按优先级排序后取结果，Oracle优化器会优先处理高优先级的查询，若有结果则终止低优先级查询的执行：

SELECT * FROM (
    -- 第一段查询：优先级1（最高）
    SELECT A.INVENTORY_ITEM_ID
          ,C.SUBINVENTORY_CODE
          ,TO_CHAR(B.TRANSACTION_COST,'FM99990.90') TRANSACTION_COST
          ,ROW_NUMBER() OVER (PARTITION BY A.INVENTORY_ITEM_ID, C.SUBINVENTORY_CODE ORDER BY A.INVENTORY_ITEM_ID, B.COST_DATE DESC) rownum1                               
          ,'' SECONDARY_INVENTORY
          ,1 AS priority
    FROM CST_ONHAND_V A
         JOIN CST_ITEM_COST_HISTORY_V B 
             ON 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                           
         JOIN CST_TXN_LAYER_DTLS_V C
             ON A.REC_TRXN_ID=C.REC_TRXN_ID
    WHERE A.INVENTORY_ITEM_ID= :p_inv_num
    ORDER BY B.COST_DATE DESC NULLS LAST
    
    UNION ALL
    
    -- 第二段查询：优先级2（次高）
    SELECT INV.INVENTORY_ITEM_ID
          ,SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE
          ,NVL(TO_CHAR((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),TO_CHAR(LN.UNIT_PRICE,'FM99990.90')) TRANSACTION_COST 
          ,ROW_NUMBER() OVER(PARTITION BY INV.INVENTORY_ITEM_ID , DECODE(SUBSTR(SEC.SECONDARY_INVENTORY_NAME,1,3),
                                                              '70O','70OPR'
                                                              ,'70M','70OPR'
                                                              ,SEC.SECONDARY_INVENTORY_NAME ) 
                           ORDER BY INV.INVENTORY_ITEM_ID, SEC.SECONDARY_INVENTORY_NAME ) rownumber
          ,INV.SECONDARY_INVENTORY 
          ,2 AS priority
    FROM INV_ITEM_SUB_INVENTORIES INV
         JOIN inv_secondary_inventories SEC 
             ON SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
             AND SEC.organization_id = INV.organization_id
         JOIN PO_LINES_ALL LN
             ON LN.ITEM_ID = INV.INVENTORY_ITEM_ID
         JOIN PO_HEADERS_ALL HDR
             ON HDR.PO_HEADER_ID = LN.PO_HEADER_ID
         LEFT JOIN INV_UOM_CONVERSIONS UOM
             ON UOM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
             AND UOM.UOM_CODE = LN.UOM_CODE
    WHERE SEC.ASSET_INVENTORY = 2
      AND (LN.EXPIRATION_DATE IS NULL OR LN.EXPIRATION_DATE > SYSDATE)
      AND (HDR.END_DATE IS NULL OR HDR.END_DATE > SYSDATE)
      AND LN.LINE_STATUS <> 'CANCELED'
      AND HDR.type_lookup_code = 'BLANKET'
      AND INV.INVENTORY_ITEM_ID = :p_inv_num
      AND SEC.attribute1='Y'
)
WHERE (priority = 1 AND rownum1 = 1 AND TRANSACTION_COST IS NOT NULL)
   OR (priority = 2 AND rownumber = 1)
ORDER BY priority
FETCH FIRST ROW WITH TIES;

注意：必须用UNION ALL而非UNION，UNION会触发全局排序去重，导致两段查询都被执行，无法实现优先逻辑。

三、priority + FETCH FIRST ROW WITH TIES的可行性 #

该方案完全可行，核心要点如下：

1. 列一致性：两段查询的列数、数据类型必须完全匹配，包括新增的priority字段。
2. UNION ALL替代UNION：避免排序去重带来的性能损耗，确保优化器优先处理高优先级查询。
3. FETCH FIRST ROW WITH TIES：若第一段查询返回多条符合条件的记录（同优先级），会全部返回；若只需单条，可改用FETCH FIRST 1 ROW ONLY。

你的示例代码存在语法错误（如多余的逗号、表名不匹配），修正后的版本参考方案2即可。

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