4

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?

3
  • Is the estimated number of rows correct for the other environment? But as both execution plans are identical in terms of the steps they do, I don't see why that would be a problem to begin with? – a_horse_with_no_name Mar 4 '20 at 8:35
  • Estimated number of rows is incorrect in the second case. Since there is no predicate on the 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. – Adamantium Mar 4 '20 at 8:42
  • 2
    Well, if the plans do deviate in reality, you should show us an example where this is actually a problem. – a_horse_with_no_name Mar 4 '20 at 13:10
4
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)
1

I found that parameter _optimizer_filter_pushdown was set to false which also affects transitive predicate generation, just like _optimizer_generate_transitive_pred that Balazs Papp suggested. After setting it to true the problem goes away:

alter session set "_optimizer_filter_pushdown"=true;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.