RE: Bad Execution Plan with Left Join and predicates in both tables
Date: Wed, 12 Oct 2016 06:52:38 +0000
Message-ID: <VI1PR07MB13900419F667F07C84BE9F4CA1DD0_at_VI1PR07MB1390.eurprd07.prod.outlook.com>
10053 not 10953...
Sent from my Windows Phone
From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: ý12/ý10/ý2016 07:51
To: thomas.aregger_at_gmail.com<mailto:thomas.aregger_at_gmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Bad Execution Plan with Left Join and predicates in both tables
Some of the ANSI left join functionality comes at a heavy price.
This is one case where looking at the 10953 trace may be beneficial.
Here is one example where the lateral view prevents decorrelation with expensive consequences: https://orastory.wordpress.com/2016/07/06/outer-join-with-or-and-lateral-view-decorrelation/
Sent from my Windows Phone
From: Thomas Aregger<mailto:thomas.aregger_at_gmail.com> Sent: ý11/ý10/ý2016 23:31
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Bad Execution Plan with Left Join and predicates in both tables
Hello
I have a simple query (running on Oracle 12.1.0.2) with a left join and a where clause checking that either a field in the left table is set or a field in the right table is set.
That's how the query looks like:
SELECT *
FROM CUSTOMER C
LEFT JOIN CUSTOMER_DETAIL CD
ON C.ID<http://C.ID> = CD.ID<http://CD.ID>
WHERE (C.LAST_ORDER = 443867 OR CD.LAST_ORDER = 443867)
AND C.STATUS <> 1
/
For some reason Oracle is not able to avoid a full table scan on CUSTOMER, which can be seen in the following execution plan (line 4):
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 4780 (100)| 1 |00:00:01.10 | 33302 |
| 1 | CONCATENATION | | 1 | | | 1 |00:00:01.10 | 33302 |
|* 2 | FILTER | | 1 | | | 0 |00:00:01.10 | 33295 |
| 3 | NESTED LOOPS OUTER | | 1 | 253K| 4778 (1)| 255K|00:00:01.06 | 33295 |
|* 4 | TABLE ACCESS FULL | CUSTOMER | 1 | 253K| 4776 (1)| 255K|00:00:00.32 | 16692 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_DETAIL | 255K| 1 | 1 (0)| 3481 |00:00:00.53 | 16603 |
|* 6 | INDEX RANGE SCAN | I_CUSTDET_ID | 255K| 1 | 1 (0)| 3481 |00:00:00.34 | 15318 | |* 7 | FILTER | | 1 | | | 1 |00:00:00.01 | 7 |
| 8 | NESTED LOOPS OUTER | | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 7 |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 5 | |* 10 | INDEX RANGE SCAN | I_CUST_LAST_ORDER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 4 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_DETAIL | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 2 |
|* 12 | INDEX RANGE SCAN | I_CUSTDET_ID | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("CD"."LAST_ORDER"=443867)
4 - filter("C"."STATUS"<>3)
6 - access("C"."ID"="CD"."ID")
7 - filter(LNNVL("CD"."LAST_ORDER"=443867))
9 - filter("C"."STATUS"<>3)
10 - access("C"."LAST_ORDER"=443867)
12 - access("C"."ID"="CD"."ID")
In the first half of the plan (under the first FILTER operation) I would expect Oracle to use the index on CUSTOMER_DETAIL.LAST_ORDER to access CUSTOMER_DETAIL and then join to the CUSTOMER table. For some reason Oracle is not doing that.
I rewrote the query a little bit:
SELECT *
FROM CUSTOMER C
LEFT JOIN CUSTOMER_DETAIL CD
ON C.ID<http://C.ID> = CD.ID<http://CD.ID>
WHERE (C.LAST_ORDER = 443867)
AND C.STATUS <> 3
UNION
SELECT *
FROM CUSTOMER C
JOIN CUSTOMER_DETAIL CD
ON C.ID<http://C.ID> = CD.ID<http://CD.ID>
WHERE (CD.LAST_ORDER = 443867)
AND C.STATUS <> 3
/
The rewritten query leads to the following execution plan:
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 6 (100)| 1 |00:00:00.01 | 8 |
| 1 | SORT UNIQUE | | 1 | 2 | 6 (34)| 1 |00:00:00.01 | 8 |
| 2 | UNION-ALL | | 1 | | | 1 |00:00:00.01 | 8 |
| 3 | NESTED LOOPS OUTER | | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 6 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | CUSTOMER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 4 | |* 5 | INDEX RANGE SCAN | I_CUST_LAST_ORDER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | CUSTOMER_DETAIL | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 2 |
|* 7 | INDEX RANGE SCAN | I_CUSTDET_ID | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 2 |
| 8 | NESTED LOOPS | | 1 | 1 | 2 (0)| 0 |00:00:00.01 | 2 |
| 9 | NESTED LOOPS | | 1 | 1 | 2 (0)| 0 |00:00:00.01 | 2 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_DETAIL | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 2 |
|* 11 | INDEX RANGE SCAN | I_CUSTDET_LAST_ORDER | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 2 | |* 12 | INDEX UNIQUE SCAN | I_CUST_ID | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | |* 13 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | ------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
4 - filter("C"."STATUS"<>3)
5 - access("C"."LAST_ORDER"=443867)
7 - access("C"."ID"="CD"."ID")
11 - access("CD"."LAST_ORDER"=443867)
12 - access("C"."ID"="CD"."ID")
13 - filter("C"."STATUS"<>3)
The second plan is obviously a lot more efficient. Am I missing something, or is it just an opimizer limitation in the first version of the query?
Regards
Thomas
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 12 2016 - 08:52:38 CEST