Method for determining optimal database materializations using a query optimizer

Claim 3

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.

Claim 4

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

Claim 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 an index which becomes a candidate for the query execution plans.

Claim 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 a join index which becomes a candidate for the query execution plans.

Claim 7

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.

Claim 8

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.

Claim 9

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.

Claim 10

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.

Claim 11

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

Claim 12

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.

Claim 13

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.

Claim 14

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 .