“IS NULL”的 Oracle SQL 性能问题 - NVL

Oracle SQL Performance issue with 'IS NULL' - NVL

提问人：user1402648 提问时间：6/24/2023 最后编辑：SelVaziuser1402648 更新时间：6/26/2023 访问量：141

问：

我使用的是 Oracle 12.2 版本。我有 SQL 有两个过滤条件来从大表中获取 NULL 值。我已将其转换为 和 .IS NULLNVL (OFF_CODE, -1)=-1nvl(off_date,'01-JAN-1900')= '01-JAN-1900'

我在两列上创建了基于功能的复合索引，然后我采用了粘贴到此处的解释计划。COST 从 800 万降低到现在的 122。

看到这个计划后，我非常高兴。但 SQL 会持续运行几分钟。在更改之前，它在 15 分钟内做出响应。即使成本降低，响应速度也不快，可能是什么问题？如何更换 IS NULL 以获得更好的性能？

-------------------------------------------------------------------------------------------------------------------------
| 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;<br/>

我的索引：

create index INDEX_TABLE_NAME1 on TABLE_NAME1( nvl( off_code_id,-1),nvl( off_date,'01-JAN-1900'));
oracle null 查询优化 sql 执行计划

评论

0赞 nbk 6/24/2023
你首先不应该有 NULL 值，为什么不运行更新并填满所有行，你只能检查，但如果它是一个大表，这仍然需要时间。您是否检查过索引的基数？
0赞 ibre5041 6/24/2023
你的选择是什么？
0赞 user1402648 6/24/2023
@nbk这些表用于OLTP应用，每天都会添加NULL值。即使每天在 Huge tale 上运行更新也会花费数据库。索引的基数为 4 。
1赞 nbk 6/24/2023
您应该使用连接而不是逗号，但是当您在 item_id 和其余部分上有一个组合索引时会发生什么
0赞 user1402648 6/24/2023
@nbk我会考虑加入，但目前正在分析尽管成本降低但响应缓慢的原因。我看到成本降低，但在创建索引后没有快速响应。

答：

1赞 The Impaler 6/24/2023 #1

如果表列上的空值百分比低于 5%，则可以促进索引的使用。如果是这种情况，则可以使用第二个表作为驱动表，并将查询编写为：

select
  u.item_id,
  sum(coalesce(u.depr_cost, 0) + coalesce(u.act_dprctn_cost, 0)) as depr_cost 
from table_name1 t
join table_name2 u on u.item_id = t.item_id
where u.off_code is null
  and u.off_date is null
group by u.item_id

此查询将受益于以下索引：

create index ix1 on table_name2 (item_id, off_code, off_date);  
create index ix2 on table_name1 (item_id); -- probably already exists

顺便说一句，我无法理解查询使用表 #1 的用途，因为所有值都来自表 #2。只是想知道......

1赞 deroby 6/26/2023 #2

我的印象是，您只对这两个字段为 NULL 的记录感兴趣。您是否有其他查询来筛选这些列以查找实际值？如果没有，为什么不创建一个筛选索引呢？

CREATE INDEX INDEX_TABLE_NAME1_NULL_NULL on TABLE_NAME1( i.item_id) WHERE (off_code_id IS NULL AND off_date IS NULL);

然后将 SQL 改回

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 i.off_code_id IS NULL
  AND i.off_date IS NULL
GROUP BY i.item_id;

笔记：

  • 这也避免了意外地包括具有实际值和这些字段中的记录-11 jan 1900
  • 尝试使用结构而不是您现在拥有的结构，我怀疑它会对执行计划产生任何影响，但它只是可读性更好，因此恕我直言，不易出错。JOIN .. ON

上一个：Oracle：创建免除完全空行的表副本

下一个：Oracle：与 NULL 连接