Oracle indexes nulls in the leading part of a composite index???

From: <mbriganti_at_my-deja.com>
Date: Thu, 06 Jan 2000 20:03:00 GMT
Message-ID: <852sda$bci$1@nnrp1.deja.com>

Here is an interesting situation for anyone who cares. Why does Oracle index nulls in the leading part of a composite index when it can never use them?

For example ...

create table test (

non_mandatory_column varchar2(10) null, mandatory_column varchar2(10) not null )

create index composite_index on
test (non_mandatory_column, mandatory_column);

insert into test values (NULL, 'ABC');

THIS ROW GETS INDEXED, but can Oracle ever use this entry in the index? Can anyone think of a WHERE clause?

Thanks in advance for all replies.

Mimmo Briganti
i2 Technologies

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 06 2000 - 14:03:00 CST