Oracle 12.2中IS NULL替换为NVL后成本降低但性能未提升问题排查
问题背景
在Oracle 12.2版本中，查询大表TABLE_NAME1的NULL值时，将IS NULL替换为NVL(OFF_CODE_ID,-1)=-1和NVL(OFF_DATE,'01-JAN-1900')='01-JAN-1900'，并创建了基于函数的复合索引INDEX_TABLE_NAME1。执行计划显示成本从800万降至122，但SQL运行时长反而超过之前的15分钟。
性能未提升的原因排查
1. 基数估计严重失真
执行计划预估TABLE_NAME1仅返回4行，但实际场景中符合OFF_CODE_ID IS NULL AND OFF_DATE IS NULL的行数可能远大于这个数值。嵌套循环关联在小数据量场景下高效，但当TABLE_NAME1实际返回行数较多时，会触发大量的INDEX RANGE SCAN（INDEX_TABLE_NAME2）和TABLE ACCESS BY INDEX ROWID（TABLE_NAME2）操作，逐行关联的IO成本会急剧上升，导致运行时长暴增。而原IS NULL查询的全表扫描在大数据量返回时，反而可能因为批量处理更高效。
2. 函数索引未覆盖查询所需列
当前创建的函数索引仅包含NVL(OFF_CODE_ID,-1)和NVL(OFF_DATE,'01-JAN-1900')两个表达式，查询需要的ITEM_ID必须通过回表（TABLE ACCESS BY INDEX ROWID BATCHED）获取。如果符合条件的NULL行数量庞大，回表操作会产生大量随机IO，成为性能瓶颈。
3. 关联算法选择不合理
执行计划选择了嵌套循环关联，但如果TABLE_NAME2中匹配ITEM_ID的数据量较大，哈希连接（HASH JOIN）的效率会远高于嵌套循环。由于基数估计错误，Oracle优化器错误地选择了不适合当前数据量的关联方式。
更优的IS NULL替代方案
方案1：将函数索引改为覆盖索引
在现有函数索引中包含ITEM_ID，避免回表操作，直接从索引中获取所需数据：
CREATE INDEX INDEX_TABLE_NAME1 ON TABLE_NAME1(NVL(OFF_CODE_ID,-1), NVL(OFF_DATE,'01-JAN-1900')) INCLUDE (ITEM_ID);
修改后，索引扫描即可直接拿到ITEM_ID，消除回表的IO开销。
方案2：使用IS NULL+位图索引（适合低更新场景）
Oracle对位图索引的NULL值支持良好，适合只读或低并发更新的表：
-- 单一位图索引匹配双NULL条件 CREATE BITMAP INDEX INDEX_TABLE_NAME1 ON TABLE_NAME1(CASE WHEN OFF_CODE_ID IS NULL AND OFF_DATE IS NULL THEN 1 END); -- 或分开创建单列NULL位图索引，结合使用 CREATE BITMAP INDEX IDX_OFF_CODE_ID_NULL ON TABLE_NAME1(OFF_CODE_ID) WHERE OFF_CODE_ID IS NULL; CREATE BITMAP INDEX IDX_OFF_DATE_NULL ON TABLE_NAME1(OFF_DATE) WHERE OFF_DATE IS NULL;
查询语句还原为IS NULL写法：
SELECT i.item_id, SUM(COALESCE(igp.DEPR_cost, 0) + COALESCE(igp.act_dprctn_cost, 0)) depr_cost FROM TABLE_NAME1 i JOIN TABLE_NAME2 igp ON i.Item_id = igp.item_id WHERE i.off_code_id IS NULL AND i.off_date IS NULL GROUP BY i.item_id;
方案3：基于IS NULL的函数索引（无需NVL替换）
直接使用判断NULL的函数表达式创建索引，避免NVL带来的转换开销：
CREATE INDEX INDEX_TABLE_NAME1 ON TABLE_NAME1(DECODE(OFF_CODE_ID, NULL, 1, 0), DECODE(OFF_DATE, NULL, 1, 0)) INCLUDE (ITEM_ID);
查询条件对应调整为：
SELECT i.item_id, SUM(COALESCE(igp.DEPR_cost, 0) + COALESCE(igp.act_dprctn_cost, 0)) depr_cost FROM TABLE_NAME1 i JOIN TABLE_NAME2 igp ON i.Item_id = igp.item_id WHERE DECODE(i.off_code_id, NULL, 1, 0) = 1 AND DECODE(i.off_date, NULL, 1, 0) = 1 GROUP BY i.item_id;
原执行计划
------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 192 | 122 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 4 | 192 | 122 (1)| 00:00:01 | | 2 | NESTED LOOPS | | 146 | 7008 | 121 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 148 | 7008 | 121 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_NAME1 | 4 | 128 | 8 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | INDEX_TABLE_NAME1 | 4 | | 4 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | INDEX_TABLE_NAME2 | 37 | | 3 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | TABLE_NAME2 | 37 | 592 | 36 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access(NVL("OFF_CODE_ID",(-1))=(-1) AND NVL("OFF_DATE",'01-JAN-1900')='01-JAN-1900') 6 - access("I"."ITEM_ID"="IGP"."ITEM_ID")
原查询语句
SELECT /*+ index (i, INDEX_TABLE_NAME1) */ i.item_id, SUM(coalesce(igp. DEPR_cost, 0) + coalesce(igp.act_dprctn_cost, 0)) depr_cost FROM TABLE_NAME1 i, TABLE_NAME2 igp WHERE 1 = 1 AND i.Item_id = igp.item_id AND nvl(i.off_code_id,-1) = -1 AND nvl(i.off_date,'01-JAN-1900') = '01-JAN-1900' GROUP BY i.item_id;
原索引创建语句
create index INDEX_TABLE_NAME1 on TABLE_NAME1( nvl( off_code_id,-1),nvl( off_date,'01-JAN-1900'));
内容的提问来源于stack exchange，提问作者user1402648
