Written by

Question Tom Scaletti · May 11

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 (3)0

Add reply

Comments

David Hockenbroch · May 11

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

0 0

Stephen Canzano · May 11

If you can provide the query plan it would help greatly. The query plan should help identify what is going on..

1 0

Vishal Pallerla · May 11

@David Hockenbroch Yes, that seems to be the right approach! Just a small correction to your condition:

AND ((TOURFIND.TNR = TNRHead.TNR) OR (TOURFIND.TNR IS NULL AND SGNRFIND.TNR = TNRHead.TNR))

(You had `TNRHead.TNR = TNRHead.TNR` twice at the end)

performance could be significantly better than NVL as NVL forces a full table scan and OR condition allows index usage so the optimizer can use indexes on both `TOURFIND.TNR` and `SGNRFIND.TNR`

0 0