Self join elimination in 11.2, and a hint that does not work

BY: Brian Fitzgerald


[oracle@waipio1 wailua20 optimizer]$ sqlplus oe/oe @ self.join.elimination.SQL

SQL*Plus: RELEASE 12.1.0.1.0 Production ON Wed Jul 2 00:38:38 2014

Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.

LAST SUCCESSFUL login TIME: Wed Jul 02 2014 00:37:57 -04:00

Connected TO:
Oracle DATABASE 12c Enterprise Edition RELEASE 12.1.0.1.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
AND REAL Application Testing options

SQL> ALTER SESSION SET optimizer_features_enable = '11.1.0.7';

SESSION altered.

SQL> SET termout off
SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor(NULL, NULL, 'projection') );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hynqpxakhr75, child NUMBER 0
-------------------------------------
SELECT s2.order_id, s1.order_mode, s2.order_mode FROM orders s1 join
orders s2 ON s1.order_id = s2.order_id

PLAN hash VALUE: 915117078

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | ROWS  | Bytes | COST (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     7 (100)|          |
|   1 |  MERGE JOIN                  |          |   105 |  2310 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORDERS   |   105 |  1155 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | ORDER_PK |   105 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |   105 |  1155 |     5  (20)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | ORDERS   |   105 |  1155 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   4 - ACCESS("S1"."ORDER_ID"="S2"."ORDER_ID")
       filter("S1"."ORDER_ID"="S2"."ORDER_ID")

COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------

   1 - "S2"."ORDER_ID"[NUMBER,22], "S1"."ORDER_MODE"[VARCHAR2,8],
       "S2"."ORDER_MODE"[VARCHAR2,8]
   2 - "S1"."ORDER_ID"[NUMBER,22], "S1"."ORDER_MODE"[VARCHAR2,8]
   3 - "S1".ROWID[ROWID,10], "S1"."ORDER_ID"[NUMBER,22]
   4 - (#keys=1) "S2"."ORDER_ID"[NUMBER,22], "S2"."ORDER_MODE"[VARCHAR2,8]
   5 - "S2"."ORDER_ID"[NUMBER,22], "S2"."ORDER_MODE"[VARCHAR2,8]


34 ROWS selected.

SQL>
SQL> ALTER SESSION SET optimizer_features_enable = '11.2.0.1';

SESSION altered.

SQL> SET termout off
SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor(NULL, NULL, 'projection') );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hynqpxakhr75, child NUMBER 1
-------------------------------------
SELECT s2.order_id, s1.order_mode, s2.order_mode FROM orders s1 join
orders s2 ON s1.order_id = s2.order_id

PLAN hash VALUE: 1275100350

----------------------------------------------------------------------------
| Id  | Operation         | Name   | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS FULL| ORDERS |   105 |  1155 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------

COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------

   1 - "S1"."ORDER_ID"[NUMBER,22], "ORDER_MODE"[VARCHAR2,8]


19 ROWS selected.

SQL>
SQL> -- a hint that is not working
SQL> SET termout off
SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor(NULL, NULL, 'projection') );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  596y43af1t1xh, child NUMBER 0
-------------------------------------
SELECT /*+ NO_ELIMINATE_JOIN */ s2.order_id, s1.order_mode,
s2.order_mode FROM orders s1 join orders s2 ON s1.order_id = s2.order_id

PLAN hash VALUE: 1275100350

----------------------------------------------------------------------------
| Id  | Operation         | Name   | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS FULL| ORDERS |   105 |  1155 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------

COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------

   1 - "S1"."ORDER_ID"[NUMBER,22], "ORDER_MODE"[VARCHAR2,8]


19 ROWS selected.

SQL>
SQL>
SQL> quit
Disconnected FROM Oracle DATABASE 12c Enterprise Edition RELEASE 12.1.0.1.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
AND REAL Application Testing options