Deleting rows from tables in a database without an index

Patent metadata

Field	Value
Publication number	US-11403266-B2
Application number	US-201916430642-A
Country	US
Kind code	B2
Filing date	Jun 4, 2019
Priority date	Sep 11, 2015
Publication date	Aug 2, 2022
Grant date	Aug 2, 2022

Abstract

A method for deleting a row from a table in a database system comprises logically deleting the row in the first table in the database system by inserting a key of the row into a corresponding row of a dedicated table in the database system; querying the dedicated table during a query against the first table to identify the corresponding row in the dedicated table; and in response to identifying the corresponding row in the dedicated table, deleting the row from the first table and the corresponding row from the dedicated table as part of query processing during a subsequent query.

First claim

What is claimed is:

1. A database system comprising:
a memory configured to store:
a first table comprising a plurality of rows and that is an indexless table; and
a dedicated table; and

a processing unit coupled to the memory and configured to:
responsive to a request to delete a selected row in the first table, logically mark for deletion a selected row in the first table by inserting a key of the selected row into a corresponding row of the dedicated table that corresponds to the selected row to be deleted, wherein each row in the dedicated table corresponds to a respective selected row that has been logically marked for deletion in the first table;
query the dedicated table while executing a query against the first table to identify the corresponding row in the dedicated table; and
in response to identifying the corresponding row in the dedicated table, delete the selected row from the first table and the corresponding row from the dedicated table as part of query processing during a subsequent query;

wherein:
each row in the first table, as well as each row in the dedicated table, comprises a create timestamp attribute and a delete timestamp attribute;
the delete timestamp attribute of the corresponding row in the dedicated table is set to a value not permitted under normal operation such that the selected row in the first table is not read under normal operation of the database system; and
the key of the selected row is a unique primary key.

2. The database system of claim 1 , wherein the delete timestamp attribute of the corresponding row in the dedicated table is set to a value of −1.

3. The database system of claim 1 , wherein the processing unit is further configured to insert a modified version of the selected row in the first table when logically deleting the selected row.

4. The database system of claim 1 , wherein the processing unit is configured to establish a correspondence between rows in the dedicated table and rows in the first table by using all rows in the first table in which the create timestamp value is smaller than the create timestamp value in rows of the corresponding dedicated table.

5. The database system of claim 1 , wherein the processing unit is further configured to perform a query of the first table during a reorganization of the first table.

6. A program product comprising a computer readable storage medium having program instructions embodied thereon, wherein the program instructions are configured, when executed by at least one programmable processor, to cause the at least one programmable processor to, responsive to a request to delete a selected row in a first table comprising a plurality of rows and that is an indexless table:
logically mark for deletion a selected row in a first table in a database system by inserting a key of the selected row into a corresponding row of a dedicated table that corresponds to the selected row to be deleted in the database system, wherein each row in the dedicated table corresponds to a respective selected row that has been logically marked for deletion in the first table;
query the dedicated table during a query against the first table to identify the corresponding row in the dedicated table; and
in response to identifying the corresponding row in the dedicated table, delete the selected row from the first table and the corresponding row from the dedicated table as part of query processing during a subsequent query;

wherein:
each row in the first table, as well as each row in the dedicated table, comprises a create timestamp attribute and a delete timestamp attribute;
the program instructions are further configured to cause the at least one programmable processor to delete the selected row in the first table and the corresponding row in the dedicated table by setting the delete timestamp value of the corresponding row of the dedicated table to a value not permitted under normal operation such that the selected row in the first table is not read under normal operation of the database system; and
the key of the selected row is a unique primary key.

7. The program product of claim 6 , wherein the program instructions are further configured to cause the at least one programmable processor to insert a modified version of the selected row in the first table when logically deleting the selected row.

8. The program product of claim 6 , wherein the delete timestamp attribute of the corresponding row in the dedicated table is set to a value of −1.

9. The program product of claim 6 , wherein the program instructions are further configured to cause the at least one programmable processor to establish a correspondence between rows in the dedicated table and rows in the first table by using all rows in the first table in which the create timestamp value is smaller than the create timestamp value in rows of the corresponding dedicated table.

10. The program product of claim 6 , wherein the program instructions are further configured to cause the at least one programmable processor to perform a query of the first table during a reorganization of the first table.

11. A computer-implemented method comprising:
responsive to a request to delete a selected row in a first table comprising a plurality of rows and that is an indexless table:
logically deleting a selected row in a first table in a database system by inserting a key of the selected row into a corresponding row of a dedicated table that corresponds to the selected row to be deleted in the database system, wherein each row in the dedicated table corresponds to a respective selected row that has been logically marked for deletion in the first table;
querying the dedicated table during a query against the first table to identify the corresponding row in the dedicated table; and
in response to identifying the corresponding row in the dedicated table, marking for deletion the selected row from the first table and the corresponding row from the dedicated table as part of query processing during a subsequent query;

wherein:
each row in the first table, as well as each row in the dedicated table, comprises a create timestamp attribute and a delete timestamp attribute;
logically deleting the selected row of the first table comprises setting the delete timestamp attribute of the corresponding row in the dedicated table to a value not permitted under normal operation such that the selected row in the first table is not read under normal operation of the database system; and
the key of the selected row is a unique primary key.

12. The method of claim 11 , wherein setting the delete timestamp attribute of the corresponding row in the dedicated table to a value not permitted under normal operation comprises setting the delete timestamp attribute of the corresponding row in the dedicated table to a value of −1.

13. The method of claim 11 , wherein logically deleting the selected row further comprises inserting a modified version of the selected row in the first table.

14. The method of claim 11 , further comprising establishing a correspondence between rows in the dedicated table and rows in the first table by using all rows in the first table in which the create timestamp value is smaller than the create timestamp value in rows of the corresponding dedicated table.

15. The method of claim 11 , further comprising performing a query of the first table during a reorganization of the first table.

Assignees

• IBM

Inventors

• Brodt Andreas
• Koeth Oliver
• Martin Daniel
• Stolze Knut

Classifications

• G06F16/215 Primary

  Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors · CPC title

• G06F16/162

  Delete operations (erasing in storage systems G06F3/0652) · CPC title

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title

• G06F16/235

  Update request formulation · CPC title

• G06F16/2455

  Query execution · CPC title