| NVL vs IS NULL [message #642997] |
Thu, 24 September 2015 11:54  |
 |
deepakdot
Messages: 89
Registered: July 2015
|
Member |
|
|
Hi
Which one gives better performance , NVL or IS NULL ?
Option1: Select * from T1 where NVL(Col1, 10) > 9;
Option2: Select * from T1 where Col1 > 9 OR Col1 IS NULL;
Which one better to use. When i check the explain plan i did not see much difference.
Thanks
Deepak Samal
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: NVL vs IS NULL [message #643013 is a reply to message #643002] |
Fri, 25 September 2015 06:38  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
deepakdot wrote on Thu, 24 September 2015 23:49
Here Both are return same result.
Then, as Gazzag said, you have answered your own question.
Quote:But i want to perfomracne / general practice which is better. While i see the explain plan for both the query in our database, i did not see any difference.
For performance, you must keep in mind that nearly every performance related question must be considered 'in situ' (I leave it as an exercise for the student to research the term 'in situ')
For 'best practice', I'd keep in mind writing code that is easily understood and maintainable by someone other than the original author. (That attitude seems to be increasingly vanishing.) Which version of the code requires explanation, and which is plainly obvious in its intent? (You have also already answered that question.)
And when considering the potential trade-off between simple, easily understood code and performance, you need to keep in mind the business impact of the less performant code. Let's say you have an OLTP transaction and the 'obvious' code completes in 0.75 seconds, while the 'clever' code completes in 0.375 seconds. Or consider a batch job that runs at off-hours. The job with the 'obvious' code completes in 5 minutes while the job with the 'clever' code completes in 2.5 minutes. In either case, the 'clever' code produced a 50% reduction in time, but does anyone really care? Is the OLTP user going to be more productive with a transaction that completes 0.375 seconds faster? Are any users going to see an improvement in their use of the system because last nights batch job completed 2.5 minutes earlier?
|
|
|
|
| Re: NVL vs IS NULL [message #643014 is a reply to message #643002] |
Fri, 25 September 2015 09:00 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
deepakdot wrote on Fri, 25 September 2015 00:49In our application we have a last_date columns which hold the value after which the row is no more valid.. For e.g if the last_date column values is 15-NOV-2015, then that row is not more valid after this date.. If it is a NULL (No value mention) then there is no last date and It is valid for ever.
Bad design. I'd suggest using DATE '9999-12-31' instead of null. Then all you need is:
Select * from Table1 Where LAST_DATE > :TodayDate
Now it could use index (assuming LAST_DATE is indexed).
SY.
|
|
|
|
| Re: NVL vs IS NULL [message #643015 is a reply to message #642997] |
Fri, 25 September 2015 09:39 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I just ran this procedure through dbms_profiler,create or replace procedure p1 as
n number;
begin
for i in 1..100000 loop
select 1 into n from dual where nvl(dummy,'X') > 'A';
select 1 into n from dual where dummy > 'A' or dummy is null;
end loop;
end;
/
the line with NVL took 1955455297 nanoseconds, the line with the OR took 1944632052 nanoseconds. So, over a hundred thousand executions, the difference is about one hundredth of a second. Is that statistically significant?
I am of course prepared to believe that my test is not valid. There could be any number of optimizations when addressing the DUAL object.
I'll attach the script, in case anyone is interested.
|
|
|
|
|
|
| Re: NVL vs IS NULL [message #643022 is a reply to message #643016] |
Fri, 25 September 2015 11:45 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Yes, so what prevents OP from collecting stats with histograms? This is no different from having table with highly skewed data (it just we skew data by using not null default and might not realize that). And Jonathan never says it is not good practice. He just says - solution is histograms. This way selectivity is calculated correctly and index is used.
SY.
|
|
|
|
| Re: NVL vs IS NULL [message #643025 is a reply to message #643022] |
Fri, 25 September 2015 12:28 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:This is no different from having table with highly skewed data
This is not true.
There you'll have quite compact data between say, 2000 and 2015, and nothing between 2015 and 9999 and a new pick for 31-12-9999.
Until 12c Oracle optimizer had not the histograms to handle this case and what he sees is about that there are many data each year between, say, 2000 and 2015 and very few between 2015 and 9999 (exactly the number in 9999 divided by (9999-2015), so if you ask for your pseudo-null value it will use the index as it thinks there are about 8000 times less rows than there actually are.
Quote: And Jonathan never says it is not good practice. He just says - solution is histograms.
This is not correct, in the second section I mentioned he demonstrated why it is bad even with histograms (up to new 12c ones where new tests have to be made to see now the optimizer behaves).
As OP is in 12c he can take profit of new histograms but these ones have been created for other purpose than setting bad defaults.
If there is no value, the value is not known, or any value is meaningless for a specific row, then the "value" should be NULL.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
