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 8,825,629 B2
• Filed: 09/07/2004
• Issued: 09/02/2014
• Est. Priority Date: 09/06/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Lawande, Shilpa, Ziauddin, Mohamed, Smith, Gregory Scott
• Primary Examiner(s): Reyes, Mariela
• Assistant Examiner(s): Pham, Michael
• Application Number:
  US10/936,469
• Publication Number:
  US 20050187917A1
• Time in Patent Office: 3,647 Days
• Field of Search: 707 1- 10
• US Class Current: 707/713
• 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 from a workload by performing a comparison between performance information for query language statements in the workload, wherein the workload comprises query language statements;

for each statement in the set,

performing an optimization process on the statement;

determining an access structure for an advanced transformation of the statement;

during the optimization process, generating a set of candidate indexes which could be used to improve an execution plan for the statement, wherein the set of candidate indexes comprises index candidates for the access structure;

selecting a subset from the set of candidate indexes based at least in part on a determination of whether an index maintenance cost exceeds a non-zero threshold or not;

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;

determining an execution plan using a processor for the statement using the subset of indexes; and

storing the execution plan in a medium.

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

merging indexes to satisfy multiple statements.

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

4. The method of claim 2, wherein merging indexes comprises:

merging indexes based on a user defined priority.

5. The method of claim 1, further comprising:

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

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

22. the method of claim 1, wherein the index maintenance cost is based on one or more of insert frequency, update frequency, delete frequency, and bulk load frequency.

23. the method of claim 1, wherein the index maintenance cost is based on a cost model that considers how far a system that processes the set of database query language statement is from a pure Decision Support System (DSS) or a pure Online Transaction Processing System (OLTP).

7. A computer-implemented system comprising:

means for identifying a set of high load database query language statements from a workload by performing a comparison between performance information for query language statements in the workload, wherein the workload comprises query language statements;

means for performing an optimization process on a first statement in the set;

means for determining an access structure for an advanced transformation of the statement;

means for generating, during the optimization process, a set of candidate indexes which could be used to improve an execution plan for the first statement, wherein the set of candidate indexes comprises index candidates for the access structure;

means for selecting a subset from the set of candidate indexes based at least in part on a determination of whether an index maintenance cost exceeds a non-zero threshold or not;

means for identifying an index in the subset that can satisfy a second statement in the set;

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

means for determining an execution plan for the first statement using the subset of indexes, wherein the means for determining the execution plan comprises a processor.

8. The system of claim 7, wherein means for consolidating comprises:

means for merging indexes to satisfy multiple statements.

9. The system of claim 7, wherein means for performing the optimization process comprises means for applying a star transformation, and wherein means for generating the set of candidate indexes comprises means for generating candidate indexes for the star transformation.

10. The system of claim 8, wherein means for merging indexes comprises:

means for merging indexes based on a user defined priority.

11. The system of claim 7, further comprising:

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

12. The system of claim 7, wherein each of the database query language statements is a SQL statement.

24. The system of claim 7, wherein the index maintenance cost is based on one or more of insert frequency, update frequency, delete frequency, and bulk load frequency.

25. the method of claim 7, wherein the index maintenance cost is based on a cost model that considers how far a system that processes the set of database query language statement is from a pure Decision Support System (DSS) or a pure Online Transaction Processing System (OLTP).

13. A volatile or non-volatile computer readable medium storing a computer program of instructions which, when executed by a processing system, cause the system to perform a method comprising:

identifying a set of high load database query language statements from a workload by performing a comparison between performance information for query language statements in the workload, wherein the workload comprises query language statements;

for each statement in the set,

performing an optimization process on the statement;

determining an access structure for an advanced transformation of the statement;

during the optimization process, generating a set of candidate indexes which could be used to improve an execution plan for the statement, wherein the set of candidate indexes comprises index candidates for the access structure;

selecting a subset from the set of candidate indexes based at least in part on a determination of whether an index maintenance cost exceeds a non-zero threshold or not;

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.

14. The computer readable medium of claim 13, wherein consolidating comprises:

merging indexes to satisfy multiple statements.

15. The computer readable medium of claim 13, 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.

16. The computer readable medium of claim 14, wherein merging indexes comprises:

merging indexes based on a user defined priority.

17. The computer readable medium of claim 13, wherein the method further comprises:

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

18. The computer readable medium of claim 13, wherein each database query language statement is a SQL statement.

26. The computer readable medium of claim 13, wherein the index maintenance cost is based on one or more of insert frequency, update frequency, delete frequency, and bulk load frequency.

27. The computer readable medium of claim 13, wherein the index maintenance cost is based on a cost model that considers how far a system that processes the set of database query language statement is from a pure Decision Support System (DSS) or a pure Online Transaction Processing System (OLTP).

19. A method comprising:

identifying a set of high load database query language statements from a workload by performing a comparison between performance information for query language statements in the workload, wherein the workload comprises query language statements;

for each statement in the set, performing an optimization process on the statement;

determining that an access structure for an advanced transformation of the statement is beneficial for the statement during the optimization process;

during the optimization process, generating a set of candidate indexes which could be used to improve an execution plan for the statement, wherein the set of candidate indexes comprises index candidates for the access structure;

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;

determining an execution plan using a processor for the statement using the subset of indexes; and

storing the execution plan in a medium.

20. A computer-implemented system comprising:

means for identifying a set of high load database query language statements from a workload by performing a comparison between performance information for query language statements in the workload, wherein the workload comprises query language statements;

means for performing an optimization process on a first statement in the set;

means for determining that an access structure for an advanced transformation of the statement is beneficial for the statement during the optimization process;

means for generating, during the optimization process, a set of candidate indexes which could be used to improve an execution plan for the first statement, wherein the set of candidate indexes comprises index candidates for the access structure;

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

means for identifying an index in the subset that can satisfy a second statement in the set;

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

means for determining an execution plan for the first statement using the subset of indexes, wherein the means for determining the execution plan comprises a processor.

21. A volatile or non-volatile computer readable medium storing a computer program of instructions which, when executed by a processing system, cause the system to perform a method comprising:

identifying a set of high load database query language statements from a workload by performing a comparison between performance information for query language statements in the workload, wherein the workload comprises query language statement;

for each statement in the set,

performing an optimization of the statement;

determining that an access structure for an advanced transformation of the statement is beneficial for the statement during the optimization process;

during the optimization process, generating a set of candidate indexes which could be used to improve an execution plan for the statement, wherein the set of candidate indexes comprises index candidates for the access structure;

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

identifying an index in the subset 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.