True or False – NVL

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:

• http://awads.net/wp/2006/01/27/nvl-nvl2-or-coalesce/
• http://www.oracledba.co.uk/tips/plsql_nvl_costs.htm
• dba-oracle.com/t_oracle_nvl.htm
• http://forums.oracle.com/forums/thread.jspa?threadID=607018

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.