Updating a partitioning column

Patent metadata

Field	Value
Publication number	US-10474697-B2
Application number	US-201514973596-A
Country	US
Kind code	B2
Filing date	Dec 17, 2015
Priority date	Dec 17, 2015
Publication date	Nov 12, 2019
Grant date	Nov 12, 2019

Abstract

An update to a partitioning column for a partitioned table in a database can be performed by handling rows for which the update will not require moving to a different partition separately (e.g. “locally”) for each partition followed by re-inserting those rows that will require moving to a different partition and deleting them from the original partition. The re-inserted rows are directed to a correct partition by application of the partitioning criteria for the table. Methods, articles, and systems are described.

First claim

What is claimed is:

1. A computer program product comprising a non-transitory machine-readable medium storing instructions that, when executed by at least one programmable processor, cause the at least one programmable processor to perform operations comprising:
identifying a set of values in a partitioning column that will be updated by one or more operations of an update statement on a partitioned table in a database, the partitioned table being partitioned into a plurality of partitions via partitioning logic;
determining which values of the set of values to be updated will remain in a same partition after being updated, the determining comprising evaluating the partitioning logic for the set of values to be updated;
executing the one or more operations of the update statement separately on each partition of the plurality of partitions that comprises one or more of the values of the set of values that will remain in the partition after being updated;
deleting, from the plurality of partitions, a set of rows containing other values of the set of values to be updated that will require moving to a different partition of the plurality of partitions once updated; and
re-inserting the set of rows into the database table such that the one or more operations of the update statement are executed for the set of rows and the partitioning logic directs each row of the set of rows to a proper partition;
wherein the update statement comprises one or more synchronization points, the one or more synchronization points acting to prevent the re-inserting until all partitions have had the one or more operations of the update statement locally executed.

2. A computer program product as in claim 1 , wherein the re-inserting further comprises including a statement identifier associated with each row of the set of rows, the statement identifier indicating that a row with which it is associated has been operated on by the update statement.

3. A computer program product as in claim 2 , wherein the executing of the one or more operations of the update statement separately on each partition comprises not operating on rows comprising the statement identifier.

4. A computer program product as in claim 1 , wherein the set of rows comprises a data package outputted by each partition, and wherein the data packages are not re-inserted until all partitions of the plurality of partitions have completed separately updating.

5. A computer program product as in claim 1 , wherein the update statement comprises the one or more operations specified in a data manipulation language.

6. A method for implementation by one or more data processors forming part of at least one computing system, the method comprising:
identifying a set of values in a partitioning column that will be updated by one or more operations of an update statement on a partitioned table in a database, the partitioned table being partitioned into a plurality of partitions via partitioning logic;
determining which values of the set of values to be updated will remain in a same partition after being updated, the determining comprising evaluating the partitioning logic for the set of values to be updated;
executing the one or more operations of the update statement separately on each partition of the plurality of partitions that comprises one or more of the values of the set of values that will remain in the partition after being updated;
deleting, from the plurality of partitions, a set of rows containing other values of the set of values to be updated that will require moving to a different partition of the plurality of partitions once updated; and
re-inserting the set of rows into the database table such that the one or more operations of the update statement are executed for the set of rows and the partitioning logic directs each row of the set of rows to a proper partition;
wherein the update statement comprises one or more synchronization points, the one or more synchronization points acting to prevent the re-inserting until all partitions have had the one or more operations of the update statement locally executed.

7. A method as in claim 6 , wherein the re-inserting further comprises including a statement identifier associated with each row of the set of rows, the statement identifier indicating that a row with which it is associated has been operated on by the update statement.

8. A method as in claim 7 , wherein the executing of the one or more operations of the update statement separately on each partition comprises not operating on rows comprising the statement identifier.

9. A method as in claim 6 , wherein the set of rows comprises a data package outputted by each partition, and wherein the data packages are not re-inserted until all partitions of the plurality of partitions have completed separately updating.

10. A method as in claim 6 , wherein the update statement comprises the one or more operations specified in a data manipulation language.

11. A system comprising:
computer hardware comprising a programmable processor, the computer hardware being configured to perform operations comprising:
identifying a set of values in a partitioning column that will be updated by one or more operations of an update statement on a partitioned table in a database, the partitioned table being partitioned into a plurality of partitions via partitioning logic;
determining which values of the set of values to be updated will remain in a same partition after being updated, the determining comprising evaluating the partitioning logic for the set of values to be updated;
executing the one or more operations of the update statement separately on each partition of the plurality of partitions that comprises one or more of the values of the set of values that will remain in the partition after being updated;
deleting, from the plurality of partitions, a set of rows containing other values of the set of values to be updated that will require moving to a different partition of the plurality of partitions once updated; and
re-inserting the set of rows into the database table such that the one or more operations of the update statement are executed for the set of rows and the partitioning logic directs each row of the set of rows to a proper partition;
wherein the update statement comprises one or more synchronization points, the one or more synchronization points acting to prevent the re-inserting until all partitions have had the one or more operations of the update statement locally executed.

12. A system as in claim 11 , wherein the re-inserting further comprises including a statement identifier associated with each row of the set of rows, the statement identifier indicating that a row with which it is associated has been operated on by the update statement.

13. A system as in claim 12 , wherein the executing of the one or more operations of the update statement separately on each partition comprises not operating on rows comprising the statement identifier.

14. A system as in claim 12 , wherein the set of rows comprises a data package outputted by each partition, and wherein the data packages are not re-inserted until all partitions of the plurality of partitions have completed separately updating.

15. A system as in claim 12 , wherein the update statement comprises the one or more operations specified in a data manipulation language.

Assignees

• Sap Se

Inventors

• Bensberg Christian
• Fricke Lars
• Heidel Martin
• Bischoff Holger
• Schmidt Joern
• Tyercha Edward-Robert
• Tonder Andreas

Classifications

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2386

  Bulk updating operations (data conversion details G06F16/258) · CPC title

• G06F16/278 Primary

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title