Oracle 19c哈希连接执行计划中为何出现SUBSTR谓词？

环境 #

Oracle数据库19.20版本

测试用例 #

1. 创建表与索引：

SQL> create table t100 as select * from dba_objects;
SQL> create index idx_substr_object_name on t100 (substr(object_name,1,4));
SQL> create index idx_object_name on t100(object_name);
SQL> create table t200 as select * from dba_objects;
SQL> create index idx_t200 on t200(object_name);

2. 执行查询并获取真实执行计划：

SQL> select /*+use_hash(a) use_hash(b)*/ count(*) from t100 a,t200 b where a.object_name=b.object_name;

  COUNT(*)
----------
    705590

SQL> set lines 1000 pages 1000

SQL> select * from table(dbms_xplan.display_cursor(FORMAT=>'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------
SQL_ID  bpkc4rb5vu9y3, child number 0
-------------------------------------
select /*+use_hash(a) use_hash(b)*/ count(*) from t100 a,t200 b where
a.object_name=b.object_name

Plan hash value: 3047534014

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |   |   |   |   696 (100)|      |
|   1 |  SORT AGGREGATE        |         |     1 |    76 |   |        |      |
|*  2 |   HASH JOIN        |         |   111K|  8294K|  3792K|   696   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| IDX_T200    | 86239 |  2779K|   |   143   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86159 |  3618K|   |   143   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME" AND
          SUBSTR("OBJECT_NAME",1,4)=SUBSTR("B"."OBJECT_NAME",1,4)) 

技术问询 #

该查询的连接条件仅为a.object_name=b.object_name，为何执行计划的谓词信息中会出现SUBSTR("OBJECT_NAME",1,4)=SUBSTR("B"."OBJECT_NAME",1,4)？

这是Oracle优化器的等价谓词推导行为导致的。

你在t100表上创建了基于substr(object_name,1,4)的函数索引idx_substr_object_name，优化器会自动推导逻辑等价条件：如果a.object_name = b.object_name成立，那么substr(a.object_name,1,4) = substr(b.object_name,1,4)必然也成立。

优化器将这个推导出来的条件加入连接谓词，是为了尝试能否利用到该函数索引——尽管最终执行计划选择了idx_object_name和idx_t200的索引快速全扫描，但推导过程已经完成，所以这个额外谓词会出现在执行计划的Predicate Information中。

这个推导出来的条件不会影响查询结果的正确性，只是优化器在寻找最优执行路径过程中产生的产物。

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