Index selection in a multi-system database management system

• US 9,418,092 B2
• Filed: 05/24/2013
• Issued: 08/16/2016
• Est. Priority Date: 12/30/2008
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Morris, John Mark, Brown, Douglas P.
• Primary Examiner(s): Fan, Shiow-Jy
• Application Number:
  US13/901,875
• Publication Number:
  US 20130254210A1
• Time in Patent Office: 1,180 Days
• Field of Search: 707/741
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

A system includes a multi-system database management system having a plurality of database systems. An index selection subsystem combines sets of query information from respective ones of the plurality of database systems into a workload. The index selection subsystem then generates candidate indexes from the workload, and selects recommended indexes from the candidate indexes based on one or more criteria.

Claims

1. A system, comprising:

a multi-system database management system that includes a plurality of database systems with different configurations from each other, and wherein each of the database systems can process data independently by using one or more processors, wherein each of the different configurations at least includes a number of database nodes and a number of access modules in each database node; and

an index selection subsystem that includes one or more processors operable to;

combine sets of query information from each one of the plurality of database systems into a workload that includes merged query information of the plurality of database systems;

generate, from the workload, one or more candidate indexes for each one of the plurality of the database systems; and

select one or more recommended indexes from the candidate indexes for each one of the plurality of database systems at least partly based on the configuration of each one of the plurality of database systems, wherein the recommended indexes comprise a first recommended index for a first copy of a given table in the first of the plurality of database systems, and a second recommended index for a second copy of the given table in the second of the plurality of database systems.

2. The system of claim 1, wherein selecting recommended indexes includes adding an index or removing an index.

3. The system of claim 1, wherein the selected recommended indexes are replicated across the plurality of database systems.

4. The system of claim 1, wherein the first and second recommended indexes are selected from among primary indexes, partitioned primary indexes, join indexes, and multi-level partitioned primary indexes.

5. The system of claim 1, wherein the first and second recommended indexes are secondary indexes.

6. The system of claim 5,

wherein each of the different configurations further includes one or more of the following:

number of parsing engines; and

a version of database software; and

wherein the selecting of the one or more recommended indexes from the candidate indexes for each one of the plurality of database systems is performed at least partly based on one or more of the following;

the number of database nodes and the number of access modules in each database node;

the number of parsing engines; and

the version of database software.

7. The system of claim 1, wherein the index selection subsystem includes multiple threads of an index selection tool that run in parallel.

8. The system of claim 7, wherein the index selection subsystem further includes multiple threads of a parsing engine that run in parallel.

9. The system of claim 1, wherein at least two of the plurality of database systems of the multi-system database management system are geographically dispersed in different cities.

10. The system of claim 1, wherein the index selection subsystem includes one or more optimizers, and wherein the selected indexes are based on a cost analysis performed by the one or more optimizers.

11. The system of claim 10, wherein the cost analysis performed by the one or more optimizers is performed in an emulated environment that emulates the plurality of database systems in the multi-system database management system.

12. The system of claim 11, further comprising a target emulation tool retrieve target emulation data from the database systems to create the emulated environment.

13. The system of claim 1, further comprising a global dictionary that contains information relating to schemas used in the plurality of database systems.

14. A method executed by a computer for use with a multi-system database management system that includes a plurality of database systems with different configurations from each other, wherein each of the database systems can process data independently by using one or more processors, wherein each of the different configurations at least includes a number of database nodes and a number of access modules in each database node, the method comprising:

retrieving query information from each one the plurality of database systems of the multi-system database management system;

combining the query information into a workload that includes merged query information of the plurality of database systems;

generating, from the workload, one or more candidate indexes for each one of the plurality of the database systems; and

selecting one or more recommended indexes from the candidate indexes for each one of the plurality of database systems at least partly based on the configuration of each one of the plurality of database systems, wherein the recommended indexes comprise a first recommended index for a first copy of a given table in the first of the plurality of database systems, and a second recommended index for a second copy of the given table in the second of the plurality of database systems.

15. The method of claim 14, wherein combining the query information comprises combining workloads defined in the database systems into the workload used for generating the one or more recommended indexes.

16. The method of claim 15, wherein combining workloads defined in the database systems comprises combining the workloads identified by a particular workload identifier.

17. The method of claim 15, further comprising:

retrieving target emulation data from the plurality of database systems; and

emulating, in the computer, an environment of the plurality of database systems of the multi-system database management system,

wherein generating the one or more recommended indexes is performed in the emulated environment.

18. The method of claim 17, wherein the emulating is further based on information contained in a global dictionary that contains information from data dictionaries maintained in the corresponding database systems.

19. A non-transitory computer readable storage medium storing at least executable code for a multi-system database management system that includes a plurality of database systems with different configurations from each other, wherein each of the database systems can process data independently by using one or more processors, wherein each of the different configurations at least includes a number of database nodes and a number of access modules in each database node, and wherein the executable code that when executed:

retrieves query information from each one the plurality of database systems of the multi-system database management system;

combines the query information into a workload that includes merged query information of the plurality of database systems;

generates, from the workload, one or more candidate indexes for each one of the plurality of the database systems; and

selects one or more recommended indexes from the candidate indexes for each one of the plurality of database systems at least partly based on the configuration of each one of the plurality of database systems, wherein the recommended indexes comprise a first recommended index for a first copy of a given table in the first of the plurality of database systems, and a second recommended index for a second copy of the given table in the second of the plurality of database systems.