By | October 18, 2010

I was looking at an execution plan the other day and saw in the predicate section that my query had been rewritten to use the function LNNVL. This function was new to me, so a quick look in the documentation provides some more information. Basically, it will convert TRUE expression to FALSE and both FALSE and UNKNOWN expressions to TRUE.

For a quick example:

select *
from t
where t1 <> t2
or t1 is null
or t2 is null;

Can be rewritten as:

select *
from t
where lnnvl(t1 = t2);

I don’t know how much use it will get, as it isn’t the most intuitive function ever, but it is definitely worth knowing it is out there.

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *