Oracle SQL查询优化诉求：缩短执行时间，适配多数据加载与列扩展

原始查询语句 #

SELECT
    XCRV.CROSS_REFERENCE JENIS
    ,
    XTD_INV_CONVERT_QTY_UOM_FNC (
        (select min(mcrv2.INVENTORY_ITEM_ID)
        from XTD_CROSS_REFF_ITEM_V mcrv2
        where 1=1
        and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
        and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
        MMT.ORGANIZATION_ID,
        (SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
        FROM
        MTL_MATERIAL_TRANSACTIONS MMT2,
        XTD_CROSS_REFF_ITEM_V XCRV2
        WHERE 
            MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
        AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
        AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) ) 
        ),
        MSIB.PRIMARY_UOM_CODE,
        'BAL'
    ) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
        (select min(mcrv2.INVENTORY_ITEM_ID)
        from XTD_CROSS_REFF_ITEM_V mcrv2
        where 1=1
        and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
        and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
        MMT.ORGANIZATION_ID,
        (SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
        FROM
        MTL_MATERIAL_TRANSACTIONS MMT2,
        XTD_CROSS_REFF_ITEM_V XCRV2
        WHERE 
            MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
        AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
        AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) ) 
        ),
        MSIB.PRIMARY_UOM_CODE,
        'PRS'
    ) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
        (select min(mcrv2.INVENTORY_ITEM_ID)
        from XTD_CROSS_REFF_ITEM_V mcrv2
        where 1=1
        and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
        and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
        MMT.ORGANIZATION_ID,
        (SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
        FROM
        MTL_MATERIAL_TRANSACTIONS MMT2,
        XTD_CROSS_REFF_ITEM_V XCRV2
        WHERE 
            MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
        AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
        AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) ) 
        ),
        MSIB.PRIMARY_UOM_CODE,
        'BKS'
    ) AS SALDO_AWAL
FROM
    MTL_MATERIAL_TRANSACTIONS MMT,
    MTL_TRANSACTION_TYPES MTT,
    MTL_SYSTEM_ITEMS_B MSIB,
    XTD_CROSS_REFF_ITEM_V XCRV,
    ORG_ORGANIZATION_DEFINITIONS OOD,
    HR_OPERATING_UNITS HOU,
    GL_LEDGERS GL
WHERE
    MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
    AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
    AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
    AND XCRV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
    AND HOU.BUSINESS_GROUP_ID = OOD.BUSINESS_GROUP_ID
    AND OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
    --HARDCODE
    AND HOU.ORGANIZATION_ID NOT IN ('82')
    AND XCRV.CROSS_REFERENCE = 'ARB12'
    -- PARAMETERS
    AND GL.LEDGER_ID = NVL(:P_LEDGER, GL.LEDGER_ID)
    AND HOU.ORGANIZATION_ID = NVL(:P_OU_ID, HOU.ORGANIZATION_ID)
    AND OOD.ORGANIZATION_ID = NVL(:P_CABANG, OOD.ORGANIZATION_ID)
    -- HEADING
    AND TRUNC(MMT.TRANSACTION_DATE) BETWEEN TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ))  AND TRUNC( TO_DATE( :P_DATE_TO, 'YYYY/MM/DD' )) 
GROUP BY
    XCRV.CROSS_REFERENCE,
    MMT.ORGANIZATION_ID,
    MSIB.PRIMARY_UOM_CODE
ORDER BY
    XCRV.CROSS_REFERENCE

当前问题 #

硬编码单条数据时查询耗时约4分钟，后续需加载大量数据并新增字段，急需优化以大幅缩短执行时间。

优化方案 #

1. 消除重复子查询，提前计算公共数据 #

原查询多次重复执行相同子查询（获取最小ITEM_ID、求和库存数量），导致重复扫表拖慢性能。用CTE提前计算这些值，避免重复执行：

WITH pre_calc AS (
    -- 提前计算每个CROSS_REFERENCE+ORGANIZATION_ID对应的最小ITEM_ID
    SELECT 
        mcrv2.cross_reference,
        mcrv2.organization_id,
        MIN(mcrv2.INVENTORY_ITEM_ID) AS min_item_id
    FROM XTD_CROSS_REFF_ITEM_V mcrv2
    GROUP BY mcrv2.cross_reference, mcrv2.organization_id
),
inv_sum AS (
    -- 提前计算每个CROSS_REFERENCE对应的库存总和
    SELECT 
        XCRV2.CROSS_REFERENCE,
        NVL(SUM(MMT2.PRIMARY_QUANTITY), 0) AS total_qty
    FROM MTL_MATERIAL_TRANSACTIONS MMT2
    JOIN XTD_CROSS_REFF_ITEM_V XCRV2 
        ON MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
    WHERE MMT2.TRANSACTION_DATE < TRUNC(TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD')) + 1
    GROUP BY XCRV2.CROSS_REFERENCE
)
SELECT
    XCRV.CROSS_REFERENCE JENIS,
    XTD_INV_CONVERT_QTY_UOM_FNC(pc.min_item_id, MMT.ORGANIZATION_ID, isum.total_qty, MSIB.PRIMARY_UOM_CODE, 'BAL') 
    || '-' || 
    XTD_INV_CONVERT_QTY_UOM_FNC(pc.min_item_id, MMT.ORGANIZATION_ID, isum.total_qty, MSIB.PRIMARY_UOM_CODE, 'PRS')
    || '-' || 
    XTD_INV_CONVERT_QTY_UOM_FNC(pc.min_item_id, MMT.ORGANIZATION_ID, isum.total_qty, MSIB.PRIMARY_UOM_CODE, 'BKS') AS SALDO_AWAL
FROM MTL_MATERIAL_TRANSACTIONS MMT
JOIN MTL_TRANSACTION_TYPES MTT 
    ON MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
JOIN MTL_SYSTEM_ITEMS_B MSIB 
    ON MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
JOIN XTD_CROSS_REFF_ITEM_V XCRV 
    ON XCRV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
JOIN ORG_ORGANIZATION_DEFINITIONS OOD 
    ON MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
JOIN HR_OPERATING_UNITS HOU 
    ON HOU.BUSINESS_GROUP_ID = OOD.BUSINESS_GROUP_ID
JOIN GL_LEDGERS GL 
    ON OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
JOIN pre_calc pc 
    ON pc.cross_reference = XCRV.CROSS_REFERENCE
    AND pc.organization_id = MMT.ORGANIZATION_ID
JOIN inv_sum isum 
    ON isum.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
WHERE
    HOU.ORGANIZATION_ID != '82'
    AND XCRV.CROSS_REFERENCE = 'ARB12'
    -- 优化参数过滤逻辑，避免NVL导致索引失效
    AND (:P_LEDGER IS NULL OR GL.LEDGER_ID = :P_LEDGER)
    AND (:P_OU_ID IS NULL OR HOU.ORGANIZATION_ID = :P_OU_ID)
    AND (:P_CABANG IS NULL OR OOD.ORGANIZATION_ID = :P_CABANG)
    -- 优化日期过滤，利用TRANSACTION_DATE的索引
    AND MMT.TRANSACTION_DATE >= TRUNC(TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD'))
    AND MMT.TRANSACTION_DATE < TRUNC(TO_DATE(:P_DATE_TO, 'YYYY/MM/DD')) + 1
GROUP BY
    XCRV.CROSS_REFERENCE,
    MMT.ORGANIZATION_ID,
    MSIB.PRIMARY_UOM_CODE,
    pc.min_item_id,
    isum.total_qty
ORDER BY
    XCRV.CROSS_REFERENCE

2. 修复日期过滤逻辑，避免索引失效 #

原查询中TRUNC(MMT.TRANSACTION_DATE)会导致字段索引无法被利用，改成范围条件：

• MMT.TRANSACTION_DATE >= TRUNC(TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD'))
• MMT.TRANSACTION_DATE < TRUNC(TO_DATE(:P_DATE_TO, 'YYYY/MM/DD')) + 1
  数据库可直接使用TRANSACTION_DATE上的索引，大幅减少扫描行数。

3. 优化参数过滤逻辑 #

原查询GL.LEDGER_ID = NVL(:P_LEDGER, GL.LEDGER_ID)写法，参数为NULL时会强制全表扫描，改成(:P_LEDGER IS NULL OR GL.LEDGER_ID = :P_LEDGER)，让数据库在参数有值时利用索引过滤。

4. 添加必要组合索引 #

根据查询逻辑，建议添加以下索引：

• MTL_MATERIAL_TRANSACTIONS：(TRANSACTION_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID, PRIMARY_QUANTITY)
• XTD_CROSS_REFF_ITEM_V：(CROSS_REFERENCE, ORGANIZATION_ID, INVENTORY_ITEM_ID)
• 检查关联字段（如MMT.INVENTORY_ITEM_ID、MMT.ORGANIZATION_ID）是否已有主键/外键索引，确保关联性能。

5. 简化UDF调用 #

如果XTD_INV_CONVERT_QTY_UOM_FNC是自定义函数，建议修改逻辑，一次传入多个单位类型返回拼接结果，减少三次函数调用开销；或在CTE中先计算公共参数，避免重复传递相同值。

6. 替换旧式连接语法 #

原查询用逗号分隔表的旧式连接，改成JOIN...ON语法，让优化器更清晰解析表关联关系，生成更优执行计划。

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