EFFICIENT FULL DELETE OPERATIONS

• US 20160283545A1
• Filed: 02/12/2016
• Published: 09/29/2016
• Est. Priority Date: 03/23/2015
• Status: Active Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Benke, Oliver, Kern, Johannes S., Martin, Daniel, Stolze, Knut
• Granted Patent: US 10,262,013 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/215 Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing

Abstract

In an approach for processing data in a database management system, a processor performs a delete operation on a first data container, wherein the first data container includes one or more data records and wherein the delete operation logically deletes at least one data record of the one or more data records. A processor associates a time of the performed delete operation with the first data container. A processor performs a write operation on a first data record of the first data container. A processor associates a time of the performed write operation with the first data record of the first data container. Responsive to receiving a database query directed at the first data container, a processor grants access to the first data record of the first data container based on, at least, the time of the performed write operation being chronologically after the time of the performed delete operation.

Claims

1. A method for processing data in a database management system, the method comprising:

performing, by one or more processors, a delete operation on a first data container, wherein the first data container includes one or more data records and wherein the delete operation logically deletes at least one data record of the one or more data records;

associating, by one or more processors, a time of the performed delete operation with the first data container;

performing, by one or more processors, a write operation on a first data record of the first data container;

associating, by one or more processors, a time of the performed write operation with the first data record of the first data container; and

responsive to receiving a database query directed at the first data container, granting access to, by one or more processors, the first data record of the first data container based on, at least, the time of the performed write operation being chronologically after the time of the performed delete operation.

2. The method of claim 1, wherein the first data container is a database table and the delete operation is a full-table delete operation selectively performed on the first data container.

3. The method of claim 1, wherein the first data container is a database table partition and the delete operation is a full-partition delete operation selectively performed on the first data container.

4. The method of claim 1, wherein the first data container comprises a plurality of data blocks, wherein one or more data blocks of the plurality of data blocks include data records of the one or more data records, and wherein each respective data block including data records of the one or more data records has an associated time indicating the most recently created data record stored in the data block.

5. The method of claim 4, wherein performing the delete operation on the first data container comprises:

performing, by one or more processors, a physical delete operation on the one or more data blocks of the plurality of data blocks, wherein the physical delete operation comprises, for each respective data block of the one or more data blocks;

identifying, by one or more processors, the most recent data record of the data block, based on times associated with each respective data record stored in the data block;

comparing, by one or more processors, the time associated with the most recent data record stored in the data block to the time of the performed delete operation; and

responsive to the time associated with the most recent data record stored in the data block being chronologically before the time of the performed delete operation, physically deleting, by one or more processors, the data block.

6. The method of claim 1, wherein the time of the performed delete operation is a current time and date value when performing the delete operation;

and

wherein the time of the performed write operation is a current time and date value when performing the write operation.

7. The method of claim 1, wherein the time of the performed delete operation is a transaction-identification (ID) associated with the performed delete operation;

wherein the time of the performed write operation is a transaction-ID associated with the performed write operation; and

wherein each transaction-ID is chronologically ordered with a unique number.

8. The method of claim 1, further comprising:

responsive to receiving the database query directed at the first data container, creating, by one or more processors, a second data container, wherein the second data container includes extracted data records from the first data container, wherein each extracted data record has an associated time chronologically after the time of the performed delete operation; and

wherein granting access to the first data record of the first data container comprises;

writing, by one or more processors, an alternate database query, such that the alternate database query accesses the second data container; and

executing, by one or more processors, the alternate database query.

9. The method of claim 8, wherein a schema definition of the second data includes, at least, the time of the performed delete operation.

10. The method of claim 1, wherein the time of the performed delete operation is a single data value.

11. The method of claim 1, wherein granting access to the first data record of the first data container comprises:

retrieving, by one or more processors, the time of the performed delete operation;

writing, by one or more processors, an alternate database query, wherein the alternate database query specifies a condition that is to be met by each respective data record in order to provide access to each respective data record, wherein the condition specifies that a time of the performed write operation for the respective data record is chronologically after the time of the performed delete operation; and

executing, by one or more processors, the alternate database query.

12. The method of claim 1, further comprising:

associating, by one or more processors, the time of the performed delete operation with the at least one data record of the one or more data records; and

wherein granting access to the first data record of the first data container is further based on the first data record being unassociated with the time of the performed delete operation.