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

3yhwsihp 于 2023-06-29 发布在 Oracle

关注(0)|答案(2)|浏览(175)

我使用的是Oracle 12.2版本。我有一个SQL，它有两个过滤条件来从一个巨大的表中获取NULL值。我已经将IS NULL转换为NVL (OFF_CODE, -1)=-1和nvl(off_date,'01-JAN-1900')= '01-JAN-1900'。
我已经在两列上创建了基于函数的复合索引，然后我采取了解释计划，我粘贴在这里。成本从800万降到现在的122万。
看到这个计划后，我非常高兴。但SQL会持续运行几分钟在更改之前，它在15分钟内响应。成本降低后React不快是什么问题？如何替换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

来源：https://stackoverflow.com/questions/76542487/oracle-sql-performance-issue-with-is-null-nvl

2条答案

按热度按时间

9avjhtql1#

如果表列上的空值百分比低于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。只是好奇。。

赞(0）回复(0）举报 2023-06-29

tvmytwxo2#

我的印象是，你只对这两个字段为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;

注意事项：

这还可以避免在这些字段中意外包含具有实际值-1和1 jan 1900的记录
如果你尝试使用JOIN .. ON结构而不是你现在所拥有的，我怀疑它会在执行计划中产生任何差异，但它只是可读性更好，因此更不容易出错。

赞(0）回复(0）举报 2023-06-29

我来回答

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

2条答案

相关问题

热门标签

最新问答