Oracle Index Null Values

| Posted by watashii | Filed under Database, Programming

When creating Oracle indexes, Oracle ignores the NULL values.  For example, the following index was created over “emp_name” column.

create index emp_name_idx
on employees (emp_name);

Querying for a NULL value on the indexed column would yield a full table scan.

select emp_name from employees where emp_name is NULL;

So how can we make Oracle perform a fast indexed search?

The solution is to index NULL values with a function-based index.  The following example replaces all the NULL values with a string named ‘null’ (note, this can be any arbitrary string).  This allows the NULL to be included as if it was a real value.

create index emp_name_idx
on employees (nvl(emp_name,'null'));

Share:

  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • Print
  • email

Related Posts:

  1. Oracle SQL Loader – Importing CSV files to a table
  2. PL/SQL – Pipelined Table Function
  3. BusinessObjects Universe Designer – Sysdate Value Object
  4. Oracle Timezone Conversions – GMT to localtime (and back)
  5. Starting / Shutdown Oracle Database in Unix

Tags: , ,

Permalink | September 21st, 2008

Leave a Reply