Partition move in case of table update

Patent metadata

Field	Value
Publication number	US-11048692-B2
Application number	US-201916373052-A
Country	US
Kind code	B2
Filing date	Apr 2, 2019
Priority date	Apr 2, 2019
Publication date	Jun 29, 2021
Grant date	Jun 29, 2021

Abstract

A system includes reception of a query to update a partition key value of a first set of rows of a database table, determination, based on fetched row identifiers, of a first subset of the first rows which are not stored on the first database server node and a second subset of the first rows which are stored on the first database server node, fetching of the first subset of rows from the database server nodes, update of the partition key value of each row of the fetched first subset of rows, instructing of the first database server node to: store the updated rows of the fetched first subset in the first partition stored on the first database server node, and update the partition key value of each of the second subset of rows of the partition stored on the first database server node.

First claim

What is claimed is:

1. A system comprising:
a first database server node comprising a processor and memory including instructions which when executed by the processor cause the first database server node to:
receive a query to update a partition key value of a first set of rows of a database table, the updated partition key value associated with a first partition of the database table stored on a second database server node, and the first set of rows comprising rows of two or more partitions of the database table stored in two or more database server nodes;
fetch row identifiers of each of the first set of rows from the database server nodes in which each of the first set of rows is stored, wherein the row identifiers are stored separately from the partition key values;
based on the row identifiers, determine a first subset of the first rows which are not stored on the second database server node and a second subset of the first rows which are stored on the second database server node;
fetch the first subset of rows from the database server nodes in which each of the first set of rows is stored;
update the partition key value of each row of the fetched first subset of rows;
instruct the second database server node to store the updated rows of the fetched first subset in the first partition stored on the second database server node;
instruct the second database server node to update the partition key value of each of the second subset of rows of the partition stored on the second database server node; and
update the row identifier of each of the first set of rows based on the updated partition key value.

2. A system according to claim 1 , further comprising:
the second database server node storing the first partition; and
a third database server node storing at least one of the first subset of rows, the at least one of the first subset of rows associated with a second partition of the database table.

3. A system according to claim 1 , wherein determination of the first subset and the second subset is based on a first four bits of the fetched row identifiers.

4. A system according to claim 1 , the first database server node to:
receive a second query to update a partition key value of a second set of rows of the database table, the updated partition key value of the second set of rows associated with a second partition of the database table stored on a third database server node, and the second set of rows comprising rows of two or more partitions of the database table stored in the two or more database server nodes;
fetch second row identifiers of each of the second set of rows from the database server nodes in which each of the second set of rows is stored;
based on the second row identifiers, determine a first subset of the second set of rows which are not stored on the third database server node and a second subset of the second set of rows which are stored on the third database server node;
fetch the first subset of the second set of rows from the database server nodes in which each of the second set of rows is stored;
update the partition key value of each row of the fetched first subset of the second set of rows;
instruct the third database server node to store the updated rows of the fetched first subset of the second set of rows in the second partition stored on the third database server node; and
instruct the third database server node to update the partition key value of each of the second subset of the second set of rows of the second partition stored on the third database server node.

5. A system according to claim 1 , the first database server node to:
receive a second query to update a partition key value of a first set of rows of a second database table, the updated partition key value associated with a first partition of the second database table stored on the second database server node, and the first set of rows comprising rows of two or more partitions of the database table stored in two or more database server nodes;
fetch row identifiers of each of the first set of rows of the second database table from the database server nodes in which each of the first set of rows of the second database table is stored;
based on the row identifiers, determine a first subset of the first rows of the second database table which are not stored on the second database server node and a second subset of the first rows of the second database table which are stored on the second database server node;
fetch the first subset of rows of the second database table from the database server nodes in which each of the first set of rows of the second database table is stored;
update the partition key value of each row of the fetched first subset of rows of the second database table;
instruct the second database server node to store the updated rows of the fetched first subset of the second database table in the first partition stored on the second database server node; and
instruct the second database server node to update the partition key value of each of the second subset of rows of the second database table of the partition stored on the second database server node.

6. A system according to claim 5 , wherein determination of the first subset and the second subset is based on a first four bits of the fetched row identifiers.

7. A computer-implemented method, comprising:
receiving a query to update a partition key value of a first set of rows of a database table;
determining that the updated partition key value is associated with a first partition of the database table stored on a first database server node;
fetching row identifiers of each of the first set of rows from two or more database server nodes in which each of the first set of rows is respectively stored, wherein the row identifiers are stored separately from the partition key values;
based on the row identifiers, determining a first subset of the first rows which are not stored on the first database server node and a second subset of the first rows which are stored on the first database server node;
fetching the first subset of rows from the database server nodes in which each of the first set of rows is respectively stored;
updating the partition key value of each row of the fetched first subset of rows;
instructing the first database server node to store the updated rows of the fetched first subset in the first partition stored on the first database server node;
instructing the first database server node to update the partition key value of each of the second subset of rows of the partition stored on the first database server node; and
updating the row identifier of each of the first set of rows based on the updated partition key values.

8. A method according to claim 7 , wherein a second database server node stores at least one of the first subset of rows, the at least one of the first subset of rows associated with a second partition of the database table.

9. A method according to claim 7 , wherein determining the first subset and the second subset is based on a first four bits of the fetched row identifiers.

10. A method according to claim 7 , further comprising:
receiving a second query to update a partition key value of a second set of rows of the database table;
determining that the updated partition key value of the second set of rows is associated with a second partition of the database table stored on a second database server node;
fetching second row identifiers of each of the second set of rows from the database server nodes in which each of the second set of rows is stored;
based on the second row identifiers, determining a first subset of the second set of rows which are not stored on the second database

Assignees

• Sap Se

Inventors

• Jung Sunggun
• Cho Sukhyeun
• Yoon Taesik
• Schmidt Joern

Classifications

• G06F16/278 Primary

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

• G06F16/24553

  of query operations · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title