在我们将Oracle数据库从12 c迁移到19 c之前，我正在进行测试。我遇到了一个不寻常的行为，这可以用下面的例子来解释。我已经将它浓缩为下面的可重复问题。很抱歉让它很长，我想提供所有可能的信息。如果需要任何进一步的信息，那么我很乐意提供。
Oracle 12 c和19 c版本如下（来自v$instance）：

VERSION
12.1.0.2.0
VERSION      VERSION_FULL
19.0.0.0.0   19.16.0.0.0

示例数据

2表如下x1c 0d1x

TAB1
COLUMN_NAME DATA_TYPE   NULLABLE
COL1    VARCHAR2(20 BYTE)   Yes
RUL_NO  NUMBER(11,0)    No
INP_DT  TIMESTAMP(6) WITH LOCAL TIME ZONE   No

TAB2
COLUMN_NAME DATA_TYPE   NULLABLE
COL1    VARCHAR2(20 BYTE)   No
COL6    NUMBER(11,0)    No
COL7    VARCHAR2(5 BYTE)    Yes
INP_DT  TIMESTAMP(6) WITH LOCAL TIME ZONE   No

Index on TAB2 -
create index tab2_IDX1 on tab2(col6);
create index tab2_IDX2 on tab2(col1);

问题SQL

SELECT  *
FROM    tab1 t
WHERE   (EXISTS (SELECT 1
                 FROM   tab2 b
                 WHERE  b.col6       = 1088609
                 AND    NVL(t.col1, '<NULL>') = NVL(b.col1, '<NULL>'))
        OR t.col1 IS NULL);

此sql在12 c数据库上返回10行，但在19 c数据库上没有返回任何行，这将导致19 c端出现回归。
下面是在跟踪模式下运行此sql时的输出。

12 c追踪

SQL> set autotrace traceonly
SQL> set linesize 200
SQL> set pagesize 1000
SQL> SELECT  *
FROM    tab1 t
WHERE   (EXISTS (SELECT 1
                 FROM   tab2 b
                 WHERE  b.col6       = 1088609
                 AND    NVL(t.col1, '<NULL>') = NVL(b.col1, '<NULL>'))
        OR t.col1 IS NULL);
  2    3    4    5    6    7
10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 572408916

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |    10 |   160 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                              |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL                  | TAB1      |    10 |   160 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB2      |     1 |    15 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | TAB2_IDX3 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter("T"."COL1" IS NULL OR  EXISTS (SELECT 0 FROM "TAB2" "B" WHERE
              "B"."COL1"=NVL(:B1,'<NULL>') AND "B"."COL6"=1088609))
   3 - filter("B"."COL6"=1088609)
   4 - access("B"."COL1"=NVL(:B1,'<NULL>'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)

19 c追踪

SQL> set autotrace traceonly
SQL> set linesize 200
SQL> set pagesize 1000
SQL>
SQL> SELECT  *
FROM    tab1 t
WHERE   (EXISTS (SELECT 1
                 FROM   tab2 b
                 WHERE  b.col6       = 1088609
                 AND    NVL(t.col1, '<NULL>') = NVL(b.col1, '<NULL>'))
        OR t.col1 IS NULL);
  2    3    4    5    6    7
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4175419084

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     1 |    31 |     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI NA                   |           |     1 |    31 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | TAB1      |    10 |   160 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB2      |     1 |    15 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | TAB2_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - access(NVL("T"."COL1",'<NULL>')="B"."COL1")
   4 - access("B"."COL6"=1088609)

Note
-----
   - this is an adaptive plan

有人能解释一下为什么在19 c中会观察到这种行为吗？因为它应该像12 c db一样返回10行。看起来是19 c端的HASH JOIN SEMI NA步骤导致了这个问题，但我不能肯定。非常感谢对此问题的任何帮助。
谢谢你，凯拉什