Bad Execution Plan with Left Join and predicates in both tables

From: Thomas Aregger <thomas.aregger_at_gmail.com>
Date: Wed, 12 Oct 2016 00:29:41 +0200
Message-ID: <CAOJe5KhBULuUPM-fBpQZYWRwY8Wdk5ptCXo-jxbboewG+JGfHw_at_mail.gmail.com>

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 = 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 = CD.ID
 WHERE (C.LAST_ORDER = 443867)
   AND C.STATUS <> 3
UNION
SELECT *
  FROM CUSTOMER C
  JOIN CUSTOMER_DETAIL CD
    ON C.ID = 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-l

Received on Wed Oct 12 2016 - 00:29:41 CEST