Unique where not null

Nick Cianci

Unique where not null
Dear Comrades (in DB2),

I find myself contemplating an index. This 20-byte field is
initially blank/null, but after some other information gathering is
populated for life. The field needs to be searchable (hence an index).

In a brief interlude I find myself wishing for IMS and sparse indexing. As
I'll abhor the long RID chains on the NULL instance of this field, and the
hassles that the pseudo delete will cause when going from a NULL to a valid
value. (Definitely an index to REORG frequently)

The question I have though is:
if I create this index as "Unique Where Not Null" will DB2
recognise that the filtering is extremely good if I don't code a null-case
in the predicate? I've had a quick scan through the man-wells but can't find
anything that leaps out at me. My intuition says that it should.

Before anyone suggests using a "Sparse-Index-Table", it's been considered
and dismissed. I'd also like to tell the Application people not to populate
the row until the field is known, but that's not a real option either.

TIA

Ciao,
Nick Cianci
DB2 DBA - CCRI Project
2nd Floor (West) 484 StKilda Rd MELBOURNE 3004
* (+613) 9865-8554
(+61) 0408-64 06 01
* [login to unmask email]
_______________________________________________________________

No trees were killed in the sending of this
message. However - a large number of
electrons were terribly inconvenienced.
_______________________________________________________________



Edward Vassie

Re: Unique where not null
(in response to Nick Cianci)
My understanding of 'Unique where not null' is that only rows that contain
non-null values appear in the index. Rows with a Null in the column would
not be indexed, and if did a SELECT ... WHERE COL IS NULL you would get a TS
scan.

If this is right, then there would be no RID chain for Null entries.

On the other hand, I could be wrong...

From Edward Vassie...


-----Original Message-----
From: Cianci, Nick [mailto:[login to unmask email]
Sent: 12 January 2001 05:17
To: [login to unmask email]
Subject: Unique where not null


Dear Comrades (in DB2),

I find myself contemplating an index. This 20-byte field is
initially blank/null, but after some other information gathering is
populated for life. The field needs to be searchable (hence an index).

In a brief interlude I find myself wishing for IMS and sparse indexing. As
I'll abhor the long RID chains on the NULL instance of this field, and the
hassles that the pseudo delete will cause when going from a NULL to a valid
value. (Definitely an index to REORG frequently)

The question I have though is:
if I create this index as "Unique Where Not Null" will DB2
recognise that the filtering is extremely good if I don't code a null-case
in the predicate? I've had a quick scan through the man-wells but can't find
anything that leaps out at me. My intuition says that it should.

Before anyone suggests using a "Sparse-Index-Table", it's been considered
and dismissed. I'd also like to tell the Application people not to populate
the row until the field is known, but that's not a real option either.

TIA

Ciao,
Nick Cianci
DB2 DBA - CCRI Project
2nd Floor (West) 484 StKilda Rd MELBOURNE 3004
* (+613) 9865-8554
(+61) 0408-64 06 01
* [login to unmask email]
_______________________________________________________________

No trees were killed in the sending of this
message. However - a large number of
electrons were terribly inconvenienced.
_______________________________________________________________








Donna Di Carlo

Re: Unique where not null
(in response to Edward Vassie)
This is not true. A UNIQUE WHERE NOT NULL index is structured like a
non-unique index. It allows for a RID chain, but only chains the RIDs if
the value is NULL.

Donna Di Carlo
BMC Software

-----Original Message-----
From: Vassie, Edward [mailto:[login to unmask email]
Sent: Friday, January 12, 2001 3:26 AM
To: [login to unmask email]
Subject: Re: Unique where not null


My understanding of 'Unique where not null' is that only rows that contain
non-null values appear in the index. Rows with a Null in the column would
not be indexed, and if did a SELECT ... WHERE COL IS NULL you would get a TS
scan.

If this is right, then there would be no RID chain for Null entries.

On the other hand, I could be wrong...

From Edward Vassie...


-----Original Message-----
From: Cianci, Nick [mailto:[login to unmask email]
Sent: 12 January 2001 05:17
To: [login to unmask email]
Subject: Unique where not null


Dear Comrades (in DB2),

I find myself contemplating an index. This 20-byte field is
initially blank/null, but after some other information gathering is
populated for life. The field needs to be searchable (hence an index).

In a brief interlude I find myself wishing for IMS and sparse indexing. As
I'll abhor the long RID chains on the NULL instance of this field, and the
hassles that the pseudo delete will cause when going from a NULL to a valid
value. (Definitely an index to REORG frequently)

The question I have though is:
if I create this index as "Unique Where Not Null" will DB2
recognise that the filtering is extremely good if I don't code a null-case
in the predicate? I've had a quick scan through the man-wells but can't find
anything that leaps out at me. My intuition says that it should.

Before anyone suggests using a "Sparse-Index-Table", it's been considered
and dismissed. I'd also like to tell the Application people not to populate
the row until the field is known, but that's not a real option either.

TIA

Ciao,
Nick Cianci
DB2 DBA - CCRI Project
2nd Floor (West) 484 StKilda Rd MELBOURNE 3004
* (+613) 9865-8554
(+61) 0408-64 06 01
* [login to unmask email]
_______________________________________________________________

No trees were killed in the sending of this
message. However - a large number of
electrons were terribly inconvenienced.
_______________________________________________________________













Tim Lowe

Re: Unique where not null
(in response to Donna Di Carlo)
Nick,
Wouldn't use of an index index that is unique where not null depend on the
index stats gathered by runstats, just as with other access paths?
(I would expect that FREQVAL stats would show the percentage of rows that
are null, so that the optimizer could make a "wise" decision. But, I am
just guessing. Could the optimizer "understand" that if 90% of the values
are null, that 10% of the values are not null, and then use that to choose
the correct access path?)

Good question.

Thanks,
Tim