Oracle Index - For null values

Question

I read indexes but i am not sure I understand this point correctly. Please elaborate this point.

Is bitmap or btree index not applicable for null

I create b-tree index on hr.employees.first_Name and hr.employees.Last_name.
Last_name has not null property where first_name can be null.

When I do select last_name from employees it is doing index full scan and when I do select first_name from employees it is doing table access full.

Sanders the Softwarer Sanders the Softwarer · Accepted Answer · 2015-03-20T11:10:26

In Oracle, b-tree index does not contain info about null keys. It means:

create table X (i integer, j integer);

create index X_j on X(j);

insert into X values (1, 1);      -- record would be indexed
insert into X values (2, 2);      -- record would be indexed
insert into X values (3, null);   -- record would not be indexed

select rowid from X;                       -- index cannot be used
select rowid from X where j = 1;           -- index can be used
select rowid from X where j is not null;   -- index can be used
select rowid from X where j is null;       -- index cannot be used

If indexing several columns, index would contain record if at least one column has not null value.

If index if function-based, same rule exists about expression result.

Bitmap indexes contains null keys. There is no any difference for null and not null values for them.

It does not mean you should use bitmap index if you wish to catch null values. Bitmap indexes requires exclusive access while updating, so they cannot be used into OLTP environment. If you wish to catch null values by index, use function-based index for converting null values into some not-nulls, say

create index employee_firstname_fbi on employees('x' || first_name);
select * from employees where 'x' || first_name = 'x';

Oracle Index - For null values

1 Answers