CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Fotis Papadamis
Fotis Papadamis

Reputation: 303

Optimize oracle SQL UNION query

I am creating a view in an Oracle Database and I have a problem regarding the execution time of the query that builds it. It's quite big a query which brings a lot of rows as results. I use 2 UNION operators, one UNION and one UNION ALL. I know that UNION is optimized but I want to know if I can do anything to make it even faster.

I've tried changing the order of the SELECT queries but I find the current order to be the fastest. I've also checked on Oracle Docs and it recommends changing the UNION ALL operator to a FULL OUTER JOIN with NVL operator. If you want, check it out at http://www.dba-oracle.com/t_oracle_union.htm it's tip number 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"
        );

Is there any way that I can reduce the elapsed time? Any workarounds are welcome.

Upvotes: 0

Views: 2074

Answers (2)

Radagast81
Radagast81

Reputation: 3006

First: You should use ANSI join syntax to make your query more readable and easier to maintain.

To speed up your query you should notice that the first 2 queries only differ in the "NewCompareColumn" is null or is not null so you can easily combine them to one query using:

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

So the final result will look like this:

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"
    )

As by this you get rid of one union and a null-check in the where clause which are both slow operations this might speed up your query a lot.

Upvotes: 3

Popeye
Popeye

Reputation: 35910

First obvious suggestion to make optimized query is to remove subqueries and use that table in join.

( -- remove this subquery
            SELECT
                "Description"
            FROM
                "Attribute"
            WHERE
                "AttributeId" = rsm."AttributeId"
        ) "Attribute",

As an example you can remove above subquery and use Attribute table in join.

There are total 4 subqueries, remove all and use it in join.

In simple terms, Subqueries are executed one per record so if your query is returning 1 million record then subquery will be executed 1 million times and makes your query slower.

Cheers!!

Upvotes: 1

Related Questions