| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Indexing columns with null values
Apologies, I'm using Oracle 9.2.0.5.0
I suppose the data must remain skewed because of the null value. I was
hoping that it would index like:
1 null 2
1 null 3
1 'n' 4 1 'n' 5 1 'y' 6
such that it would be easy to look up.
Thanks for the advice.
"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:2i5ru7Fjai1fU1_at_uni-berlin.de...
> 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 - 09:52:04 CDT
 |
 |