Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

peformance:nvl( my_column, 'N') <> 'Y' VS nvl( my_column, 'N') = 'N'

Peddi Kristipati-OracleMar 14 2014 — edited Mar 15 2014

Which one gives better performance? Could please explain.

1)nvl( my_column, 'N') <> 'Y'

2) nvl( my_column, 'N') = 'N'

--

Thanks

Peddi

This post has been answered by Frank Kulash on Mar 14 2014
Jump to Answer

Comments

Solomon Yakobson

Neither. You have expression against my_column in both cases. Therefore index (assuming my_column is indexed) can't be used. However, if you create FBI on nvl( my_column, 'N'), then nvl( my_column, 'N') = 'N' will perform better assuming  my_column can have values other that N, Y or NULL. Otherwise that performance difference will be very close to 0.

SY.

Frank Kulash

Hi,

Peddi(user576892) wrote:

Which one gives better performance? Could please explain.

1)nvl( my_column, 'N') <> 'Y'

2) nvl( my_column, 'N') = 'N'

--

Thanks

Peddi

Performance will be the same.

The results will be different if my_column is anything except 'Y', 'N' or NULL.

Greg Spall

Well, no reason to believe it would be any different, so let's do a quick test case

(and yes, results support what Solomon and Frank already pointed out ... ie no difference)

Setup a table to test - with some fake data.

create table junk (

   id  number,

   dd  date,

   cflag  char(1)

   )

/


insert into junk

select level id,

       sysdate - floor(dbms_random.value(1,1000)) dd,

       case mod(floor(dbms_random.value(1,100)),2) when 0 then 'Y' else 'N' end cflag

   from dual

  connect by level <= 1000000

/


commit;

start trace ..

Query 1

set autotrace traceonly

select * from junk

where nvl(cflag,'N') <> 'Y'

/

Results: Query 1

505298 rows selected.

Elapsed: 00:00:49.77

Execution Plan

----------------------------------------------------------

Plan hash value: 3017102790

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   563K|    13M|   768   (3)| 00:00:10 |

|*  1 |  TABLE ACCESS FULL| JUNK |   563K|    13M|   768   (3)| 00:00:10 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(NVL("CFLAG",'N')<>'Y')

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         29  recursive calls

          1  db block gets

      36492  consistent gets

          0  physical reads

        176  redo size

   10895874  bytes sent via SQL*Net to client

     236165  bytes received via SQL*Net from client

      33688  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     505298  rows processed

Query 2

select * from junk

where nvl(cflag,'N') = 'N'

/

Results: Query 1

505298 rows selected.

Elapsed: 00:00:49.76

Execution Plan

----------------------------------------------------------

Plan hash value: 3017102790

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   563K|    13M|   768   (3)| 00:00:10 |

|*  1 |  TABLE ACCESS FULL| JUNK |   563K|    13M|   768   (3)| 00:00:10 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(NVL("CFLAG",'N')='N')

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

      36447  consistent gets

          0  physical reads

          0  redo size

   10895838  bytes sent via SQL*Net to client

     236165  bytes received via SQL*Net from client

      33688  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     505298  rows processed

Now at this point, the 2nd query looks a bit better (ie redo size), however, that's often because Oracle still has things cached.

So we can either clear the cache, or just re-run the first and see how it compares.

(I'm feeling lazy, so I'll re-run the first query again)

Query 1

select * from junk

where nvl(cflag,'N') <> 'Y'

/

Results: Query 1 (take 2)

505298 rows selected.

Elapsed: 00:00:49.78

Execution Plan

----------------------------------------------------------

Plan hash value: 3017102790

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   563K|    13M|   768   (3)| 00:00:10 |

|*  1 |  TABLE ACCESS FULL| JUNK |   563K|    13M|   768   (3)| 00:00:10 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(NVL("CFLAG",'N')<>'Y')

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      36370  consistent gets

          0  physical reads

          0  redo size

   10896095  bytes sent via SQL*Net to client

     236165  bytes received via SQL*Net from client

      33688  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     505298  rows processed

And suddenly they look very very similar.

As already mentioned, I can't see any significant difference in performance with that.

Unless you have some other additional criteria or conditions, or something ...

Thanks for your responses.

Possible Values of my_columns are only 3 ( i ) 'Y'  ( ii )  'N' ( iii ) NULL

1) nvl( my_column, 'N') <> 'Y'

2) nvl( my_column, 'N') = 'N'

Both gives same performance?

If not, could you please explain the reason for the better performance option.

In other words, does the = operator give better performance than <> operator with strings?

--

Thanks

Peddi

Frank Kulash
Answer

Hi, Peddi,

The comparison will be equally efficient, whether you use = or <>.

Marked as Answer by Peddi Kristipati-Oracle · Sep 27 2020

Many thanks for the practical results.

Jonathan Lewis

The answer depends on how much of the data is null, N or Y.

If you have a large data set and a tiny amount of data at N or NULL then you can create a function-based index on nvl(mycolumn,'N'), create a histogram on the indexed column, and the predicate nvl(mycolumn,'N')  = 'N' will be able to use the index.

Better yet in those circumstances to use a CASE expression to convert everything else to NULL, (and change the predicate accordingly), then you have an index only on the rows you want and don't need a histogram.

Regards

Jonathan Lewis

Hoek

How about creating the index as follows: create index t_idx on t (my_column, 'N'); ?

Then would we still need an NVL in the query and/or the function based index?

(I'm without a database to test this currently, hence my question.)

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 12 2014
Added on Mar 14 2014
8 comments
2,653 views