1 + NULL is NULL

If you try to add a null to a number, the result is a null. You can see what I mean in the example below, which I tested in Oracle 10:

SQL> select 1 from dual
  2  /

         1
----------
         1

SQL> select nvl(null,'null') from dual
  2  /

NVL(NULL,'NULL')
----------------
null

SQL> select nvl(to_char(1+null),'null') from dual
  2  /

NVL(TO_CHAR(1+NULL),'NULL')
---------------------------
null

SQL>

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list

  2  as select * from dba_objects

  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------

NULL

                                                  

SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move

  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

DBMS_STATS.SET_PARAM

The example below worked as shown in Oracle 10. First create a test table with an index:

 

SQL> create table andrews_table (col1 number)

  2  /

 

Table created.

 

SQL> create index andrews_index

  2  on andrews_table(col1)

  3  /

 

Index created.

SQL>

Next run dbms_stats.set_param and set cascade to false:

 

SQL> exec dbms_stats.set_param('cascade','false');

 

PL/SQL procedure successfully completed.

SQL>

Then, if you analyze the table using gather_table_stats:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'andrews_table');

 

PL/SQL procedure successfully completed.

SQL>

The table is analyzed:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

SQL>

... but the index isn't:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

NULL

SQL>

However, if you run dbms_stats.set_param and set cascade to true:

SQL> exec dbms_stats.set_param('cascade','true');

 

PL/SQL procedure successfully completed.

SQL>

When you analyze the table, the index is analyzed too:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'andrews_table');

 

PL/SQL procedure successfully completed.

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL>

 

In Oracle 11, dbms_stats.set_param still runs but it has no effect. You can run

dbms_stats.set_param('cascade','false'); but the index might still be analyzed!

SQL*Plus SET NULL Statement

I have used the NVL function in several other posts but here is another example. First a table is created. It has never been analyzed so its last_analyzed date is null. The NVL function spots this and replaces the null by the text, Not yet:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> select nvl(to_char(last_analyzed),'Not yet')
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

NVL(TO_CH
---------
Not yet

SQL>

If you don't like the NVL function, you can use the SQL*Plus SET NULL statement instead. Here is one way to use it:

SQL> set null 'Null'
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Null

SQL>

And here is another:

SQL> col last_analyzed null Never
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Never

SQL>

An Empty String is Null in Oracle

(I ran this SQL in an Oracle 11 database.) Oracle treats an empty string as null. The length of an empty string is also null, not zero:

 

SQL> select nvl('','NULL1') null1 from dual;

 

NULL1

-----

NULL1

 

SQL> select nvl(to_char(length('')),'NULL2')

  2  null2 from dual;

 

NULL2

-----

NULL2

 

SQL>

This surprised me a little so I checked it a different way but got the same answer:

 

SQL> select 'Empty string is null'

  2  from dual

  3  where '' is null;

 

'EMPTYSTRINGISNULL'

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

Empty string is null

 

SQL> select 'Empty string is not null'

  2  from dual

  3  where '' is not null;

 

no rows selected

 

SQL>

Counting NULL Values

You should not use = NULL to check if something is NULL. You should use IS NULL instead. First find a table or view which has a column containing some null values and use the NVL function to count them:

SQL> select count(*) from dba_tab_comments
  2  where nvl(comments,'NULL') = 'NULL'
  3  /

  COUNT(*)
----------
      2354

SQL>

Count them again using IS NULL. The answer will be the same:

SQL> select count(*) from dba_tab_comments
  2  where comments is null
  3  /

  COUNT(*)
----------
      2354

SQL>

Finally, count them using = NULL. This will not find the null values:

SQL> select count(*) from dba_tab_comments
  2  where comments = null
  3  /

  COUNT(*)
----------
         0

SQL>