SQL NVL alternative in WHERE clause
I need to analyse and improve the performance on some 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.
Discussion (2)0
Comments
I'm not sure it'll be much better performance-wise, but you could make it something like:
AND ((TOURINFO.TNR = TNRHead.TNR) OR (TOURFIND.TNR IS NULL AND SGNRFIND.TNR = TNRHead.TNR = TNRHead.TNR))
If you can provide the query plan it would help greatly. The query plan should help identify what is going on..