Oracle 12c迁移至19c后SQL查询结果异常求助

版本信息 #

-- 12c版本
VERSION
12.1.0.2.0

-- 19c版本
VERSION      VERSION_FULL
19.0.0.0.0   19.16.0.0.0

表结构与索引 #

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

-- 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在12c环境返回10行数据，但在19c环境无结果返回，导致迁移后功能退化。

执行计划对比 #

12c执行计划 #

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)

19c执行计划 #

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

问题原因分析 #

问题核心在于19c优化器对SQL的改写逻辑与12c存在差异：

1. 12c执行逻辑：采用FILTER操作，遍历TAB1每一行时直接判断两个独立条件：

   • 若t.col1 IS NULL，直接保留该行；
   • 若存在匹配的TAB2行（b.col6=1088609且NVL(t.col1, '<NULL>')=NVL(b.col1, '<NULL>')），则保留该行。
     该逻辑完全符合SQL原始语义，因此能正确返回t.col1 IS NULL的行。

2. 19c执行逻辑：优化器将原始SQL改写为HASH JOIN SEMI NA（半连接）操作，错误地将OR t.col1 IS NULL条件合并到依赖TAB2的连接条件中，仅保留能匹配NVL(t.col1, '<NULL>')=b.col1的行。
   由于TAB2.COL1是非空字段，且数据中不存在col1='<NULL>'的行，导致t.col1 IS NULL的行无法匹配到任何TAB2行，最终被过滤，无结果返回。

解决方案 #

方案1：改写SQL，明确拆分OR条件 #

将原始SQL拆分为两个独立查询并通过UNION ALL合并，避免优化器错误改写：

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

方案2：使用Hint强制优化器采用FILTER计划 #

通过Hint禁用哈希连接，强制优化器使用12c风格的FILTER逻辑：

SELECT /*+ NO_HASH_JOIN */ *
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);

或直接指定使用FILTER：

SELECT /*+ USE_FILTER(b) */ *
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);

方案3：调整优化器参数兼容旧版本 #

临时设置优化器特性兼容12c版本，验证是否解决问题：

ALTER SESSION SET optimizer_features_enable = '12.1.0.2';

若有效，可针对性设置该参数，或排查Oracle官方是否有对应补丁修复此优化器bug。

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