Oracle数据库视图UNION类查询执行耗时优化咨询

针对你遇到的视图构建查询耗时过长的问题，结合你的SQL结构，我整理了几个优先级较高的优化方向，你可以逐步尝试：

1. 合并重复的UNION查询 #

你的前两个SELECT语句逻辑几乎完全一致，唯一的区别是rec."NewCompareColumn"的判断条件（IS NOT NULL vs IS NULL）以及对应的RequiredAction值。重复执行两次几乎相同的表连接会浪费大量资源，完全可以用CASE表达式合并成一个查询，只执行一次连接操作：

SELECT 
    rp."RatePlanCode", 
    rsi."ProductCode", 
    rsi."ProductDescription", 
    rsi."SPCode", 
    rsi."SPDescription", 
    rsi."SNCode", 
    rsi."SNDescription", 
    rsi."ZNCode", 
    rsi."ZNDescription", 
    rsi."UsageIndicatorShortDesc", 
    pr."ProductName", 
    pr."ProductTypeId", 
    rsm."InternalCode", 
    (SELECT "Description" FROM "Attribute" WHERE "AttributeId" = rsm."AttributeId") "Attribute", 
    rsm."AttributeId", 
    rsm."CompareColumnValue" "OldCompareValue", 
    rec."NewCompareColumn", 
    rec."Timestamp" "ModificationDate",
    -- 用CASE替代两个分支的SELECT
    CASE 
        WHEN rec."NewCompareColumn" IS NOT NULL THEN 'Update'
        ELSE 'MTB Row deleted'
    END AS "RequiredAction"
FROM 
    "RaUsageMapping" rsm, 
    "RatePlan" rp, 
    "RaUsageRecord" rsi, 
    "MatrixModificationCheck" rec, 
    "Product" pr 
WHERE 
    rsm."UsageRecordId" = rsi."UsageRecordId" 
    AND pr."ProductId" = rec."ProductId" 
    AND rp."RatePlanId" = rsi."RatePlanId" 
    AND rsm."InternalCode" = rec."InternalCode" 
    AND rsm."AttributeId" = rec."AttributeId" 
    AND rsm."CompareColumnValue" = rec."CompareColumn" 
    AND pr."ProductStatusId" IN ( '2', '6' )

这一步能直接减少一半的表扫描和连接开销，是最立竿见影的优化。

2. 替换NOT IN为NOT EXISTS #

你的第三个SELECT中使用了NOT IN子查询，这种写法在Oracle中性能通常不如NOT EXISTS，尤其是当子查询返回的字段可能包含NULL时（NOT IN会因为NULL导致整个结果集为空）。改用NOT EXISTS可以更好地利用索引，提升查询效率：

-- 原NOT IN部分
AND ( rsm."InternalCode", rsm."AttributeId", rsm."CompareColumnValue" ) NOT IN ( 
    SELECT "InternalCode", "AttributeId", "CompareColumnValue" FROM "MappingCheckCompareValues" 
)

-- 替换为NOT EXISTS
AND NOT EXISTS (
    SELECT 1 FROM "MappingCheckCompareValues" mccv
    WHERE mccv."InternalCode" = rsm."InternalCode"
      AND mccv."AttributeId" = rsm."AttributeId"
      AND mccv."CompareColumnValue" = rsm."CompareColumnValue"
)

3. 将相关子查询转为JOIN #

你SQL中的(SELECT "Description" FROM "Attribute" WHERE "AttributeId" = rsm."AttributeId")是相关子查询，意味着每返回一行结果就要执行一次这个子查询，当结果集很大时开销极高。把它改成直接的JOIN操作，只需要扫描一次Attribute表：

-- 原写法
(SELECT "Description" FROM "Attribute" WHERE "AttributeId" = rsm."AttributeId") "Attribute"

-- 改为JOIN，在FROM子句中加入Attribute表
FROM 
    "RaUsageMapping" rsm, 
    "RatePlan" rp, 
    "RaUsageRecord" rsi, 
    "Product" pr,
    "Attribute" attr -- 新增JOIN的表
WHERE 
    rsm."AttributeId" = attr."AttributeId" -- 关联条件
    -- 其他原有条件...

然后在SELECT列表中直接用attr."Description" AS "Attribute"即可。

4. 优化索引配置 #

上述优化的效果很大程度依赖于合适的索引，建议为以下字段创建联合索引（覆盖连接条件和查询中需要返回的字段，减少回表）：

• RaUsageMapping: (UsageRecordId, InternalCode, AttributeId, CompareColumnValue)
• RatePlan: (RatePlanId, RatePlanCode)
• RaUsageRecord: (UsageRecordId, RatePlanId, ProductCode, ProductDescription, SPCode, SPDescription, SNCode, SNDescription, ZNCode, ZNDescription, UsageIndicatorShortDesc)
• MatrixModificationCheck: (ProductId, InternalCode, AttributeId, CompareColumn, NewCompareColumn, Timestamp)
• Product: (ProductId, InternalCode, ProductName, ProductTypeId, ProductStatusId)
• MappingCheckCompareValues: (InternalCode, AttributeId, CompareColumnValue)

5. 关于FULL OUTER JOIN替代UNION ALL的尝试 #

你提到的用FULL OUTER JOIN搭配NVL替代UNION ALL的方案，更适合两个结果集有重叠或需要关联的场景。你的第三个查询是筛选未出现在MappingCheckCompareValues中的记录，和前两个查询的结果集没有交集（前两个都关联了MatrixModificationCheck），所以UNION ALL本身是高效的。如果要尝试，需要找到两个结果集的关联键，但实际收益可能不如前面的优化点，建议优先完成前4步后再测试。

整合后的完整优化SQL示例 #

-- 合并前两个UNION分支
WITH base_updates AS (
    SELECT 
        rp."RatePlanCode", 
        rsi."ProductCode", 
        rsi."ProductDescription", 
        rsi."SPCode", 
        rsi."SPDescription", 
        rsi."SNCode", 
        rsi."SNDescription", 
        rsi."ZNCode", 
        rsi."ZNDescription", 
        rsi."UsageIndicatorShortDesc", 
        pr."ProductName", 
        pr."ProductTypeId", 
        rsm."InternalCode", 
        attr."Description" AS "Attribute", 
        rsm."AttributeId", 
        rsm."CompareColumnValue" "OldCompareValue", 
        rec."NewCompareColumn", 
        rec."Timestamp" "ModificationDate",
        CASE 
            WHEN rec."NewCompareColumn" IS NOT NULL THEN 'Update'
            ELSE 'MTB Row deleted'
        END AS "RequiredAction"
    FROM 
        "RaUsageMapping" rsm
        JOIN "RaUsageRecord" rsi ON rsm."UsageRecordId" = rsi."UsageRecordId"
        JOIN "RatePlan" rp ON rp."RatePlanId" = rsi."RatePlanId"
        JOIN "MatrixModificationCheck" rec ON rsm."InternalCode" = rec."InternalCode" 
                                          AND rsm."AttributeId" = rec."AttributeId" 
                                          AND rsm."CompareColumnValue" = rec."CompareColumn"
        JOIN "Product" pr ON pr."ProductId" = rec."ProductId"
        JOIN "Attribute" attr ON rsm."AttributeId" = attr."AttributeId"
    WHERE 
        pr."ProductStatusId" IN ( '2', '6' )
),
invalid_mappings AS (
    SELECT 
        rp."RatePlanCode", 
        rsi."ProductCode", 
        rsi."ProductDescription", 
        rsi."SPCode", 
        rsi."SPDescription", 
        rsi."SNCode", 
        rsi."SNDescription", 
        rsi."ZNCode", 
        rsi."ZNDescription", 
        rsi."UsageIndicatorShortDesc", 
        pr."ProductName", 
        pr."ProductTypeId", 
        rsm."InternalCode", 
        attr."Description" AS "Attribute", 
        rsm."AttributeId", 
        rsm."CompareColumnValue" "OldCompareValue", 
        NULL AS "NewCompareColumn", 
        NULL AS "ModificationDate",
        'Invalid Mapping' AS "RequiredAction"
    FROM 
        "RaUsageMapping" rsm
        JOIN "RaUsageRecord" rsi ON rsm."UsageRecordId" = rsi."UsageRecordId"
        JOIN "RatePlan" rp ON rp."RatePlanId" = rsi."RatePlanId"
        JOIN "Product" pr ON pr."InternalCode" = rsm."InternalCode"
        JOIN "Attribute" attr ON rsm."AttributeId" = attr."AttributeId"
    WHERE 
        pr."ProductStatusId" IN ( '2', '5', '6' )
        AND NOT EXISTS (
            SELECT 1 FROM "MappingCheckCompareValues" mccv
            WHERE mccv."InternalCode" = rsm."InternalCode"
              AND mccv."AttributeId" = rsm."AttributeId"
              AND mccv."CompareColumnValue" = rsm."CompareColumnValue"
        )
)
SELECT * FROM base_updates
UNION ALL
SELECT * FROM invalid_mappings;

这里用CTE（公共表表达式）拆分逻辑，让SQL更易读，同时保持执行效率。

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