Apparatus and Method for Autonomic Index Creation, Modification and Deletion

• US 20070294272A1
• Filed: 06/09/2006
• Published: 12/20/2007
• Est. Priority Date: 06/09/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Anderson, Mark John, Bestgen, Robert Joseph, Flanagan, James Michael, Forstie, Scott, Schreiber, Thomas James
• Granted Patent: US 8,838,574 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2272 Management thereof
• CPC Class Codes: G06F 16/901 Indexing; Data structures therefor; Storage structures for retrieval from the web G06F16/951

Abstract

An index advice record engine generates and stores index advice records. An index advice policy mechanism allows a user to define an index advice policy that specifies criteria for autonomic index creation, modification and deletion. An autonomic index mechanism reads the index advice records, compares this information with the criteria in the user-defined index advice policies, and determines whether an index should be created, modified or deleted based on the information in the index advice records and the index advice policies. By automating the process of creating, modifying and deleting indexes according to user-defined policies, the preferred embodiments alleviate human users from most of the work of manually creating, modifying and deleting indexes.

Claims

1. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a database residing in the memory;

a user-defined index policy residing in the memory, the user-defined index policy specifying criteria for modification of an index for the database; and

an index mechanism that receives monitored activity of the database and autonomically modifies the index when the criteria specified in the user-defined index policy is satisfied by the monitored activity.

2. The apparatus of claim 1 further comprising:

an index advice record engine residing in the memory, the index advice record engine generating a plurality of index advice records that comprise the monitored activity of the database;

wherein the index mechanism reads the plurality of index advice records and determines whether information in the plurality of index advice records satisfies the criteria for modification of the index in the user-defined index policy, and if so, the index mechanism modifies the index.

3. The apparatus of claim 1 further comprising an index advice policy engine comprising a graphical user interface for receiving from a user the criteria for modification of the index.

4. The apparatus of claim 1 wherein the user-defined index policy further specifies criteria for deleting the index, wherein the index mechanism autonomically deletes the index when the criteria for deleting the index is satisfied by the monitored activity.

5. The apparatus of claim 1 wherein the index mechanism comprises a policy manager that allows a user to define criteria for using the user-defined index policy.

6. The apparatus of claim 1 wherein the user-defined index policy further specifies criteria for creation of a selected index in the database, the index mechanism determining whether the selected index was autonomically created in the past, and if so, the index mechanism determines whether the selected index was beneficial in the past, and if not, the index mechanism does not create the selected index.

7. The apparatus of claim 6 wherein the index mechanism deletes at least one index advice record that advised creation of the selected index if the selected index was not beneficial in the past.

8. A computer-implemented method for autonomically modifying an index for a database, the method comprising the steps of:

generating a user-defined index policy that specifies criteria for modification of the index;

receiving monitored activity of the database; and

autonomically modifying the index when the criteria specified in the user-defined index policy is satisfied by the monitored activity of the database.

9. The method of claim 8 further comprising the steps of:

generating a plurality of index advice records that comprise the monitored activity of the database;

reading the plurality of index advice records; and

determining whether information in the plurality of index advice records satisfies the criteria for modification of the index in the user-defined index policy, and if so, modifying the index.

10. The method of claim 8 wherein the user-defined index policy further specifies criteria for deleting the index, the method further comprising the step of autonomically deleting the index when the criteria for deleting the index is satisfied by the monitored activity.

11. The method of claim 8 further comprising the step of allowing a user to define criteria for using the user-defined index policy.

12. The method of claim 8 wherein the user-defined index policy further specifies criteria for creation of a selected index in the database, the method further comprising the steps of determining whether the selected index was autonomically created in the past, and if so, determining whether the selected index was beneficial in the past, and if not, not creating the selected index.

13. The method of claim 12 further comprising the step of deleting at least one index advice record that advised creation of the selected index if the selected index was not beneficial in the past.

14. A computer-readable program product comprising:

an index mechanism that receives monitored activity of a database and autonomically modifies an index when criteria specified in a user-defined index policy is satisfied by the monitored activity; and

recordable media bearing the index mechanism.

15. The program product of claim 14 further comprising:

an index advice record engine residing in the memory, the index advice record engine generating a plurality of index advice records that comprise the monitored activity of the database;

wherein the index mechanism reads the plurality of index advice records and determines whether information in the plurality of index advice records satisfies the criteria for modification of the index in the user-defined index policy, and if so, the index mechanism modifies the index.

16. The program product of claim 14 further comprising an index advice policy engine comprising a graphical user interface for receiving from a user the criteria for modification of the index.

17. The program product of claim 14 wherein the user-defined index policy further specifies criteria for deleting the index, wherein the index mechanism autonomically deletes the index when the criteria for deleting the existing index is satisfied by the monitored activity.

18. The program product of claim 14 wherein the index mechanism comprises a policy manager that allows a user to define criteria for using the user-defined index policy.

19. The program product of claim 14 wherein the user-defined index policy further specifies criteria for creation of a selected index in the database, the index mechanism determining whether the selected index was autonomically created in the past, and if so, the index mechanism determines whether the selected index was beneficial in the past, and if not, the index mechanism does not create the selected index.

20. The program product of claim 19 wherein the index mechanism deletes at least one index advice record that advised creation of the selected index if the selected index was not beneficial in the past.