True or False – NVL

11 05 2010

May 11, 2010

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.