[
https://issues.apache.org/jira/browse/DERBY-2204?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12555438#action_12555438
]
A B commented on DERBY-2204:
----------------------------
> 2) A primary key in one table matches a primary key in another table,
> typically
> when the "sub" table contains additional optional information related to the
> main table.
[ snip ]
> Derby will create two identical backing indexes here, one for the primary key
> and one for the foreign key.
I think Derby does actually optimize for this case. There is code in the
"executeConstantAction()" method of CreateIndexConstantAction() which checks to
see if a duplicate index exists, and if so, it will re-use that index instead
of creating a new one.
That said, it looks Derby does not currently do the correct thing when the
primary key is dropped; see DERBY-3299.
> Foreign key constraint backing index creation can be smarter when foreign key
> is a subset of the table's primary key
> --------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2204
> URL: https://issues.apache.org/jira/browse/DERBY-2204
> Project: Derby
> Issue Type: Improvement
> Components: Performance, SQL
> Reporter: Daniel John Debrunner
>
> When a foreign key's columns is a subset (or matches) the table's primary key
> then there is no need to create an additional backing index. Such an index
> will occupy disk space and decrease insert/update/delete performance.
> Two typical situations where this is a problem are shown in the schema for
> the oe system test.
> 1) A multiple column primary key has a sub-set of columns which are a primary
> key in another table.
> ALTER TABLE CUSTOMER ADD CONSTRAINT
> CUSTOMER_PK PRIMARY KEY(C_W_ID, C_D_ID, C_ID);
> ALTER TABLE CUSTOMER ADD CONSTRAINT
> C_D_FK_DISTRICT FOREIGN KEY (C_W_ID,C_D_ID) REFERENCES DISTRICT;
> Derby will create two backing indexes here, one for the primary key and one
> for the foreign key. Derby could be improved
> so that no index is created for the foreign key, since Derby can perform an
> index scan on a sub-set of the first N columns in an index.
> Not sure how easy fixing the runtime portion of constraint manipulation is,
> if the constraint enforcement is via compiled SQL queries then it should be
> easier than if they are direct scans against the access api.
> 2) A primary key in one table matches a primary key in another table,
> typically when the "sub" table contains additional optional information
> related to the main table.
> ALTER TABLE NEWORDERS ADD CONSTRAINT
> NEWORDERS_PK PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID);
> ALTER TABLE NEWORDERS ADD CONSTRAINT
> NO_O_FK FOREIGN KEY (NO_W_ID, NO_D_ID, NO_O_ID) REFERENCES ORDERS;
> Derby will create two identical backing indexes here, one for the primary key
> and one for the foreign key.
> Fixing this case might be easier than 1) since Derby already supports having
> mutliple logical indexes map to a single physical index.
> In both cases work is needed to handle dropping of the primary key while the
> foreign key constraint is kept:
> - in case 1) a new index will need to be built using the sub-set of the
> columns, thus dropping a primary key constraint could take time.
> - in case 2) possibly the backing index can be re-used but it needs to be
> converted from a unique index to a non-unique one.
> Also the case where the primary key is created after the foreign key needs to
> be covered, though that could be a follow on.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
