What are the issues regarding creating a index on a nullable column?

For example there are 3 nullable columns. I want to add one index on
each of the 3 nullable columns:

Table1 : 100,000 rows

id bigint not null primary key
empid1 bigint nullable null 9/10 of the time
empid2 bigint nullable null 1/3 of the time
empid3 bigint nullable null 1/3 of the time

thanks

As far as an index is concerned, a NULL is treated like any other value.
(Yeah, I know NULL is really the absense of a value, but index manager
failed the basic relational concepts course ;-)

Given your stats, you won't be able to create a unique index on the columns
since a unique undex will only allow one NULL. With a non-unique index, all
the NULLs will be gathered into a single index key (with lots of associated
RIDs).

--
__________________________________________________ ___________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Labs

DB2 UDB v8.1 is available for AIX, HP, Linux, Solaris and Windows
http://www-3.ibm.com/software/data/db2/udb/v8/

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2...s2unix/support

On DB2 for OS390 you can create a UNIQUE WHERE NOT NULL

Douglas Doole wrote:

>As far as an index is concerned, a NULL is treated like any other value.
>(Yeah, I know NULL is really the absense of a value, but index manager
>failed the basic relational concepts course ;-)
>
>Given your stats, you won't be able to create a unique index on the columns
>since a unique undex will only allow one NULL. With a non-unique index, all
>the NULLs will be gathered into a single index key (with lots of associated
>RIDs).
>
>
>

"Douglas Doole" <doole@ca.ibm.com> wrote in message
news:bf6e92$2tr$1@hanover.torolab.ibm.com...
> As far as an index is concerned, a NULL is treated like any other value.
> (Yeah, I know NULL is really the absense of a value, but index manager
> failed the basic relational concepts course ;-)

I'ld say it passed. It was SQL that flunked.

;-)

Regards
Paul Vernon
Business Intelligence, IBM Global Services