Oracle does not use transitive closure to add additional predicate

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 |
-

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)

StackExchange Database Administrators Q#261168, answer score: 4