Home » SQL & PL/SQL » SQL & PL/SQL » nvl and is null (oracle 11g)
nvl and is null [message #675080] Sat, 09 March 2019 07:25 Go to next message
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
Junior Member
Hi,

today we had a discussion regarding use of nvl .One of my team mates told that its good to use IS NULL in place of nvl.
Let me explain the same here .Suppose i have a table where one column is having null data also .

i have written the query like

select *
from table
where nvl(col,'X') = 'X' ;

He told me to write

select *
from table
where col IS NULL ;

Can someone please elaborate which one is faster or suggestible .
Re: nvl and is null [message #675081 is a reply to message #675080] Sat, 09 March 2019 07:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
harishankar_kar wrote on Sat, 09 March 2019 05:25
Hi,

today we had a discussion regarding use of nvl .One of my team mates told that its good to use IS NULL in place of nvl.
Let me explain the same here .Suppose i have a table where one column is having null data also .

i have written the query like

select *
from table
where nvl(col,'X') = 'X' ;

He told me to write

select *
from table
where col IS NULL ;

Can someone please elaborate which one is faster or suggestible .
What prevents you from running your own time tests to observe for your self the results?
If PERSON_A here post that NVL is faster while PERSON_B claims IS NULL is faster how do you decide which is correct?
Re: nvl and is null [message #675082 is a reply to message #675081] Sat, 09 March 2019 07:38 Go to previous messageGo to next message
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
Junior Member
Hi ,

That's correct .Looks logical .But if someone answers wrt performance showing some stats then it will be really helpful .
Re: nvl and is null [message #675083 is a reply to message #675081] Sat, 09 March 2019 07:42 Go to previous messageGo to next message
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
Junior Member
Hi ,

Its correct .
But if someone proves wrt some stats or some evidence then it will be really great .I think you will be guiding me in correct direction as most of the queries have been answered correct .
Re: nvl and is null [message #675084 is a reply to message #675082] Sat, 09 March 2019 07:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ankit_chikun wrote on Sat, 09 March 2019 05:38
Hi ,

That's correct .Looks logical .But if someone answers wrt performance showing some stats then it will be really helpful .

Sock puppet posting from same IP#

https://en.wikipedia.org/wiki/Sockpuppet_(Internet)
Re: nvl and is null [message #675085 is a reply to message #675084] Sat, 09 March 2019 07:52 Go to previous messageGo to next message
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
Junior Member
My apology .Both are same user .
Re: nvl and is null [message #675086 is a reply to message #675083] Sat, 09 March 2019 07:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
harishankar_kar wrote on Sat, 09 March 2019 05:42
Hi ,

Its correct .
But if someone proves wrt some stats or some evidence then it will be really great .I think you will be guiding me in correct direction as most of the queries have been answered correct .

post EXPLAIN PLAN for both cases.
Re: nvl and is null [message #675088 is a reply to message #675080] Sat, 09 March 2019 07:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you considering whether the column should be indexed? How?
Re: nvl and is null [message #675089 is a reply to message #675088] Sat, 09 March 2019 08:03 Go to previous messageGo to next message
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
Junior Member
No.
I just want to understand which should be used in coding for better performance .

Re: nvl and is null [message #675090 is a reply to message #675085] Sat, 09 March 2019 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
harishankar_kar wrote on Sat, 09 March 2019 14:52
My apology .Both are same user .
Why did you then create a NEW specific user to answer your question as for your previous topic you created a specific account to post a question then you continue with this one?

You are border line, my boy, ban is close if you continue to abuse of multiple accounts (that I will merge).

By the way, the 2 queries are not equivalent and won't give the same result.

Re: nvl and is null [message #675091 is a reply to message #675089] Sat, 09 March 2019 08:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So time running a loop that does the select a million times. And think about whether he column ever contains 'X'.
Re: nvl and is null [message #675092 is a reply to message #675080] Sat, 09 March 2019 08:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
1. Construct nvl(col,'X') = 'X' to select rows where col is null can only be used when we are guaranteed column col value can't be 'X'. And even if it is true now it can change tomorrow which makes code not transparent and forces you to track all places where such construct is used. Construct col is null is transparent.
2. Construct nvl(col,'X') = 'X' will not use index (unless you create function based index on nvl(col,'X')). Construct col is null will use index if there is multi-column index with col as a leading column and at least one other column is not null:

SQL> create table tbl(
  2                   id number primary key,
  3                   name varchar2(10),
  4                   address varchar2(300),
  5                   phone varchar2(50)
  6                  )
  7  /

Table created.

SQL> create index tbl_idx1 on tbl(name,id)
  2  /

Index created.

SQL> explain plan for
  2  select * from tbl where nvl(name,'X') = 'X'
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 2144214008

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   199 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TBL  |     1 |   199 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

   1 - filter(NVL("NAME",'X')='X')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

SQL> explain plan for
  2  select * from tbl where name is null
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 3798903459

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   199 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL      |     1 |   199 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TBL_IDX1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("NAME" IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

SQL> 

SY.
Re: nvl and is null [message #675093 is a reply to message #675080] Sat, 09 March 2019 08:53 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
harishankar_kar wrote on Sat, 09 March 2019 07:25
Hi,

today we had a discussion regarding use of nvl .One of my team mates told that its good to use IS NULL in place of nvl.
Let me explain the same here .Suppose i have a table where one column is having null data also .

i have written the query like

select *
from table
where nvl(col,'X') = 'X' ;

He told me to write

select *
from table
where col IS NULL ;

Can someone please elaborate which one is faster or suggestible .
To me it's not a question of which one is faster. You would be very hard pressed to find a meaningful difference in performance.

To me it's a matter of usage. The intent of nvl() is to return a different, specified, value of the column IS NULL. So if I'm running a report and I have null columns, I might say 'nvl(mycol,'NULL')' so that in the report, if a column IS NULL, it would return the literal string 'NULL' instead of leaving the column blank.

If I'm doing a comparison in a WHERE clause, I would use 'where mycol IS NULL' or 'where mycol IS NOT NULL'


Re: nvl and is null [message #675114 is a reply to message #675093] Sat, 09 March 2019 11:39 Go to previous message
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
Junior Member
Thanks for the reply Stevens .Its clear now .

Previous Topic: Binary Integer Division
Next Topic: Need suggestion on code refactoring
Goto Forum:
  


Current Time: Thu Mar 28 20:05:27 CDT 2024