Execution plan too expensive case when exists

I have the below query, but when I execute it runs forever.

   WITH aux AS (
        SELECT
            contract,
            contract_account,
            business_partner,
            payment_plan,
            installation,
            contract_status
        FROM
            reta.mv_integrated_md a
        WHERE
            contract_status IN (
                'LIVE',
                'FINAL'
            )
    ), aux1 AS (
        SELECT
            a.*,
            CASE
                WHEN EXISTS (
                    SELECT
                        NULL
                    FROM
                        aux b
                    WHERE
                        b.business_partner = a.business_partner
                        AND b.installation = a.installation
                        AND b.payment_plan = 'BMW'
                ) THEN
                    'X'
            END h
        FROM
            aux a
    )
    SELECT
        *
    FROM
        aux1;

My execution plan shows a huge cost which I cannot locate. How could I optimize this query? I have tried some hints but none of them have worked 🙁

Plan hash value: 1662974027
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |    19M|  2000M|   825G  (1)|999:59:59 |       |       |
|*  1 |  VIEW                        |                             |    19M|   990M| 41331   (1)| 00:00:02 |       |       |
|   2 |   TABLE ACCESS STORAGE FULL  | SYS_TEMP_0FDA49C92_9A7BE8DE |    19M|  1066M| 41331   (1)| 00:00:02 |       |       |
|   3 |  TEMP TABLE TRANSFORMATION   |                             |       |       |            |          |       |       |
|   4 |   LOAD AS SELECT             | SYS_TEMP_0FDA49C92_9A7BE8DE |       |       |            |          |       |       |
|   5 |    PARTITION RANGE SINGLE    |                             |    18M|   974M|   759K  (1)| 00:00:30 |     1 |     1 |
|*  6 |     TABLE ACCESS STORAGE FULL| MV_INTEGRATED_MD            |    18M|   974M|   759K  (1)| 00:00:30 |     1 |     1 |
|   7 |   VIEW                       |                             |    19M|  2000M| 41331   (1)| 00:00:02 |       |       |
|   8 |    TABLE ACCESS STORAGE FULL | SYS_TEMP_0FDA49C92_9A7BE8DE |    19M|  1066M| 41331   (1)| 00:00:02 |       |       |
----------------------------------------------------------------------------------------------------------------------------

Kindly let me know if any additional information needed.

Answer

Use window functions:

SELECT r.contract, r.contract_account, r.business_partner,
       r.payment_plan, r.installation, r.contract_status,
       MAX(CASE WHEN r.payment_plan = 'BMW' THEN 'X' END) OVER (PARTITION BY business_partner, installation) as h
FROM reta.mv_integrated_md@rbip r
WHERE r.contract_status IN ('LIVE', 'FINAL');

Not only is the query much simpler to write and read, but it should perform much better too.

Advertisement

Answer