Automatic Maintenance of a Set of Indexes with Different Currency Characteristics in a Database Management System

• US 20120016881A1
• Filed: 04/07/2011
• Published: 01/19/2012
• Est. Priority Date: 07/13/2010
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hrle, Namik, Schuetzner, Johannes, Teng, James Z.
• Granted Patent: US 10,452,629 B2
• Time in Patent Office: Days
• US Class Current: 707/746
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

Abstract

Methods and apparatus, including computer program products, for maintaining a set of indexes in a database management system (DBMS) having at least one table. A current, stale or deferred status is defined for at least a part of the indexes, resulting in at least a part of a set of current, stale, or deferred indexes in the DBMS. Current indexes are maintained by refreshing a current index synchronously with a table change relating to the current index. Stale indexes are maintained by refreshing a stale index continuously and asynchronously to table modifications of tables relating to the stale index based on log information relating to the modifications. Deferred indexes are maintained by building a deferred index in response to a query to a table relating to the deferred index, thereby bringing the deferred index in accordance with the current query time status to the table relating to the deferred index.

Claims

1. A computer-implemented method for maintaining a set of indexes in a database management system having at least one table, the method comprising:

defining for at least a part of said indexes a status out of a group comprising a current status, a stale status, and a deferred status, resulting in at least a part of a set of current indexes, a set of stale indexes, and a set of deferred indexes in the database management system;

maintaining the set of current indexes by refreshing a current index synchronously together with a table change relating to the current index;

maintaining the set of stale indexes by refreshing a stale index continuously and asynchronously to table modifications relating to a table relating to the stale index based on log information relating to the table modifications; and

maintaining the set of deferred indexes by building a deferred index in response to a query to a table relating to the deferred index, thereby bringing the deferred index in accordance with the current status for a time of the query to the table relating to the deferred index.

2. The method according to claim 1, wherein the stale index is maintained in the current status until a predefined criterion is met which is based on a predefined stale- current-threshold.

3. The method according to claim 1, further comprising:

monitoring for at least a part of the stale indexes the log information since a last refresh of the stale index, and if a predefined threshold relating to index changes in the log information relating to the stale index exceeds a predefined value, triggering a refreshing of the stale index.

4. The method according to claim 1, further comprising:

maintaining the set of stale indexes by refreshing the stale index in response to a first query to a table relating to the stale index based on the log information accumulated since a previous refreshing of the stale index, thereby bringing the stale index in accordance with the current status for the time of the first query to the table relating to the stale index.

5. The method according to claim 1, wherein a second query is performed against a table relating to the stale index having a non-updated stale index.

6. The method according to claim 1, wherein the deferred index is maintained in the stale status until a predefined criterion is met depending on a predetermined deferred-stale-threshold.

7. The method according to claim 1, further comprising:

delaying, in response to a query to the table relating to the stale index, an access to the table relating to the stale index until the table relating to the stale index has been brought to the current status.

8. The method according to claim 1, further comprising:

partitioning the table relating to the stale index and the stale index, and wherein the stale index partitions are in different statuses.

9. The method according to claim 8, wherein partitioned stale index partitions are refreshed independently from each other.

10. The method according to claim 9, wherein the independent refreshing is performed temporarily in parallel, temporarily overlapping, or sequentially.

11. A database management system having at least one table, the database management system being adapted for maintaining a set of indexes and comprising:

a defining unit operable to define, for at least a part of said indexes, a status out of a group comprising a current status, a stale status, and a deferred status, resulting in at least a part of a set of current indexes, a set of stale indexes, and a set of deferred indexes in the database management system;

a first maintaining unit operable to maintain the set of current indexes by refreshing a current index synchronously together with a table change relating to the current index; and

a second maintaining unit operable to maintain the set of stale indexes by refreshing a stale index continuously and asynchronously to table modifications relating to a table relating to the stale index based on log information relating to the table modifications;

wherein the first maintaining unit is further operable to maintain the set of deferred indexes by building a deferred index in response to a query to a table relating to the deferred index, thereby bringing the deferred index in accordance with the current status for a time of the query to the table relating to the deferred index.

12. A computer system comprising:

a database management system having at least one table, the database management system being adapted for maintaining a set of indexes and comprising;

a defining unit operable to define, for at least a part of said indexes, a status out of a group comprising a current status, a stale status, and a deferred status, resulting in at least a part of a set of current indexes, a set of stale indexes, and a set of deferred indexes in the database management system;

a first maintaining unit operable to maintain the set of current indexes by refreshing a current index synchronously together with a table change relating to the current index; and

a second maintaining unit operable to maintain the set of stale indexes by refreshing a stale index continuously and asynchronously to table modifications relating to a table relating to the stale index based on log information relating to the table modifications;

wherein the first maintaining unit is further operable to maintain the set of deferred indexes by building a deferred index in response to a query to a table relating to the deferred index, thereby bringing the deferred index in accordance with the current status for a time of the query to the table relating to the deferred index;

a processing unit adapted for processing data to operate the database management system; and

a storage storing the at least one table and being accessible by the processing unit.

13. A computer program product for maintaining a set of indexes in a database management system having at least one table, the computer program product comprising:

a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code comprising;

computer readable program code configured to define for at least a part of said indexes a status out of a group comprising a current status, a stale status, and a deferred status, resulting in at least a part of a set of current indexes, a set of stale indexes, and a set of deferred indexes in the database management system;

computer readable program code configured to maintain the set of current indexes by refreshing a current index synchronously together with a table change relating to the current index;

computer readable program code configured to maintain the set of stale indexes by refreshing a stale index continuously and asynchronously to table modifications relating to a table relating to the stale index based on log information relating to the table modifications; and

computer readable program code configured to maintain the set of deferred indexes by building a deferred index in response to a query to a table relating to the deferred index, thereby bringing the deferred index in accordance with the current status for a time of the query to the table relating to the deferred index.

14. The computer program product according to claim 13, wherein the stale index is maintained in the current status until a predefined criterion is met which is based on a predefined stale-current-threshold.

15. The computer program product according to claim 13, further comprising:

computer readable program code configured to monitor for at least a part of the stale indexes the log information since a last refresh of the stale index, and if a predefined threshold relating to index changes in the log information relating to the stale index exceeds a predefined value, triggering a refreshing of the stale index.

16. The computer program product according to claim 13, further comprising:

computer readable program code configured to maintain the set of stale indexes by refreshing the stale index in response to a first query to a table relating to the stale index based on the log information accumulated since a previous refreshing of the stale index, thereby bringing the stale index in accordance with the current status for the time of the first query to the table relating to the stale index.

17. The computer program product according to claim 13, wherein a second query is performed against a table relating to the stale index having a non-updated stale index.

18. The computer program product according to claim 13, wherein the deferred index is maintained in the stale status until a predefined criterion is met depending on a predetermined deferred-stale-threshold.

19. The computer program product according to claim 13, further comprising:

computer readable program code configured to delay, in response to a query to the table relating to the stale index, an access to the table relating to the stale index until the table relating to the stale index has been brought to the current status.

20. The computer program product according to claim 13, further comprising:

computer readable program code configured to partition the table relating to the stale index and the stale index, and wherein the stale index partitions are in different statuses.

21. The computer program product according to claim 20, wherein partitioned stale index partitions are refreshed independently from each other.

22. The computer program product according to claim 21, wherein the independent refreshing is performed temporarily in parallel, temporarily overlapping, or sequentially.