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.
