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: [jira] Commented: (DERBY-3330) provide support for unique constraint over nullable columns
Date Wed, 27 Feb 2008 20:57:21 GMT
This is not what I thought was going to happen.  For backward 
compatibility (both performance, regression possibility and behavior), 
why change the implementation of unique non-nullable constraints, to
use a non-unique index?
Especially at the very end of the code freeze cycle.  I am ok with
moving forward on this if only constraints that could not be created
before this change use the new code, but if all constraints use the
new code then a lot more time/testing is needed.  By incrementally
enabling the new code for the new indexes we can address bugs
as they come up but they are unlikely to be regressions in existing

I don't know what the performance degredation will be, but it can come
in many forms:
o just added code path for using non-unique index
o bad/different statistics for using non-unique index for unique 
nonnullable constraint, leading to different query plans.
o more disk space required and longer insert times because now more 
indexes are needed because the physical/logical index optimization does
not apply as a different index is being used for non-nullable unique 
o future index optimization may not apply, there are some compression
   techniques that lend themselves better to unique indexes vs. non-unique.
o others i have not thought of.

Anurag shekhar wrote:
> Unique constraint on non nullable fields still act like a unique index 
> (because there is no null value) but internally they are backed by non 
> unique indexes with uniqueWhenNotNull attribute.
> anurag
> Mike Matrigali wrote:
>> I thought from functional spec and ongoing discussion that behavior of 
>> unique constraint on non-nullable columns would be unchanged.  So this
>> constraint should still be backed by a unique index.
>> Anurag Shekhar (JIRA) wrote:
>>>     [ 
>>> https://issues.apache.org/jira/browse/DERBY-3330?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12573032#action_12573032

>>> ]
>>> Anurag Shekhar commented on DERBY-3330:
>>> ---------------------------------------
>>> dml019 test group by clause of unique constraint. When unique 
>>> constraint was backed by unique index, distinct scan  was used but 
>>> after making it non unique constraint this was not the case so the 
>>> results are not ordered.
>>> I have checked the test suite from nist web site and it mandates only 
>>> number of rows and not their sequence.
>>>> provide support for unique constraint over nullable columns
>>>> -----------------------------------------------------------
>>>>                 Key: DERBY-3330
>>>>                 URL: https://issues.apache.org/jira/browse/DERBY-3330
>>>>             Project: Derby
>>>>          Issue Type: New Feature
>>>>          Components: Store
>>>>    Affects Versions:
>>>>         Environment: all
>>>>            Reporter: Anurag Shekhar
>>>>            Assignee: Anurag Shekhar
>>>>         Attachments: BTreeController.diff, derby-3330-testcase.diff, 
>>>> derby-3330.diff, derby-3330v2.diff, derby-3330v3.diff, 
>>>> derby-3330v4.diff, derby-3330v5.diff, derby-3330v6.diff, 
>>>> derby-3330v7.diff, derby-3330v8.diff, derby-3330v9.diff, 
>>>> FunctionalSpec_DERBY-3330-V2.html, FunctionalSpec_DERBY-3330.html, 
>>>> UniqueConstraint_Implementation.html, 
>>>> UniqueConstraint_Implementation_V2.html, 
>>>> UniqueConstraint_Implementation_V3.html
>>>> Allow unique constraint over nullable field. Right now derby support 
>>>> unique constraint only over not null columns.

View raw message