True or False – NVL

11 05 2010

May 11, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of the NVL function, inspired by a recent thread that I found in a discussion forum.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1.  The most common use of the NVL function is to prevent division by zero errors in mathematical equations.

2.  The SQL statement:

SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1;

   replaces NULL values found in the CURRENT_STATUS column of table T1 with the phrase: Not disclosed

3.  The NVL function replaces a NULL column value with any other value.

4.  The NVL function is the most efficient method for converting NULL values to a non-NULL value.

To help you with the questions, here is a test table:

CREATE TABLE T1(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 DATE,
  C5 DATE,
  C6 DATE,
  C7 VARCHAR2(20),
  C8 VARCHAR2(20),
  C9 VARCHAR2(20),
  PRIMARY KEY(C1));

INSERT /*+ APPEND */ INTO
  T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A')),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

The above creates a table with 1,000,000 rows, where one of every 5 rows contains NULL values in columns C2, C3, C5, C6, C8, and C9.

For some of the quiz questions, the following script might be helpful:

SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS
SET TIMING ON

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'NVL_TEST';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT /*+ GATHER_PLAN_STATISTICS */
  COALESCE(C3,C2,C1) NUM,
  COALESCE(C6,C5,C4) DAT,
  COALESCE(C9,C8,C7) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,NVL(C2,C1)) NUM,
  NVL(C6,NVL(C5,C4)) DAT,
  NVL(C9,NVL(C8,C7)) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,C1) NUM,
  NVL(C6,C4) DAT,
  NVL(C9,C7) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  DECODE(C3,NULL,DECODE(C2,NULL,C1,C2),C3) NUM,
  DECODE(C6,NULL,DECODE(C5,NULL,C4,C5),C6) DAT,
  DECODE(C9,NULL,DECODE(C8,NULL,C7,C8),C9) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,NVL(C2,C4)) COL1,
  NVL(C6,NVL(C5,C7)) COL2
FROM
  T1;

SELECT
  SYSDATE
FROM
  DUAL;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

It is probably best to execute the above script several times to limit the effects of physical block reads.  You may see different performance results whether or not the 10046 trace is enabled, whether or not physical block reads are performed on every execution, and whether or not the test is executed directly on the database server.  The GATHER_PLAN_STATISTICS hint is used to permit DBMS_XPLAN.DISPLAY_CURSOR to retrieve the execution plan with the ALLSTATS LAST format parameter.


Actions

Information

9 responses

11 05 2010
Charles Hooper

I will answer the easy question.
#2 False. Why?

CREATE TABLE T1 (CURRENT_STATUS NUMBER);
  
INSERT INTO T1 VALUES(1);

The above SQL created a table with a single row. Now the test case:

SQL> SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1;
SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1
                            *
ERROR at line 1:
ORA-00904: "Not disclosed": invalid identifier

Well, that did not work. How about this:

SELECT NVL(CURRENT_STATUS, 'Not disclosed') FROM T1;
  
SQL> SELECT NVL(CURRENT_STATUS, 'Not disclosed') FROM T1;
SELECT NVL(CURRENT_STATUS, 'Not disclosed') FROM T1
                           *
ERROR at line 1:
ORA-01722: invalid number

That did not work either. How could such a simple concept go wrong? Is it too much to ask that people test that something works before posting it on the Internet, or at least to ask someone to double-check that it works as described?

17 05 2010
Andy

This kind of error seems to be a recurring theme on *that* website …. :)

17 05 2010
Charles Hooper

At its core, for that website (and most commercial websites), it is a business decision that determines what is posted, what is error checked, what is periodically reviewed, what is corrected, and how frequently the pages are cross-linked from domains owned by the same company.

The cross-linking drives the pages further up the Google search results, which means that someone trying to learn how something works will *probably* view the web pages that were cross-linked, and will possibly leave the person to quickly learn that Oracle Database is probably an impossibly difficult to learn platform. Oracle Database is not an impossibly difficult to learn platform.

At its core, in case the message is not clear, this blog website has a simple message: “Stop, think, understand.” Not everything examined needs to be true for that to happen, but it is important to determine methods to detect when something cannot possibly be true, and verify that what is true will also not only be true in the specific release of Oracle that is in use, but also in past and future releases.

*Enough said on the topic, I think*

12 05 2010
oraculix

I’ll answer the (imho) even easier question #1: FALSE.
Why? NVL doesn’t replace zeroes, it replaces NULLs. And a division by NULL doesn’t throw an exception, it simply resolves to NULL.

Ah, by the way: #3 can be falsified with your answer to #2. ;-)

12 05 2010
Charles Hooper

I think that I was typing up my answer for question #3 while you were posting the answer for question #1.

Nice answer for question #1.

If I remember correctly, I think that Crystal Reports will *optionally* replace NULL numeric values with a value of 0, and if you bring the query result into a spreadsheet package, such as Excel, the NULL value will be treated as a 0. But, those special cases do not make the question true. As you stated, it definitely is not true for SQL.

12 05 2010
Charles Hooper

Time to answer another one of the easy questions.
#3 “The NVL function replaces a NULL column value with any other value.” False. Why? In my previous comment, after I replaced the double quotes ( ” ) with single quotes ( ‘ ), the query still failed. In that example the CURRENT_STATUS column was defined as a NUMBER and the SQL statement attempted to replace the NULL value in that column with a value that was a VARCHAR (string) value. The replacement value specified in the NVL function must be of the same datatype.

14 05 2010
Martin Preiss

Charles,
#4 false; coalesce (and case and decode) can use a short circuit evalution (and NVL and NVL2 are not setup for this) – http://triangle-circle-square.blogspot.com/2010/02/short-circuit-evaluations-moving-away.html

(I guess your links would provide the same information)

Regards
MP

14 05 2010
Charles Hooper

Martin,

That is a great example that you linked to in your response. I never considered using DBMS_LOCK.SLEEP to demonstrate the potential difference that the short-circuit evaluation could make in evaluation time. At first glance it might seem silly to use DBMS_LOCK.SLEEP in the PL/SQL function, but it could be the case in a production application that each of the PL/SQL functions in the NVL() and COALESCE() functions performs a large number of calculations and send several SQL statements to the database.

oraculix and Martin, thanks for contributing to this blog article.

28 02 2012
NVL-st Du noch oder COALESCE-t Du schon? « Oraculix

[...] “True or False – NVL”, ein paar schöne Beispiele zu Mißverständnissen bzgl. NVL() Bewerten: Diesen Artikel:DruckenFacebookTwitterMehrE-MailDiggRedditStumbleUponLinkedIn [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: