Re: Indexing columns with null values

And one more candidate with no version info ... Comments inline

"Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> schrieb im Newsbeitrag news:01e57dfbfd65034898427f56e0666c0f_at_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?

AFAIK, NULL values can only be indexed using bitmap indexes, other index types do not take NULLs into their trees.

> 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?
>

No, keep on indexing a_id, state, b_id.

> 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?
>

You say that problems started after taking statistics. Well, I have exactly 1 index (among 400) in my actual db that has the same problem,
and unless we figure out why, we delete the statistics of that index:

analyze index myIndexName delete statistics;

For some reason, it works in our case, the index is taken again then :|

> Thanks,
>
> Kevin
>
>

If that helps, good luck.
For more help, more details are needed.

hth, Jan Received on Wed Jun 02 2004 - 06:24:25 CDT