Re: Indexes

Brad Worsfold wrote:
>
> I know that you can create an index like this, but does Oracle actually add
> new entries to the index if one of the columns is null???? That is the
> question.
>
> Any ideas??
>
> Thanks,
>
> Tomm Carr <tommcatt_at_geocities.com> wrote in article
> <33D4DE6A.5BC0_at_geocities.com>...
> > Brad Worsfold wrote:
> > >
> > > If I want to create a composite index on two columns, will Oracle Index
 a
> > > row if one
> > > of the columns is optional? Meaning that one column has a value, the
 other
> > > is null???
> >
> > If I am not mistaken (meaning I'm too lazy to look it up at the moment)
> > you can create a *non-unique* index on a column that allows null values.
> >
> > > I was told that Indexes are not created on rows with Null Values?
> >
> > So. There you go listening to people again. Never take anyone's word
> > on anything (including mine). Create a test table and try it. See what
> > happens. If it can't be done the server won't get all huffy and refuse
> > to talk to you anymore!
> >
> > --
> > Tomm Carr
> > --
> > "Can you describe your assailant?"
> > "No problem, Officer. That's exactly what I was doing when he hit me!"
> >

Yes. An index may have a null in any of its column values.

Note, however, that, unlike every other use of nulls, a null in an index is regarded as a singular value. That is, an index value of (1,null) is treated as EQUAL to another index entry of (1,null).

An index entry is not made for an entry whuch is wholly null (e.g. (null,null,null)) This makes sense, since a predicate such as "a is null and b is null and c is null" will NOT be evaluated in an index on (a,b,c).

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"