Oracle does not use transitive closure to add additional predicate

execution-planoptimizationoracleoracle-12c

Presented with a query like this:

select *
from a, b
where a.val = b.val
 and a.val = 1

Oracle optimizer can use transitive closure property and combine two predicates a.val = b.val and a.val = 1 to deduce another: b.val = 1. It can be seen in Predicate Information of an execution plan:

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |   600 |  3600 |     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN                           |       |   600 |  3600 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| B     |    20 |    60 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | VAL_I |    20 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL                  | A     |    30 |    90 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."VAL"="B"."VAL")
   3 - access("B"."VAL"=1)
   4 - filter("A"."VAL"=1)

However, on another environment (same Oracle version – 12.1.0.2) I cannot reproduce the same behavior:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   360 |  2160 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   360 |  2160 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |    30 |    90 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |  1000 |  3000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."VAL"="B"."VAL")
   2 - filter("A"."VAL"=1)

Because of the missing predicate the index is not used. Reported cardinality is also incorrect.

Is there a way to control this behavior, possibly via an optimizer parameter?

Best Answer

create table a(val number);
create table b(val number);
explain plan for select * from a, b where a.val = b.val and a.val = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 652036164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."VAL"="B"."VAL")
   2 - filter("A"."VAL"=1)
   3 - filter("B"."VAL"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

And the parameter:

SQL> alter session set "_optimizer_generate_transitive_pred"=false;

Session altered.

SQL> explain plan for select * from a, b where a.val = b.val and a.val = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 652036164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."VAL"="B"."VAL")
   2 - filter("A"."VAL"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Related Question