Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (nike.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=sbcglobal.net;
  h=Received:X-YMail-OSG:X-Yahoo-Newman-Property:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=WhD49wN7+E5ZT1y/OqIiw2f1iMlxT7VBWYlY7jYbs8BreELUpGZYxtJwXQEPMD+17Piu87xLeuPAuGKmKO4WlC+Gs6Fgo9R6A/41VRwaKYZgHDaGkXrXmPSo6QwVpSw1JXyDPI4adwMrqphdonKPhtohZzoN4AyYuaA/2ODX3rI=
  ;
Message-ID: <47C5CEB1.2040304@sbcglobal.net>
Date: Wed, 27 Feb 2008 12:57:21 -0800
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: [jira] Commented: (DERBY-3330) provide support for unique
 constraint
 over nullable columns
References: <714104446.1204139751209.JavaMail.jira@brutus>
 <47C5C773.4010808@sbcglobal.net> <47C5CAB1.3030404@sun.com>
In-Reply-To: <47C5CAB1.3030404@sun.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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: 10.4.0.0
>>>>         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.
>>>
>>
> 
>