One of the most common reason why index is not used is because columns are defined as nullable when, in fact, those columns should be not null.

Here is an example.

create table t( c1 number, c2 varchar2(4000));
create index t_idx on t(c1);

begin
for i in 1..10000 loop
insert into t values(i,'abc');
end loop;
end;
/

exec dbms_stats.gather_table_stats('TEST','T');

select c1 from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 40000 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 10000 | 40000 |    18   (0)| 00:00:01 |
--------------------------------------------------------------------------

This is because c1 is nullable (can be null) , so the optimizer cannot rely on the index as there might be missed rows .
Since the every row in c1 contains value(from 1 to 10000), column c1 should be defined as not null instead of null.

alter table t modify (c1 not null);

select c1 from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3163761342

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 10000 | 40000 |     7   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| T_IDX | 10000 | 40000 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

After redefining the column c1 as not null, index t_idx is used and cost of this query is reduced from 18 to 7.

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Furl
  • Reddit
  • Spurl
  • StumbleUpon
  • Technorati