Can you rewrite the NVL function?

Question

I'm using an NVL function in my WHERE clause like this.

... AND NVL(FIRST_DATE, SECOND_DATE) BETWEEN (SYSDATE - 7) and SYSDATE

What I'm doing is this: If FIRST_DATE is null then use the SECOND_DATE for comparison. The problem with this is that it prevents index usage on the column first_date or second_date. So I rewrote the condition into this:

... AND 
((FIRST_DATE BETWEEN (SYSDATE-7)  AND SYSDATE) 
OR 
(FIRST_DATE IS NULL AND SECOND_DATE BETWEEN (SYSDATE-7) AND SYSDATE))

I tested it by running it with different dates and it always returned the same results as with the NVL Function. But I want to make sure and check if I didn't miss any exceptions where the result may differ. Can anyone confirm that what I did is semantically equivalent to the NVL Function?

It is semantically equivalent. But is it actually any faster? — Tony Andrews, Apr 28 '20 at 10:03
@BarbarosÖzhan If `FIRST_DATE` is null, the first condition automatically results into `FALSE`, which makes the `FIRST_DATE IS NOT NULL` redundant. — Victor O, Apr 28 '20 at 12:12
@TonyAndrews yes because it allows index usage without the need to create a function based index. — Victor O, Apr 28 '20 at 12:14

score 1 · Answer 1 · answered Apr 28 '20 at 10:36

The issue isn't really the NVL() per se. It is simply the complication of the WHERE clause. Oracle supports indexes on expressions, so I would recommend a new index:

create index idx_t_dates on t(coalesce(first_date, second_date))

Then, if you use the same expression in the where clause, then the index should be used:

and coalesce(first_date, second_date) between sysdate - 7 and sysdate

I prefer coalesce()over nvl() because it is the standard function with the same functionality.

I was also thinking about creating a function based index but I didn't want to go that route, since it's once of our biggest tables. — Victor O, Apr 28 '20 at 12:09

Can you rewrite the NVL function?

1 Answers1