Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Indexing NULL table column

Oracle Tips by Burleson

September 21,  2003 - Revised
March 22, 2016

Note: Starting in Oracle 11g, there is new "create index" syntax that allows NULL values to be included in the index:

create index
   (ename asc, 1)

Here, the "1" tells Oracle that to index on NULL values within the tables.

One problem with pre 11g databases (see above) is having the optional ability to index on a NULL column.  By default, relational databases ignore NULL values (because the relational model says that NULL means "not present").  Hence, Oracle indexes will not include NULL values. 

For example, this index definition would not index on "open positions", new employee positions that are stored with a NULL employee name:

create index

Whenever a SQL query asks for the open position employee slots "where ename is NULL", there will be no index entries for NULLS in emp_name_idx and Oracle would perform an unnecessary large-table full-table scan.

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=6)

To get around the optimization of SQL queries that choose NULL column values, we can create a function-based index using the null value built-in SQL function to index only on the NULL columns. 

Note that the "null value" (NVL) function replaces NULL values with the character string "null', a real value that can participate in an index:

-- create an FBI on ename column with NULL values
create index
analyze index emp_null_ename_idx compute statistics;
You can also do this techniques with NULL numeric values.  This syntax replaces NULL values with a zero:
-- create an FBI on emp_nbr column with NULL values
create index
analyze index emp_null_ename_idx compute statistics;

Now we can use the index and greatly improve the speed of any queries that require access to the NULL columns.  Note that we must make one of two changes:

    1- Add a hint to force the index

   2 - Change the WHERE predicate to match the function

Here is an example of using an index on NULL column values:

-- insert a NULL row
insert into emp (empno) values (999);

set autotrace traceonly explain;

-- test the index access (change predicate to use FBI)
select /*+ index(emp_null_ename_idx) */
   emp e
   nvl(ename,'null') = 'null'

Reader Comments:

I was reading "Indexing NULL table column values for fast SQL performance" article, where you have mentioned that if the column is having null values (of very less amount) and you want to select where column is null then to use the index, create function based index and changed your query to use that index.
I think, it can be done without changing query as well......
SQL> select count(1) from t where n is null;
Execution Plan
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=3)
  1    0   SORT (AGGREGATE)
  2    1     TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=334 Bytes=1002)
SQL> create index tind on t(n, 1); ----> here 1 is just any arbitary value.
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(1) from t where n is null;
Execution Plan
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
  1    0   SORT (AGGREGATE)
  2    1     INDEX (RANGE SCAN) OF 'TIND' (NON-UNIQUE) (Cost=2 Card=3
         34 Bytes=1336)
Sandeep Redkar


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.