You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Daniel John Debrunner (JIRA)" <ji...@apache.org> on 2007/01/03 02:32:27 UTC
[jira] Created: (DERBY-2204) Foreign key constraint backing index
creation can be smarter when foreign key is a subset of the table's primary
key
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.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2204) Foreign key constraint backing index
creation can be smarter when foreign key is a subset of the table's primary
key
Posted by "A B (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-2204) Foreign key constraint backing index
creation can be smarter when foreign key is a subset of the table's primary
key
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2204?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2204:
---------------------------------
Derby Categories: [Performance]
> 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: 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.