db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anurag shekhar <Anurag.Shek...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-3330) provide support for unique constraint over nullable columns
Date Wed, 27 Feb 2008 21:17:29 GMT
I will make the changes and upload a new patch.
Mike Matrigali wrote:
> 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
> applications.
> 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 
> constraint.
> 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