INDEX SELECTION IN A MULTI-SYSTEM DATABASE MANAGEMENT SYSTEM

• US 20130254210A1
• Filed: 05/24/2013
• Published: 09/26/2013
• Est. Priority Date: 12/30/2008
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradata Corporation
• Inventors: Morris, John Mark, Brown, Douglas P.
• Granted Patent: US 9,418,092 B2
• Time in Patent Office: Days
• US Class Current: 707/741
• 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; 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;

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.

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 recommended indexes comprise a first recommended index for a first of the plurality of database systems, and a second recommended index for a second of the plurality of database systems.

5. The system of claim 4, wherein the first recommended index is for a first copy of a given table in the first database system, and the second recommended index is for a second copy of the given table in the second database system.

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

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

8. The system of claim 7,

wherein each of the different configurations specifies one or more of the following:

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

a 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.

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

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

11. 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.

12. 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.

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

13. The system of claim 13, 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.

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

16. 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, 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;

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.

17. The method of claim 16, 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.

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

19. The method of claim 17, 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.

20. The method of claim 19, 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.