Re: Indexing columns with null values

Comments inline

Anurag

"Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> wrote in message 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?
No null should not really be a problem. In a concatenated index, null values will obviously be indexed too. Anyway your query queries for a specific value of state, thus Oracle is aware that you are looking for the not null values.

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

You should instead consider converting the mapping table into an IOT.

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

Partly yes. The optimizer parameters optimizer_index* also play a part in this decision (and so does db_file_multiblock_read_count).

Go to asktom.oracle.com and put in the following search "index not used" (in quotes). Read the links obtained.

>
> Thanks,
>
> Kevin
>
>
Received on Wed Jun 02 2004 - 18:35:17 CDT