Deleting rows from tables in a database without an index

Patent metadata

Field	Value
Publication number	US-10691656-B2
Application number	US-201615355233-A
Country	US
Kind code	B2
Filing date	Nov 18, 2016
Priority date	Sep 11, 2015
Publication date	Jun 23, 2020
Grant date	Jun 23, 2020

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 method for deleting a first row from a first table in a database system, said method comprising:
logically deleting the first row in the first table in the database system by inserting a key of the first row into a corresponding row of a dedicated table in the database system and setting a create timestamp attribute of the corresponding row in the dedicated table to a delete timestamp value of a delete operation for the first row;
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 first row from the first table and the corresponding row from the dedicated table as part of query processing during a subsequent query;
wherein deleting the first row in the first table and the corresponding row in the dedicated table comprises setting a delete timestamp attribute of the first row and a delete timestamp attribute of the corresponding row of the dedicated table to the delete timestamp value in the create timestamp attribute of the corresponding row of the dedicated table.

2. The method according to claim 1 , wherein the first table does not have an index.

3. The method according to claim 1 , further comprising inserting a modified first row in the first table.

4. The method according to claim 1 , 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.

5. The method according to claim 1 , further comprising performing a query of the first table during a reorganization of the first table.

6. The method of claim 1 , wherein querying the dedicated table during a query against the first table further comprises:
reading the first row from the first table; and
in response to determining that the corresponding row in the dedicated table has the create timestamp value set to the delete timestamp value of the delete operation and that the delete timestamp attribute of the corresponding row is null, excluding the first row from results of the query against the first table.

Assignees

• IBM

Inventors

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

Classifications

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title

• G06F16/162

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

• G06F16/235

  Update request formulation · CPC title

• G06F16/215 Primary

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

• G06F16/2322

  using timestamps · CPC title