Indexing NULL table column values for fast SQL performance

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)
 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值