Oracle 不使用傳遞閉包來添加額外的謂詞

提出這樣的查詢：

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

Oracle 優化器可以使用傳遞閉包屬性並結合兩個謂詞a.val = b.val並a.val = 1推導出另一個：b.val = 1. 可以在一個執行計劃的 Predicate Information 中看到：

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

但是，在另一個環境（相同的 Oracle 版本 - 12.1.0.2）上，我無法重現相同的行為：

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

由於缺少謂詞，因此未使用索引。報告的基數也不正確。

有沒有辦法控制這種行為，可能通過優化器參數？

我發現該參數_optimizer_filter_pushdown設置為false也影響傳遞謂詞生成，就像_optimizer_generate_transitive_predBalazs Papp 建議的那樣。將其設置為 true 後，問題就消失了：

alter session set "_optimizer_filter_pushdown"=true;

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)

和參數：

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)

引用自：https://dba.stackexchange.com/questions/261168