SQL NVL alternative in WHERE clause

Question

I do have that silly job at work,in which I need to analyse and improve the performance on some stupid 20 years old SQL statements.

One statement uses NVL in the WHERE clause (no wonder why it's slow) but I still have to improve the performance and the data really needs to be the same when returned.

SELECT *
FROM TOURHead, TOURFIND, SGNRFIND, TNRHead
WHERE TOURHead.cl = '123'
AND TOURHead.cl = TOURFIND.cl
AND TOURHead.TOURNR = TOURFIND.TOURNR
AND TOURFIND.cl =* SGNRFIND.cl
AND TOURFIND.SGNR    =* SGNRFIND.SGNR
AND TOURFIND.cl = TNRHead.cl
AND NVL(TOURFIND.TNR, SGNRFIND.TNR) = TNRHead.TNR;

Really hope someone can help me with this one.

I know that the =* really shouldn't be in use and that JOINS are the proper way to go. Anyways, I have no idea what I can do to get rid of the NVL.

Answer 1

You can use the isnull as alternative of NVL.

AND ISNULL(TOURFIND.TNR, SGNRFIND.TNR) = TNRHead.TNR;

and kindly check proper index is available for both this column.