全外连接场景下Oracle分区裁剪未生效的问题咨询

这其实是Oracle优化器在全外连接场景下的已知行为限制，并不是你的操作有误，下面给你详细拆解：

问题本质：全外连接语义与谓词下推的冲突 #

全外连接的核心语义是保留两个表中所有不匹配的行，Oracle优化器默认会假设：如果把d = date '2018-01-03'这个谓词提前推到单个表的扫描阶段，可能会破坏全外连接的完整性——比如假设t1中没有该日期的行，但t2有，优化器担心提前过滤t1会导致无法保留t2中该行的连接结果。

但在你的特定场景中，最终的WHERE条件是过滤连接列等于固定值，逻辑上提前过滤每个表的对应分区是完全安全的：只有d='2018-01-03'的行才会出现在最终结果里，两边其他分区的行即使参与连接，也会被后续过滤掉。不过Oracle 12.1.0.2的优化器规则并没有识别到这个特殊场景，因此不会自动执行谓词下推。

验证手动下推的正确性 #

你手动将谓词下推到子查询的做法是完全正确的，执行计划也证明了这一点：单分区扫描（PARTITION RANGE SINGLE）替代了全分区扫描（PARTITION RANGE ALL），性能会有明显提升，而且结果和原查询完全一致。

替代方案：使用优化器提示强制下推 #

除了改写SQL为子查询形式，你还可以通过添加优化器提示来让Oracle自动完成谓词下推，无需修改SQL结构：

select /*+ PUSH_PRED(t1) PUSH_PRED(t2) */ * 
from t1 full join t2 using(d) 
where d = date '2018-01-03';

这个提示会强制优化器将WHERE子句中的过滤谓词推送到t1和t2的表访问阶段，从而触发单分区扫描，达到和手动下推相同的执行计划效果。

版本说明 #

这个限制在Oracle 12.1.0.2中是明确存在的，后续版本（如12cR2、19c及以上）对全外连接的谓词下推逻辑有优化，可能会自动识别这种场景并执行下推，但如果你仍在使用12.1.0.2，手动下推或添加提示是最可靠的解决方案。

附：你的测试用例（格式化后） #

测试表创建语句

create table t1(
 d date not null,
 n number not null
) partition by range(d)(
 partition jan1 values less than(date '2018-01-02'),
 partition jan2 values less than(date '2018-01-03'),
 partition jan3 values less than(date '2018-01-04')
);

create table t2(
 d date not null,
 n number not null
) partition by range(d)(
 partition jan1 values less than(date '2018-01-02'),
 partition jan2 values less than(date '2018-01-03'),
 partition jan3 values less than(date '2018-01-04')
);

insert into t1(d,n) values(date '2018-01-01', 1);
insert into t1(d,n) values(date '2018-01-02', 2);
insert into t2(d,n) values(date '2018-01-02', 2);
insert into t2(d,n) values(date '2018-01-03', 3);
commit;

原查询执行计划

select * from t1 full join t2 using(d) where d = date '2018-01-03';
-----------------------------------------------------------
| Id  | Operation           | Name     | Pstart| Pstop |
-----------------------------------------------------------
|   0 | SELECT STATEMENT    |          |       |       |
|   1 |  PARTITION RANGE ALL|          |     1 |     3 |
|*  2 |   VIEW              | VW_FOJ_0 |       |       |
|*  3 |    HASH JOIN FULL OUTER|      |       |       |
|   4 |     TABLE ACCESS FULL| T1      |     1 |     3 |
|   5 |     TABLE ACCESS FULL| T2      |     1 |     3 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("D"=TO_DATE(' 2018-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("T1"."D"="T2"."D")

手动下推后执行计划

select * from (select * from t1 where d = date '2018-01-03') full join (select * from t2 where d = date '2018-01-03') using(d);
-------------------------------------------------------------
| Id  | Operation           | Name     | Pstart| Pstop |
-------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |       |       |
|   1 |  VIEW              | VW_FOJ_0 |       |       |
|*  2 |   HASH JOIN FULL OUTER|      |       |       |
|   3 |    PARTITION RANGE SINGLE|      |     3 |     3 |
|   4 |     VIEW            |          |       |       |
|*  5 |      TABLE ACCESS FULL| T1      |     3 |     3 |
|   6 |    PARTITION RANGE SINGLE|      |     3 |     3 |
|   7 |     VIEW            |          |       |       |
|*  8 |      TABLE ACCESS FULL| T2      |     3 |     3 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."D"="from$_subquery$_003"."D")
   5 - filter("D"=TO_DATE(' 2018-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - filter("D"=TO_DATE(' 2018-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

内容的提问来源于stack exchange，提问作者Ronnis