Index selection in a database system

• US 7,499,907 B2
• Filed: 10/12/2001
• Issued: 03/03/2009
• Est. Priority Date: 10/12/2001
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Koppuravuri, Manjula, Brown, Douglas P., Chaware, Jeetendra
• Primary Examiner(s): WOO, ISAAC M
• Application Number:
  US09/977,038
• Publication Number:
  US 20030093408A1
• Time in Patent Office: 2,699 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2272 Management thereof
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

An index selection mechanism allows for efficient generation of index recommendations for a given workload of a database system. The workload includes a set of queries that are used to access tables in a database system. The index recommendations are validated to verify improved performance, followed by application of the indexes. Graphical user interface screens are provided to receive user input as well as to present reports to the user.

Claims

1. A system comprising:

at least one processor;

a first module executable on the at least one processor to receive a set of queries and to provide a set of candidate indexes for the set of queries, the first module adapted to eliminate one or more candidate indexes based on one or more predetermined criteria; and

an optimizer adapted to generate a recommended index from the set of candidate indexes,

wherein the one or more predetermined criteria comprises a threshold change rate, the first module adapted to eliminate one or more candidate indexes having a change rate exceeding the threshold change rate.

2. The system of claim 1, wherein the first module is adapted to further eliminate a candidate index that is a subset of another candidate index.

3. A test system comprising:

at least one processor;

an optimizer module executable on the at least one processor to receive environment information of a database system separate from the test system, the optimizer module to use the environment information to emulate an environment of the database system based on the environment information;

a first module executable in the emulated environment and adapted to receive a set of queries and to provide a set of candidate indexes for the set of queries, the first module adapted to eliminate one or more candidate indexes based on one or more predetermined criteria; and

an analysis module executable in the emulated environment and adapted to generate a recommended index from the set of candidate indexes,

wherein the analysis module is adapted to apply a genetic algorithm, and the analysis module is adapted to cooperate with the optimizer module to generate the recommended index using the genetic algorithm.

4. The test system of claim 3, wherein the set of queries comprises a set of SQL statements.

5. The test system of claim 3, wherein the analysis module is adapted to generate at least another recommended index from the set of candidate indexes.

6. The test system of claim 3, wherein the first module is adapted to provide the set of candidates indexes by identifying the candidate indexes from the set of queries and defining the set of queries in a database.

7. The test system of claim 6, wherein the analysis module is adapted to access the database to retrieve the candidate indexes.

8. The test system of claim 6, further comprising a validation module adapted to validate the recommended index in a database system.

9. The test system of claim 8 further comprising a user interface to receive user-specified one or more indexes, the optimizer adapted to generate a cost associated with a query plan based on the user-specified one or more indexes.

10. The test system of claim 9, wherein the user interface is adapted to receive a user-specified percentage value, the system further comprising another module to collect statistics based on a sample of rows of one or more tables, a size of the sample based on the user-specified percentage value.

11. The test system of claim 10, further comprising another module adapted to provide a hint on which table or tables statistics need to be collected.

12. The test system of claim 6, wherein the analysis module is adapted to access the database to retrieve the candidate indexes.

13. The test system of claim 3, wherein the analysis module is adapted to submit candidate indexes to the optimizer module, the optimizer module adapted to determine the cost of one or more of the queries based on the candidate indexes.

14. The test system of claim 13, wherein the optimizer module is adapted to select the candidate index associated with a lowest cost as the recommended index.

15. The test system of claim 3 wherein the set of queries comprises a workload captured from the database system, and wherein the database system is a parallel system having plural access modules, the environment information containing information regarding the parallel system and plural access modules.

16. The test system of claim 15, wherein the optimizer module is adapted to compute costs for the candidate indexes in the emulated environment of the database system.

17. An article comprising at least one storage medium containing instructions that when executed cause a system to:

receive a set of queries;

generate a set of candidate indexes from the set of queries;

eliminate candidate indexes based on one or more predetermined criteria;

invoke an optimizer to perform cost analysis of the candidate indexes; and

use the cost analysis to select a recommended index for a database system,

wherein eliminating candidate indexes based on one or more predetermined criteria comprises at least one of;

eliminating candidate indexes that are changed with updates at a rate greater than a predetermined change rate threshold; and

eliminating a candidate index that is a subset of another candidate index.

18. The article of claim 17, wherein the instructions when executed cause the system to apply a genetic algorithm to select the recommended index.

19. The article of claim 17, wherein the system is a test system separate from the database system, the instructions when executed causing the test system to:

import environment information regarding the database system;

emulate an environment of the database system based on the imported environment information,

wherein the generating, eliminating, invoking, and using acts are performed in the emulated environment.

20. The article of claim 19, wherein the environment information comprises cost-related information, statistics, and random samples from the database system.

21. The article of claim 17, wherein the environment information comprises cost-related information, statistics, and random samples from the database system.