Oracle SQL Performance issue with 'IS NULL' - NVL

Question

I am using Oracle 12.2 version. I have SQL that has two filter condition to fetch NULL values from huge table. i have converted that IS NULL to NVL (OFF_CODE, -1)=-1 and nvl(off_date,'01-JAN-1900')= '01-JAN-1900'.

I have created functional based composite index on the two columns, then I taken explain plan that I pasted here. COST is reduced from 8 million to 122 now.

I was very happy after seeing this plan. but the SQL keep running for minutes. Before the change it was responding within 15 minutes. what could be the issue even after the cost is reduced the response is not quick? and how can I replace the IS NULL for better performance?

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

my query

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

My index:

create index INDEX_TABLE_NAME1 on TABLE_NAME1( nvl( off_code_id,-1),nvl( off_date,'01-JAN-1900'));

Answer 1

You can promote the use of an index if the percentage of nulls is lower than 5% on the table column(s). If that's the case you can use the second table as the driving table and write the query as:

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

This query would benefit from the following indexes:

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

As a side note, I fail to understand what the query is using table #1 for, since all values come from table #2. Just wondering...

Answer 2

I'm under the impression you are only interested in the records where these two fields are NULL. Do you have other queries that filter on these columns looking for actual values? If not, why not create a filtered indexed?

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

And then change your SQL back to

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;

Notes:

• this also avoids accidently including records that have the actual values -1 and 1 jan 1900 in those fields
• try to use the JOIN .. ON construction rather than what you have now, I doubt it will make any difference in the execution plan but it's just better readable and hence less error prone IMHO.