OraFAQ Forum: SQL & PL/SQL » NVL Vs OR condition

Home » SQL & PL/SQL » SQL & PL/SQL » NVL Vs OR condition (PL/SQL Release 11.2.0.4.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NVL Vs OR condition [message #668259]

Thu, 15 February 2018 01:12

saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All,

If we want to consider the null values also which one is efficient to use ?


select  * from emp  where  nvl(COMM , 800) >=  800 ;
select  * from emp  where  nvl(COMM , P_value ) >=  P_value ;

or  
select  * from emp  where comm  >= 800 or comm is null ;

In both cases we are considering the null values with parameter condition.

Thanks
SaiPradyumn

Report message to a moderator

Re: NVL Vs OR condition [message #668260 is a reply to message #668259]

Thu, 15 February 2018 01:20

John Watson
Messages: 8270
Registered: January 2010
Location: Global Village

Senior Member

You will have to test. Enable timing and autot trace would be the easiest way. The design of any index on COMM is of course critical.
You also need to be more imaginative. How about writing a UNION query? Or using CASE?

Report message to a moderator

Re: NVL Vs OR condition [message #668261 is a reply to message #668260]

Thu, 15 February 2018 01:44

saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad

Senior Member

ok Thanks .Will try work around with your suggestion on our project related tables

Report message to a moderator

Re: NVL Vs OR condition [message #668262 is a reply to message #668259]

Thu, 15 February 2018 02:42

Michel Cadot
Messages: 67146
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Best is LNNVL.

Report message to a moderator

Re: NVL Vs OR condition [message #668264 is a reply to message #668262]

Thu, 15 February 2018 11:14

John Watson
Messages: 8270
Registered: January 2010
Location: Global Village

Senior Member

or coalesce perhaps?

Report message to a moderator

Re: NVL Vs OR condition [message #668344 is a reply to message #668264]

Mon, 19 February 2018 03:34

cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester

Senior Member

Given that the 2nd argument is a constant coalesce shouldn't be any noticeably faster than nvl.
You get a difference when the 2nd argument is a calculation or query and oracle can skip it.

Report message to a moderator

Re: NVL Vs OR condition [message #668345 is a reply to message #668344]

Mon, 19 February 2018 03:38

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

iirc, nvl always evaluates the full expression even if the passed value is not null, therefore depending on what P_value is, it might cause a lot of needless overhead.

I mean in this case it's probably a static value, but just in case people land here from google Smile