Execution plan too expensive case when exists

user_16119285 0
Viewed 55

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.

oracle sql
3 Answers

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.

user_11440350

Highest cost is due to FTS(Full table scan) on table/MV MV_INTEGRATED_MD.

Try to create index on contract_status and check if it reduces the cost and also, what is size of this mv/table in terms of block and it is 10 percent or more than total buffer cache size ?

TABLE ACCESS STORAGE FULL| MV_INTEGRATED_MD            |    18M|   974M|   759K  (1)| 00:00:30 |     1 |     1
user_109082740

If you run your query with the /*+ gather_plan_statistics */ hint (I'm simulating it with a 1000 row table) you imediately see the problem :

 select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |      1 |        |   1000 |00:00:00.01 |       9 |      5 |
|*  1 |  VIEW                                    |                             |   1000 |   1000 |   1000 |00:00:00.09 |       0 |      0 |
|   2 |   TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6737_1A17DE13 |   1000 |   1000 |    500K|00:00:00.08 |       0 |      0 |
|   3 |  TEMP TABLE TRANSFORMATION               |                             |      1 |        |   1000 |00:00:00.01 |       9 |      5 |
|   4 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6737_1A17DE13 |      1 |        |      0 |00:00:00.01 |       8 |      5 |
|*  5 |    TABLE ACCESS FULL                     | MV_INTEGRATED_MD            |      1 |   1000 |   1000 |00:00:00.01 |       7 |      5 |
|   6 |   VIEW                                   |                             |      1 |   1000 |   1000 |00:00:00.01 |       0 |      0 |
|   7 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6737_1A17DE13 |      1 |   1000 |   1000 |00:00:00.01 |       0 |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("B"."BUSINESS_PARTNER"=:B1 AND "B"."INSTALLATION"=:B2 AND "B"."PAYMENT_PLAN"='BMW'))
   5 - filter("CONTRACT_STATUS"='LIVE')

It is in the line 2 where a full scan is activated in a loop for each line of the main table (see starts = 1000)

Typically you want to resolve the EXISTS with a semi join to preserve good performance, but here it seems that Oracle can not rewrite it.

So you'll need to rewrite the query yourself.

Despite the excelent proposal of @GordonLinoff (that I'll start with) you may try to use an outer join as follows

with bmw as (
select distinct business_partner, installation 
from mv_integrated_md 
where payment_plan = 'BMW')
SELECT
            a.contract,
            a.contract_account,
            a.business_partner,
            a.payment_plan,
            a.installation,
            a.contract_status,
            case when b.business_partner is not null then 'X' end as h
FROM  mv_integrated_md a
left outer join bmw b 
on  b.business_partner = a.business_partner and
    b.installation = a.installation
WHERE a.contract_status IN ( 'LIVE', 'FINAL')

This will lead to two fulls scans, one deduplication and outer join.

user_48081220
Related