sql - 存在执行计划太昂贵的情况

Question

我有以下查询，但是当我执行它时它会永远运行。

   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;

我的执行计划显示了一个我找不到的巨大成本。我该如何优化这个查询？我尝试了一些提示，但没有一个有效:(

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

如果需要任何其他信息，请告诉我。

Answer 1

使用窗口函数:

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');

不仅查询的编写和读取更简单，而且性能也应该更好。