Oracle does not use transitive closure to add additional predicate
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)