nvl and is null [message #675080] |
Sat, 09 March 2019 07:25 |
|
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 #675092 is a reply to message #675080] |
Sat, 09 March 2019 08:17 |
Solomon Yakobson
Messages: 3275 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 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
harishankar_kar wrote on Sat, 09 March 2019 07:25Hi,
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'
|
|
|
|