Method for index tuning of a SQL statement, and index merging for a multi-statement SQL workload, using a cost-based relational query optimizer

• US 20050187917A1
• Filed: 09/07/2004
• Published: 08/25/2005
• Est. Priority Date: 09/06/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Smith, Gregory Scott, Lawande, Shilpa, Ziauddin, Mohamed
• Granted Patent: US 8,825,629 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

An access structure analysis method is interspersed with the query optimization process. The method can determine the ideal combination of access structures, including both materialized views and indexes, for a given database workload. The interspersed structure analysis method can include advanced transformations like view merging, star transformation, bitmap access plans, query rewrite using materialized views, for example. The method may be performed using the query optimizer'"'"'s rules as heuristics to guide the index candidate generation process.

Claims

1. A method comprising:

identifying a set of high load database query language statements;

for each statement in the set,

performing an optimization process on the statement;

during the optimization process, generating a set of candidate indexes which could be used to improve an execution plan for the statement;

selecting a subset from the set of candidate indexes using a model to remove expensive indexes;

identifying an index in the subset that can satisfy a different statement;

consolidating the identified index in the subset with an index for the different statement; and

determining an execution plan for the statement using the subset of indexes.

2. The method of claim 1, wherein consolidating comprises:

merging indexes to satisfy multiple statements.

3. The method of claim 1, wherein selecting the subset comprises

determining an impact of index maintenance for each candidate index;

and

removing the candidate index if the impact exceeds a threshold value.

4. The method of claim 3, wherein determining the impact comprises:

analyzing table volatility.

5. The method of claim 1, wherein performing the optimization process comprises applying a star transformation, and generating the set of candidate indexes comprises generating candidate indexes for the star transformation.

6. The method of claim 1, wherein merging indexes further comprises:

merging indexes based on a user defined priority.

7. The method of claim 1, further comprising:

for each candidate index, generating a temporary data structure with statistical metadata and no data.

8. The method of claim 1, wherein the database query language statement is a SQL statement.