I would rather see the "upgrade" in this case take place when the alter
happens rather than creating the non-nullable index up front with null
checks, allowing unique indexes that never have this to enjoy the
performance possible when the null checking is not necessary. This
would mean either dropping/recreating the index at alter time or adding
a new store interface to "convert" from old index type to new index
type. For simplicity I would suggest doing the drop/create in first
implementation for this path and logging a subsequent task to look at
optimizing this alter table time performance.
Anurag shekhar wrote:
> Existing unique constraint will have only non nullable columns but it is
> possible
> to make columns null able (using statement "alter table <table name> alter
> column <column name> null") .
> Making columns null able isn't allowed on a column participating in
> unique constraint
> but as a part of this patch I have made that checking optional (only in
> case of soft
> upgrade it will throw an exception). Existing backing index will use the
> new code
> once the columns are made null able.
> anurag
>
> anurag
> Mike Matrigali wrote:
>> Why are you upgrading the backing indexes of existing unique
>> constraints, those that exist should only be on non-nullable columns
>> so should not need to be changed. I have not read through the new
>> code yet, but I assume also that new constraints on non-nullable columns
>> also don't use the "new" code - they should not pay the left/right
>> search cost.
>>
>>
>> Anurag Shekhar (JIRA) wrote:
>>> [
>>> https://issues.apache.org/jira/browse/DERBY-3330?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
>>> ]
>>>
>>> Anurag Shekhar updated DERBY-3330:
>>> ----------------------------------
>>>
>>> Attachment: derby-3330v8.diff
>>>
>>> In this patch (derby-3330v8.diff) I have modified upgrade routine to
>>> upgrade only the backing indexes of unique constraint. Other indexes
>>> are left unchanged.
>>>
>>> DataDictionaryImpl.getAllConglomerates (introduced in
>>> derby-3330v6.dif) is no more required so I have taken it off.
>>>
>>> Tests are running without any error or failures.
>>>> 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,
>>>> FunctionalSpec_DERBY-3330-V2.html, FunctionalSpec_DERBY-3330.html,
>>>> UniqueConstraint_Implementation.html
>>>>
>>>>
>>>> Allow unique constraint over nullable field. Right now derby support
>>>> unique constraint only over not null columns.
>>>
>>
>
>