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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Indexing NULL table column values for fast SQL performance

Oracle Tips by Burleson Consulting
September 21,  2003 - Revised January 1, 2007

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

create index
    emp_ename_idx
on
   emp 
   (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
    emp_ename_idx
on
   emp 
   (ename)
;

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
    emp_null_ename_idx
on
   emp 
   (nvl(ename,'null'));
 
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
    emp_null_emp_nbr_idx
on
   emp 
   (nvl(ename,o));
 
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) */
   ename
from
   emp e
where
   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;
 
 COUNT(1)
----------
      334
 
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;
 
 COUNT(1)
----------
      334
 
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 performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.