10053 not 10953...
Sent from my Windows Phone
________________________________
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 12/10/2016 07:51
To: thomas.aregger@xxxxxxxxx<mailto:thomas.aregger@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx>
Sent: 11/10/2016 23:31
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
