时间戳

首页 > 解决方案 > 优化oracle SQL UNION查询

sql - 优化oracle SQL UNION查询

问题描述

我正在 Oracle 数据库中创建一个视图，但我对构建它的查询的执行时间有疑问。这是一个相当大的查询，它会带来很多行作为结果。我使用 2 个UNION运算符，一个UNION和一个UNION ALL。我知道这UNION是优化的，但我想知道我是否可以做任何事情让它更快。

我试过改变SELECT查询的顺序，但我发现当前的顺序是最快的。我也检查过Oracle Docs，它建议将UNION ALL运算符更改为FULL OUTER JOINwithNVL运算符。如果您愿意，请在http://www.dba-oracle.com/t_oracle_union.htm 查看它的提示号 7。

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",
        'Update' 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 rec."NewCompareColumn" IS NOT NULL
        AND pr."ProductStatusId" IN (
            '2',
            '6'
        )
    UNION
    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",
        'MTB Row deleted' 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 rec."NewCompareColumn" IS NULL
        AND pr."ProductStatusId" IN (
            '2',
            '6'
        )
    UNION ALL
    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",
        NULL,
        NULL,
        'Invalid Mapping' AS "RequiredAction"
    FROM
        "RaUsageMapping" rsm,
        "RatePlan" rp,
        "RaUsageRecord" rsi,
        "Product" pr
    WHERE
        rsm."UsageRecordId" = rsi."UsageRecordId"
        AND rp."RatePlanId" = rsi."RatePlanId"
        AND pr."InternalCode" = rsm."InternalCode"
        AND pr."ProductStatusId" IN (
            '2',
            '5',
            '6'
        )
        AND ( rsm."InternalCode",
              rsm."AttributeId",
              rsm."CompareColumnValue" ) NOT IN (
            SELECT
                "InternalCode",
                "AttributeId",
                "CompareColumnValue"
            FROM
                "MappingCheckCompareValues"
        );

有什么办法可以减少经过的时间吗？欢迎任何解决方法。

标签： sqloraclequery-optimization

解决方案

第一：您应该使用 ANSI 连接语法来使您的查询更具可读性和更易于维护。

为了加快查询速度，您应该注意到前 2 个查询仅在"NewCompareColumn" is nullor上有所不同，is not null因此您可以使用以下命令轻松地将它们组合成一个查询：

CASE WHEN rec."NewCompareColumn" IS NULL
     THEN 'MTB Row deleted'
     ELSE 'Update'
END AS "RequiredAction"

所以最终结果将如下所示：

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 WHEN rec."NewCompareColumn" IS NULL
         THEN 'MTB Row deleted'
         ELSE 'Update'
    END AS "RequiredAction"
FROM "RaUsageRecord" rsi
JOIN "RaUsageMapping" rsm
  ON rsm."UsageRecordId" = rsi."UsageRecordId"
JOIN "RatePlan" rp
  ON rp."RatePlanId" = rsi."RatePlanId" 
JOIN "MatrixModificationCheck" rec
  ON rec."InternalCode"  = rsm."InternalCode" 
 AND rec."AttributeId"   = rsm."AttributeId" 
 AND rec."CompareColumn" = rsm."CompareColumnValue" 
JOIN "Product" pr
  ON pr."ProductId" = rec."ProductId"
 AND pr."ProductStatusId" IN ('2','6')
UNION ALL
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",
    NULL,
    NULL,
    'Invalid Mapping' AS "RequiredAction"
FROM "RaUsageRecord" rsi
JOIN "RaUsageMapping" rsm
  ON rsm."UsageRecordId" = rsi."UsageRecordId"
JOIN "RatePlan" rp
  ON rp."RatePlanId" = rsi."RatePlanId"
JOIN "Product" pr
  ON pr."InternalCode" = rsm."InternalCode"
 AND pr."ProductStatusId" IN ('2','5','6')
WHERE ( rsm."InternalCode",
          rsm."AttributeId",
          rsm."CompareColumnValue" ) NOT IN (
        SELECT
            "InternalCode",
            "AttributeId",
            "CompareColumnValue"
        FROM
            "MappingCheckCompareValues"
    )

这样一来，您就摆脱了 where 子句中的一个union和一个null-check ，它们都是慢速操作，这可能会大大加快您的查询速度。

推荐阅读