DATABASE MANAGEMENT DELETE EFFICIENCY

• US 20140229427A1
• Filed: 02/11/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,960 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 computer-implemented method of managing a database contained in a storage facility, comprising:

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

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.

2. The method of claim 1, wherein the order rows are deleted is a hierarchical order of keys in indexes.

3. The method of claim 1, wherein the order rows are deleted is a spatial locality order of keys in indexes which includes prioritizing a key that is in a nearby location.

4. The method of claim 1, wherein the order rows are deleted is a temporal locality order of keys in indexes which includes prioritizing a key that is in a memory.

5. The method of claim 1, wherein the order rows are deleted is a frequency order of keys in indexes which includes prioritizing a key that is frequently accessed to a memory.

6. The method of claim 1, wherein the order rows are deleted is 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.

7. The method of claim 1, wherein the order rows are deleted is a cardinality order of keys in indexes which includes prioritizing keys in a first index that has a greater quantity of unique values than a second index.

8. The method of claim 1, wherein deleting rows in the database tables thereby manages the database contained in the storage facility.

9. A computer-implemented method of managing a database, comprising:

receiving a delete request for a first row and a second row of a table of the database, the first row associated with a first value and the second row associated with a second value;

determining a third value and a fourth value, both the third value and the fourth value associated with the delete request and with an index, the third value of the first row of the table associated with the first value and the fourth value of the second row of the table associated with the second value;

associating the third value with a first key value and the fourth value with a second key value, the first key value of a first key of the index associated with the first row of the table and the second key value of the second key of the index associated with the second row of the table; and

ordering the first row to be deleted before the second row when a comparatively closely related relationship includes the first key but not the second key, ordering the second row to be deleted before the first row when a comparatively closely related relationship includes the second key but not the first key, and ordering the first row to be deleted before the second row otherwise.

10. The method of claim 9, wherein the comparatively closely related relationship is a hierarchical relationship involving keys of the index.

11. The method of claim 9, wherein the comparatively closely related relationship is a spatial locality relationship involving keys of the index which include a difference in index key locations.

12. The method of claim 9, wherein the comparatively closely related relationship is a temporal locality relationship involving keys of the index which include whether a particular index key is in a memory.

13. The method of claim 9, wherein the comparatively closely related relationship is a frequency relationship involving keys of the index which include whether a particular index key is frequently accessed to a memory.

14. The method of claim 9, further comprising selecting a first index that has a greater number of rows than a second index.

15. The method of claim 9, further comprising selecting a first index that has a greater quantity of unique values than a second index.

16. The method of claim 9, wherein the first value and the third value are equivalent, and wherein the second value and the fourth value are equivalent.

17. A computer-implemented method of managing a database, comprising:

receiving a request to delete a first row and a second row in a table of a database;

determining a first index node of a first value of the first row and a second index node of a second value of the second row of an index of the database;

comparing a first relationship involving the first index node and a third index node of the index of the database with a second relationship involving the second index node and the third index node of the index of the database; and

setting the first row to be deleted before the second row when the first relationship is a comparatively closely related relationship, setting the second row to be deleted before the first row when the second relationship is a comparatively closely related relationship, and setting the first row to be deleted before the second row otherwise.

18. The method of claim 17, wherein the first relationship and the second relationship are hierarchical relationships involving nodes of the index.

19. The method of claim 17, wherein the first relationship and the second relationship are spatial locality relationships involving nodes of the index which include a difference in index node locations.

20. The method of claim 17, wherein the first relationship and the second relationship are temporal locality relationships involving nodes of the index which include whether a particular index node is in a memory.

21. The method of claim 17, wherein the first relationship and the second relationship are frequency relationships involving nodes of the index which include whether a particular index node is frequently accessed to a memory.

22. The method of claim 17, further comprising selecting a first index that has a greater number of rows than a second index.

23. The method of claim 17, further comprising selecting a first index that has a greater quantity of unique values than a second index.

24-29. -29. (canceled)