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?
B
table the plan reports that all 1000 rows will be fetched. Execution plans seem the same in terms of cost only for this simple example i devised to demonstrate the problem. In reality it causes full table scans on multi-million tables even though a fast index path could be used.