db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: DB2 mode vs. Cloudscape mode
Date Fri, 04 Mar 2005 17:18:33 GMT
Satheesh is right, old versions of cloudscape allowed creating unique
indexes on null columns - but only one null was allowed.  Currently
derby uses the btree implementation to enforce uniqueness and it has
no support for treating null's differently than other values when
enforcing uniqueness.

I looked at this once and doing that support is not easy, it changes
a lot of basic assumptions in the unique key btree code path.  A lot
of the locking and searching optimizations that exist because keys
are known to be unique are no longer valid if one allows multiple nulls.
And it is not as simple as special casing one side of the tree since
a muli-column key can now put duplicates arbitrarily throughout the
tree (ie. 2 column key: (non null value, null value)).  The decision
was that the only way to support the standard given the existing 
technology was to support a subset
of the standard - it was better to not allow null's in unique indexes
than incorrectly supporting only one null in a unique index.At the time 
it was basically a resource
decision, and we did not see customers asking for it very much, it
seemed like a bad design to have null's in your primary key.

Does anyone know what oracle, microsoft and mysql do?

I looked at this once and looked down the road of special casing the
unique index code to support this behavior, but ran into problems.  If
I looked at it again I would look at using non-unique indexes if null's
are allowed, and instead special case the insert to check for an
existing matching row if any of the columns are not null.  With this
approach I think only insert path code need be changed, all other  (this 
avoid searching problems that I ran into).  One could
do this with existing store support by prior to any insert of a row into 
the index with any non-null column simply search for the row
giving the key as all of the columns not including the row location at
repeatable read isolation level.  It could be done more efficiently
lower in store, at a possible cost of increased code path for all other
btree accesses (but maybe this could be avoided by providing a inherited
implementation for this slightly different kind of btree).

Satheesh Bandaram wrote:
> Yes, Cloudscape used to allow creating UNIQUE indexes on null columns,
> but in a non-standard way. Old cloudscape used to allow only one NULL
> value in the UNIQUE index, where as, I think the standard required
> multiple NULL values.
> I think making Derby handle multiple NULLs in UNIQUE index needs changes
> to store/indexing.
> Satheesh
> Jeremy Boynes wrote:
>>Satheesh Bandaram wrote:
>>>Yes, you are right... The references to "DB2 mode" is from the past,
>>>leftover comments. There is no distinction between the two.
>>Did Cloudscape support UNIQUE on possibly null columns? If so, is this
>>a restriction that we can easily remove or is there something deep in
>>the index code that would make this problematic?
>>Also, can anyone think of an issue with making the NOT NULL implicit
>>in a PRIMARY KEY specification (per spec but unlike DB2)?

View raw message