DATABASE MANAGEMENT DELETE EFFICIENCY

• US 20140229429A1
• Filed: 03/12/2013
• Published: 08/14/2014
• Est. Priority Date: 02/11/2013
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert J., Beuch, Daniel E.
• Granted Patent: US 9,229,961 B2
• Time in Patent Office: Days
• US Class Current: 707/609
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/214 Database migration support
• CPC Class Codes: G06F 16/902 using more than one table in sequence, i.e. systems with three or more layers

Abstract

A method, system, and computer program product to efficiently delete data from a database is disclosed. The method, system, and computer program product may include structuring the database to have a plurality of tables having indexes to related rows and having keys with key values associated with particular rows. The method, system, and computer program product may include deleting rows in the database tables by deleting keys in indexes related to the rows in an order such that corresponding rows are deleted based on relation to the keys. The method, system, and computer program product may include ordering the rows to be deleted based on concepts such as hierarchy, spatial locality, temporal locality, frequency of access, number of rows, and value uniqueness. Comparatively closely related relationships may be prioritized to be deleted.

Claims

1. A system comprising:

one or more processors; and

program code including a query optimizer, the program code configured to be executed by the one or more processors to process a database query, the program code configured to;

structure a database to have a plurality of tables having indexes to related rows and having keys with key values associated with particular rows; and

delete rows in the database tables by deleting keys in indexes related to the rows in an order such that corresponding rows are deleted based on relation to the keys.

2. The system of claim 1, wherein the order rows are deleted includes at least one of a hierarchical order of keys in indexes, a spatial locality order of keys in indexes which includes prioritizing a key that is in a nearby location, a temporal locality order of keys in indexes which includes prioritizing a key that is in a memory, and a frequency order of keys in indexes which includes prioritizing a key that is frequently accessed to a memory.

3. The system of claim 1, wherein the order rows are deleted includes at least one of a volume order of keys in indexes which includes prioritizing keys in a first index that has a greater number of rows than a second index and a cardinality order of keys in indexes which includes prioritizing keys in a third index that has a greater quantity of unique values than a fourth index.

4. A computer program product for processing a database query, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code comprising computer readable program code configured to:

structure a database to have a plurality of tables having indexes to related rows and having keys with key values associated with particular rows; and

delete rows in the database tables by deleting keys in indexes related to the rows in an order such that corresponding rows are deleted based on relation to the keys.

5. The computer program product of claim 4, wherein the order rows are deleted includes at least one of a hierarchical order of keys in indexes, a spatial locality order of keys in indexes which includes prioritizing a key that is in a nearby location, a temporal locality order of keys in indexes which includes prioritizing a key that is in a memory, and a frequency order of keys in indexes which includes prioritizing a key that is frequently accessed to a memory.

6. The computer program product of claim 4, wherein the order rows are deleted includes at least one of a volume order of keys in indexes which includes prioritizing keys in a first index that has a greater number of rows than a second index and a cardinality order of keys in indexes which includes prioritizing keys in a third index that has a greater quantity of unique values than a fourth index.