Oracle SQL递归查询优化与全工单数据集适配求助

问题背景 #

使用Oracle SQL Developer处理工单递归层级时遇到性能瓶颈：工单cc-1归属bb-1，bb-1归属aa-1。需要生成带父工单字段的数据集，用于AWS Quicksight通过工单编号参数过滤。目前单个工单（如aa-1）的递归查询耗时72秒，无法支撑数百个工单的全量数据查询，希望优化后能快速加载近3个月的工单数据。

现有查询代码 #

WITH order_hierarchy AS (
        SELECT  we.wip_entity_name AS work_order,
                msi.segment1 AS part_number,
                mut.serial_number,
                mmt.transaction_date
        FROM    apps.mtl_system_items_b msi
                INNER JOIN apps.mtl_material_transactions mmt
                    ON msi.organization_id = mmt.organization_id
                    AND msi.inventory_item_id = mmt.inventory_item_id
                INNER JOIN apps.wip_entities we
                    ON mmt.organization_id = we.organization_id
                    AND mmt.transaction_source_id = we.wip_entity_id
                LEFT OUTER JOIN apps.mtl_unit_transactions_all_v mut
                    ON mmt.transaction_id = mut.transaction_id
                    AND mmt.organization_id = mut.organization_id
                AND mmt.inventory_item_id = mut.inventory_item_id
        WHERE   TRUNC(mmt.transaction_date) >= ADD_MONTHS(TRUNC(TO_DATE(SYSDATE),'Q'),-12)
                AND msi.serial_number_control_code = 5
                AND mmt.transaction_type_id = 35
                AND CASE
                        WHEN mut.serial_number IS NOT NULL THEN mmt.transaction_quantity / ABS(mmt.transaction_quantity)
                        ELSE mmt.transaction_quantity
                END = -1      
)
SELECT  *
FROM    order_hierarchy
START WITH work_order = 'aa-1'
CONNECT BY PRIOR serial_number = work_order; 

样本数据 #

CREATE TABLE table_name (work_order, part_number, serial_number, transaction_date) AS
  SELECT 'aa-1', 'A1', '112233', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'aa-1', 'A2', '112234', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'aa-1', 'B1', 'bb-1', '18-JAN-24'   FROM DUAL UNION ALL
  SELECT 'bb-1', 'S1', '998877', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'bb-1', 'S2', '998878', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'bb-1', 'C1', 'cc-1', '18-JAN-24'   FROM DUAL UNION ALL
  SELECT 'cc-1', 'X1', '998877', '18-JAN-24' FROM DUAL UNION ALL
  SELECT 'cc-1', 'X2', '998878', '18-JAN-24' FROM DUAL;

样本数据递归查询CTE #

WITH order_hierarchy AS (
        SELECT  work_order,
                part_number,
                serial_number,
                transaction_date
        FROM    orders o      
)
SELECT  *
FROM    order_hierarchy
START WITH work_order = 'aa-1'
CONNECT BY PRIOR serial_number = work_order;

优化方案 #

1. 缩小数据范围+优化过滤条件 #

• 把时间范围从最近3年（12个季度）改成近3个月，直接减少数据量：将ADD_MONTHS(TRUNC(TO_DATE(SYSDATE),'Q'),-12)替换为ADD_MONTHS(TRUNC(SYSDATE), -3)
• 避免对transaction_date列做函数运算，修改过滤条件为mmt.transaction_date >= TRUNC(ADD_MONTHS(SYSDATE, -3))，让Oracle能用到该列的索引

2. 添加针对性索引 #

针对关联和过滤字段创建复合索引，提升基础查询效率：

-- 物料交易表索引
CREATE INDEX idx_mmt_org_date_type ON apps.mtl_material_transactions(organization_id, transaction_date, transaction_type_id);
-- 系统物料表索引
CREATE INDEX idx_msi_org_item_serial ON apps.mtl_system_items_b(organization_id, inventory_item_id, serial_number_control_code);
-- 工单实体表索引
CREATE INDEX idx_we_org_wipid ON apps.wip_entities(organization_id, wip_entity_id, wip_entity_name);
-- 单位交易视图底层表索引（如果视图基于mtl_unit_transactions_all）
CREATE INDEX idx_mut_trans_id ON apps.mtl_unit_transactions_all(transaction_id, organization_id, inventory_item_id, serial_number);

3. 改用递归CTE替代CONNECT BY #

Oracle 11gR2及以上支持递归CTE，多起点递归时性能更稳定，同时新增root_work_order字段方便Quicksight参数过滤：

WITH RECURSIVE order_hierarchy AS (
    -- 基础数据：筛选近3个月的工单
    SELECT  
        we.wip_entity_name AS work_order,
        msi.segment1 AS part_number,
        mut.serial_number,
        mmt.transaction_date,
        we.wip_entity_name AS root_work_order
    FROM    apps.mtl_system_items_b msi
            INNER JOIN apps.mtl_material_transactions mmt
                ON msi.organization_id = mmt.organization_id
                AND msi.inventory_item_id = mmt.inventory_item_id
            INNER JOIN apps.wip_entities we
                ON mmt.organization_id = we.organization_id
                AND mmt.transaction_source_id = we.wip_entity_id
            LEFT OUTER JOIN apps.mtl_unit_transactions_all_v mut
                ON mmt.transaction_id = mut.transaction_id
                AND mmt.organization_id = mut.organization_id
                AND mmt.inventory_item_id = mut.inventory_item_id
    WHERE   mmt.transaction_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)
            AND msi.serial_number_control_code = 5
            AND mmt.transaction_type_id = 35
            AND CASE
                    WHEN mut.serial_number IS NOT NULL THEN SIGN(mmt.transaction_quantity)
                    ELSE mmt.transaction_quantity
            END = -1
    -- 递归关联子工单
    UNION ALL
    SELECT  
        child.we.wip_entity_name AS work_order,
        child.msi.segment1 AS part_number,
        child.mut.serial_number,
        child.mmt.transaction_date,
        parent.root_work_order
    FROM    order_hierarchy parent
            INNER JOIN apps.mtl_material_transactions child_mmt
                ON parent.serial_number = child_mmt.transaction_source_id
            INNER JOIN apps.wip_entities child_we
                ON child_mmt.organization_id = child_we.organization_id
                AND child_mmt.transaction_source_id = child_we.wip_entity_id
            INNER JOIN apps.mtl_system_items_b child_msi
                ON child_mmt.organization_id = child_msi.organization_id
                AND child_mmt.inventory_item_id = child_msi.inventory_item_id
            LEFT OUTER JOIN apps.mtl_unit_transactions_all_v child_mut
                ON child_mmt.transaction_id = child_mut.transaction_id
                AND child_mmt.organization_id = child_mut.organization_id
                AND child_mmt.inventory_item_id = child_mut.inventory_item_id
    WHERE   child_mmt.transaction_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)
            AND child_msi.serial_number_control_code = 5
            AND child_mmt.transaction_type_id = 35
            AND CASE
                    WHEN child_mut.serial_number IS NOT NULL THEN SIGN(child_mmt.transaction_quantity)
                    ELSE child_mmt.transaction_quantity
            END = -1
)
SELECT * FROM order_hierarchy
-- Quicksight参数过滤：WHERE root_work_order IN (:QUICKSIGHT_WORK_ORDER_PARAM)
;

注：用SIGN()替代原除法运算，逻辑一致但性能更优。

4. 预计算层级数据（ETL方式） #

如果Quicksight需要频繁查询，建议每天定时预计算近3个月的工单层级关系，存储到中间表：

-- 创建预计算表
CREATE TABLE order_hierarchy_precomputed (
    work_order VARCHAR2(50),
    part_number VARCHAR2(50),
    serial_number VARCHAR2(50),
    transaction_date DATE,
    root_work_order VARCHAR2(50),
    hierarchy_level NUMBER
);

-- 刷新数据的存储过程
CREATE OR REPLACE PROCEDURE refresh_order_hierarchy AS
BEGIN
    TRUNCATE TABLE order_hierarchy_precomputed;
    INSERT INTO order_hierarchy_precomputed
    WITH RECURSIVE order_hierarchy AS (
        SELECT  
            we.wip_entity_name AS work_order,
            msi.segment1 AS part_number,
            mut.serial_number,
            mmt.transaction_date,
            we.wip_entity_name AS root_work_order,
            1 AS hierarchy_level
        FROM    apps.mtl_system_items_b msi
                INNER JOIN apps.mtl_material_transactions mmt
                    ON msi.organization_id = mmt.organization_id
                    AND msi.inventory_item_id = mmt.inventory_item_id
                INNER JOIN apps.wip_entities we
                    ON mmt.organization_id = we.organization_id
                    AND mmt.transaction_source_id = we.wip_entity_id
                LEFT OUTER JOIN apps.mtl_unit_transactions_all_v mut
                    ON mmt.transaction_id = mut.transaction_id
                    AND mmt.organization_id = mut.organization_id
                    AND mmt.inventory_item_id = mut.inventory_item_id
        WHERE   mmt.transaction_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)
                AND msi.serial_number_control_code = 5
                AND mmt.transaction_type_id = 35
                AND CASE
                        WHEN mut.serial_number IS NOT NULL THEN SIGN(mmt.transaction_quantity)
                        ELSE mmt.transaction_quantity
                END = -1
        UNION ALL
        SELECT  
            child.we.wip_entity_name AS work_order,
            child.msi.segment1 AS part_number,
            child.mut.serial_number,
            child.mmt.transaction_date,
            parent.root_work_order,
            parent.hierarchy_level + 1
        FROM    order_hierarchy parent
                INNER JOIN apps.mtl_material_transactions child_mmt
                    ON parent.serial_number = child_mmt.transaction_source_id
                INNER JOIN apps.wip_entities child_we
                    ON child_mmt.organization_id = child_we.organization_id
                    AND child_mmt.transaction_source_id = child_we.wip_entity_id
                INNER JOIN apps.mtl_system_items_b child_msi
                    ON child_mmt.organization_id = child_msi.organization_id
                    AND child_mmt.inventory_item_id = child_msi.inventory_item_id
                LEFT OUTER JOIN apps.mtl_unit_transactions_all_v child_mut
                    ON child_mmt.transaction_id = child_mut.transaction_id
                    AND child_mmt.organization_id = child_mut.organization_id
                    AND child_mmt.inventory_item_id = child_mut.inventory_item_id
        WHERE   child_mmt.transaction_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)
                AND child_msi.serial_number_control_code = 5
                AND child_mmt.transaction_type_id = 35
                AND CASE
                        WHEN child_mut.serial_number IS NOT NULL THEN SIGN(child_mmt.transaction_quantity)
                        ELSE child_mmt.transaction_quantity
                END = -1
    )
    SELECT * FROM order_hierarchy;
    COMMIT;
END;
/

-- 创建每天凌晨1点的定时刷新任务
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'REFRESH_ORDER_HIERARCHY',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'refresh_order_hierarchy',
        start_date      => TRUNC(SYSDATE) + 1/24,
        repeat_interval => 'FREQ=DAILY;INTERVAL=1',
        enabled         => TRUE
    );
END;
/

之后Quicksight直接查询order_hierarchy_precomputed表，配合root_work_order字段做参数过滤，查询速度会大幅提升。

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