Smart index creation and reconciliation in an interconnected network of systems
A tool for using an interconnected network of systems to create an index for a database table. An index advisor on a primary server recommends one or more indexes to improve efficiency. While resources of the primary server are being used by various queries and processes, the primary server sends the recommendations to a secondary server (with available resources) so that the recommended indexes may be built in parallel with the processes executing on the primary server. The secondary server builds the recommended indexes based on its own copies of the database tables. The secondary server sends the built indexes to the primary server, where the primary server must reconcile the indexes with any changes that took place to the database tables subsequent to the replication of the tables on the secondary server. The primary server makes the associations between the new indexes and the tables they were built for.
1. A method for forming an index to access a database table, the method comprising the steps of:
a first server computer determining access performance and workload of at least some of a database, and in response, determining a recommendation for an index to a database table stored on the first server computer;
the first server computer sending the recommendation for the index to a second server computer, wherein the second server computer maintains a previous version of the database table;
the second server computer creating the recommended index using the previous version of the database table;
the first server computer receiving the recommended index from the second server computer;
the first server computer modifying the recommended index based on one or more differences between the database table stored on the first server computer and the previous version of the database table stored on the second server computer; and
the first server computer subsequently using the index to access an entry in the database table.
2. The method of claim 1 , further comprising the step of, before the step of using the index to access an entry in the database table, the first server computer determining an access plan, based in part on the index, for accessing entries in the database table.
3. The method of claim 1 , further comprising the step of, before the step of receiving the recommended index from the second server computer, the first server computer replicating the database table to the second server computer.
4. The method of claim 1 , further comprising the step of the first server computer receiving from the second server computer a timestamp corresponding to the most recent change of the previous version of the database table on the second server computer.
5. The method of claim 4 , wherein the step of modifying the recommended index based on changes to the database table made after the previous version comprises:
the first server computer scanning a database log corresponding to the database table, the database log keeping a record of all changes made to the database table;
the first server computer noting the changes to the database table occurring after the received timestamp; and
the first server computer adding the noted changes to the recommended index.
6. The method of claim 1 , wherein the step of the second server computer creating the recommended index using the previous version of the database occurs in parallel with processes executing on the first server computer.
7. A computer system for forming an index to access a database, the computer system comprising:
a processor;
a tangible, computer-readable memory for storing program instructions which when executed by the processor perform the steps of:
determining access performance and workload of at least some of a database, and in response, determining a recommendation for the index to a database table
sending the recommendation for the index from a first server computer to a second server computer, wherein the second server computer maintains a previous version of the database table;
receiving the recommended index at the first server computer from the second server computer;
modifying the recommended index based on one or more differences between the database table stored on the first server computer and the previous version of the database table stored on the second server computer; and
subsequently using the index to access an entry in the database table.
8. The computer system of claim 7 , wherein the product instructions when executed by the processor perform the further step of:
replicating the database table from the first server computer to the second server computer.
9. The computer system of claim 7 , wherein the product instructions when executed by the processor perform the further step of:
building the recommended index on the second server computer, based on the recommendation for the recommended index and the previous version of the database table, on the second server computer, of the database table.
10. The computer system of claim 7 , wherein the product instructions when executed by the processor perform the further step of:
determining an access plan, based in part on the index, for accessing the entry in the database table.
11. The computer system of claim 7 , wherein the product instructions when executed by the processor perform the further step of:
scanning a database log on the second server computer corresponding to the previous version of the database table for a timestamp of the most recent change to the previous version of the database table.
12. The computer system of claim 11 , wherein the product instructions when executed by the processor perform the further step of:
receiving at the first server computer the timestamp from the second server computer.
13. The computer system of claim 12 , wherein the program instructions, which when executed by the processor perform the step of modifying the recommended index based on changes to the database table made after the previous version of the database table, perform the steps of:
scanning a database log corresponding to the database table on the first server computer, the database log keeping a record of all changes made to the database table;
noting the changes to the database table occurring after the received timestamp; and
adding the changes to the database table occurring after the received timestamp to the recommended index.
14. A computer program product for forming an index to access a database, the computer program product comprising:
a tangible, computer-readable memory for storing program instructions which when executed by a processor perform the steps of:
determining access performance and workload of at least some of a database, and in response, determining a recommendation for the index to a database table
sending the recommendation for the index from a first server computer to a second server computer, wherein the second server computer maintains a previous version of the database table;
receiving the recommended index at the first server computer from the second server computer;
modifying the recommended index based on one or more differences between the database table stored on the first server computer and the previous version of the database table stored on the second server computer; and
subsequently using the index to access an entry in the database table.
15. The computer program product of claim 14 , wherein the program instructions, stored on the tangible, computer-readable memory, are downloaded over a network to a remote data processing system for use in a computer-readable memory in the remote data processing system.
16. The computer program product of claim 15 , wherein the product instructions when executed by the processor perform the further step of replicating the database table from the first server computer to the second server computer.
17. The computer program product of claim 15 , wherein the product instructions when executed by the processor perform the further step of building the recommended index on the second server computer, based on the recommendation for the recommended index and the previous version of the database table, on the second server computer, of the database table.
18. The computer program product of claim 15 , wherein the product instructions when executed by the processor perform the further step of determining an access plan, based in part on the recommended index, for accessing the entry in the database table.
19. The computer program product of claim 15 , wherein the product instructions when executed by the processor perform the further step of scanning a database log on the second server computer corresponding to the previous version of the database table for a timestamp of the most recent change to the previous version of the database table.
20. The computer program product of claim 19 , wherein the product instructions when executed by the processor perform the further step of receiving at the first server computer the timestamp from the second server computer.
21. The computer program product of claim 20 , wherein the program instructions, which when executed by the processor perform the step of modifying the index, perform the steps of:
scanning a database log corresponding to the database table on the first server computer, the database log keeping a record of all changes made to the database table;
noting the changes to the database table occurring after the received timestamp; and
adding the changes to the database table occurring after the received timestamp to the recommended index.