Indexing columns with null values

From: Kevin Crosbie <caoimhinocrosbai_at_yahoo.com>
Date: Wed, 02 Jun 2004 10:32:51 GMT
Message-ID: <01e57dfbfd65034898427f56e0666c0f@news.teranews.com>

Hi,

I'm trying to figure out why an index is not being used.

I have 2 tables which I map together using a mapping table, the mapping table indicating the state:

a:
create table a (
a_id number primary key,
a_val number);

b:
create table b (
b_id number primary key,
b_val number);

a_b_map
create table a (

a_id   number references a,
b_id   number references b,
state char(1) default null, constraint state_p check(state in ('n', 'y',

null));

I have an index on a_id, state, b_id such that I can do the following: select b_id
from a_b_map
where a_id = :a
and state = 'n'

Note I never try to test if state is null.

My explain plan tells me that it does a full scan of the a_b_map, and since the table is quite large, this takes some time.

My questions are:
1) Is the null value causing this problem. If I made this into a third possible state value, say 'r' would this cause my index to be used? 2) I index a_id, state, b_id so that I can get b_id out ordered, because I normally use this select as a subquery. Does this make a difference or should I just index a_id, state?

The index was used before, it was after an analysis on the table and index that it stopped working... I'm not sure which analysis could have caused this, but does this mean something about my data?

Thanks,

Kevin Received on Wed Jun 02 2004 - 05:32:51 CDT