Method for determining optimal database materializations using a query optimizer

• US 6,356,889 B1
• Filed: 09/30/1999
• Issued: 03/12/2002
• Est. Priority Date: 09/30/1998
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lohman, Guy Maring, Valentin, Gary
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/409,323
• Time in Patent Office: 894 Days
• Field of Search: 707/2, 707/3, 707/4, 707/9, 707/10, 707/100, 707/102, 707/103.R, 707/104.1, 707/200-204, 709/104, 709/106
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method for determining optimal database materializations utilizing a query optimizer in a database management system. The method takes one or more queries as inputs and using the query optimizer in the database management system generates a series of virtual materializations by materializing some subsets of the database. The virtual materializations are used to consider the relative performance benefits, i.e. cost-benefits, for the queries based on the various virtual materializations. If the query optimizer decides to use any of the materializations in its plan, then those materializations are recommended to the user, or created automatically for the user.

Claims

1. A method for determining optimal materializations for a query optimizer in a database management system, wherein the query optimizer generates one or more query execution plans in response to a query input from a user for accessing data in a database schema in the database management system, said method comprising the steps of:

(a) generating a plurality of temporary materializations as candidates for the query execution plans associated with the query;

(b) computing estimated statistics for selected performance parameters for each of said temporary materializations;

(c) utilizing the query optimizer to optimize each of the query execution plans;

(d) determining if any of said temporary materializations are being utilized in any of the query execution plans;

(e) if any of said temporary materializations are being utilized in any of the query execution plans, recommending said temporary materializations to the user together with the associated query execution plans.

2. The method as claimed in claim 1, further including the step of deleting said temporary materializations not being utilized in any of the query execution plans.

3. The method as claimed in claim 1, further including the step of converting said recommended temporary materializations into actual materializations.

4. The method as claimed in claim 1, wherein said step of generating a plurality of temporary materializations for the query comprises generating a temporary materialization for an index which becomes a candidate for the query execution plans.

5. The method as claimed in claim 1, wherein said step of generating a plurality of temporary materializations for the query comprises generating a temporary materialization for a join index which becomes a candidate for the query execution plans.

6. The method as claimed in claim 1, wherein said step of generating a plurality of temporary materializations for the query comprises generating a temporary materialization for columns and orders of interest which becomes a candidate for the query execution plans.

11. Data storage media recorded with a computer program which, in combination with a general purpose computer configured for a database management system and equipped to read into memory and execute program data from the data storage media, constituting a method in accordance with claim 1.

7. A relational database management system for use with a computer system wherein queries are entered by a user for retrieving data from a database schema, the relational database management system including a query optimizer for optimizing query execution plans associated with the queries entered by the user, said relational database management system comprising:

(a) means for processing queries;

(b) means for generating a plurality of temporary materializations as candidates for the query execution plans associated with the query;

(c) means for computing estimated statistics for selected performance parameters for each of said temporary materializations;

(d) the query optimizer including means for optimizing each of the query execution plans and means for selecting query execution plans on the basis of selected performance parameters;

(e) means for determining if any of said temporary materializations are being utilized in any of the query execution plans;

(f) means for recommending said temporary materializations to the user together with the associated query execution plans selected by the query optimizer if any of said temporary materializations are being utilized in any of the query execution plans.

8. The relational database management system as claimed in claim 7, further including means for deleting temporary materializations not being utilized in any of the query execution plans.

9. The relational database management system as claimed in claim 8, including means for converting said recommended temporary materializations into actual materializations.

10. A computer program product for use on a computer wherein queries are entered by a user for retrieving data from a relational database management system having a query optimizer for optimizing query execution plans associated with the queries entered by the user, said computer program product comprising:

a recording medium;

means recorded on said medium for instructing said computer to perform the steps of,

(a) generating, a plurality of temporary materializations as candidates for the query execution plans associated with the query;

(b) computing estimated statistics for selected performance parameters for each of said temporary materializations;

(c) utilizing the query optimizer to optimize each of the query execution plans;

(d) determining if any of said temporary materializations are being utilized in any of the query execution plans;

(e) if any of said temporary materializations are being utilized in any of the query execution plans, recommending the temporary materializations to the user together with the associated query execution plans.