Home » SQL & PL/SQL » SQL & PL/SQL » NVL Vs OR condition (PL/SQL Release 11.2.0.4.0 - Production)
NVL Vs OR condition [message #668259] Thu, 15 February 2018 01:12 Go to next message
saipradyumn
Messages: 365
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


Re: NVL Vs OR condition [message #668260 is a reply to message #668259] Thu, 15 February 2018 01:20 Go to previous messageGo to next message
John Watson
Messages: 7671
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?

Re: NVL Vs OR condition [message #668261 is a reply to message #668260] Thu, 15 February 2018 01:44 Go to previous messageGo to next message
saipradyumn
Messages: 365
Registered: October 2011
Location: Hyderabad
Senior Member
ok Thanks .Will try work around with your suggestion on our project related tables
Re: NVL Vs OR condition [message #668262 is a reply to message #668259] Thu, 15 February 2018 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65980
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Best is LNNVL.

Re: NVL Vs OR condition [message #668264 is a reply to message #668262] Thu, 15 February 2018 11:14 Go to previous messageGo to next message
John Watson
Messages: 7671
Registered: January 2010
Location: Global Village
Senior Member
or coalesce perhaps?
Re: NVL Vs OR condition [message #668344 is a reply to message #668264] Mon, 19 February 2018 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 13340
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.
Re: NVL Vs OR condition [message #668345 is a reply to message #668344] Mon, 19 February 2018 03:38 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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
Re: NVL Vs OR condition [message #668346 is a reply to message #668345] Mon, 19 February 2018 03:40 Go to previous messageGo to next message
cookiemonster
Messages: 13340
Registered: September 2008
Location: Rainy Manchester
Senior Member
p_value has to be a static value unless you're using dynamic SQL.
Re: NVL Vs OR condition [message #668347 is a reply to message #668346] Mon, 19 February 2018 03:48 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
I never rule out people pasting examples which are not the real code Smile

Especially in a "general terms" question like this one.

[Updated on: Mon, 19 February 2018 03:48]

Report message to a moderator

Re: NVL Vs OR condition [message #668359 is a reply to message #668347] Mon, 19 February 2018 07:00 Go to previous message
cookiemonster
Messages: 13340
Registered: September 2008
Location: Rainy Manchester
Senior Member
true.
Previous Topic: Finding exact row in case of matching
Next Topic: Grouping of records in a table with a group id
Goto Forum:
  


Current Time: Mon Nov 19 14:37:35 CST 2018