Index and materialized view selection for a given workload

A system for selecting candidate materialized views for a database workload, the system comprising:

• a module that generates a set of interesting table-subsets for the workload;
• a pruning module that provides a cost based selection of materialized views from the list of interesting table-subsets;
• a merging module that merges selected materialized views;
• and
• a module that combines the cost based selection of materialized views with the merged selected materialized views to provide the set of candidate materialized views.

A computer readable medium having instructions stored thereon for causing a computer to implement a method of selecting materialized views and indexes for a database workload, the method comprising:

• obtaining a set of candidate materialized views for the workload;
• obtaining a set of candidate indexes for the workload;
• and
• enumerating over the set of materialized views and indexes in the combined candidate sets to provide a recommended set of materialized views and indexes.

A method of determining a set of syntactically relevant materialized views for a specified workload of queries for a given database, the method comprising:

• determining subsets of tables that are referenced by the queries;
• eliminating uninteresting subsets based on cost;
• ranking remaining subsets based on relative importance of the subsets in reducing the total cost of the workload to provide a set of interesting table subsets;
• and
• determining on a query by query basis which materialized views are proposed for each query based on the ranked table subsets to form the set of syntactically relevant materialized views.

A computer readable medium having instructions stored thereon for causing a computer to implement a method of proposing a set of potential materialized views for use in a database server for a given workload, the method comprising:

• generating a list of syntactically relevant materialized views for each query in the workload;
• pruning the relevant materialized views to provide a set of materialized views which are likely to be used for the workload;
• and
• merging selected materialized views and adding them to the materialized views which are likely to be used for the workload to form the subset.

A system for proposing a set of candidate materialized views for use in a database server for a given workload, the system comprising:

• a module that generates a list of syntactically relevant materialized views for each query in the workload;
• a pruning module that prunes uninteresting materialized views to provide materialized views likely to be used for the workload;
• and
• a merging module that merges selected materialized views and adds them to the materialized views likely to be used to form the set of candidate materialized views.

A method of merging materialized views to provide a set of merged materialized views, the method comprising:

• merging pairs of materialized views that have the same tables, join and selection conditions and differing in projection or group by columns;
• and
• merging pairs of materialized views that have different selection conditions, but matching Group By and join conditions;
• and
• adding the merged pairs to a set of candidate materialized views for a specified workload on a database.

A computer readable medium having instructions stored thereon to cause a computer to perform a method of merging materialized views to provide a set of merged materialized views, the method comprising:

• merging pairs of materialized views that have the same tables, join and selection conditions and differing in projection or group by columns;
• and
• merging pairs of materialized views that have the same Group By and join conditions, but differ in selection conditions;
• and
• adding the merged pairs to a set of candidate materialized views for a specified workload on a database.

A method of proposing a set of potential materialized views for use in a database server for a given workload, the method comprising:

• eliminating low cost table-subsets from a set of syntactically relevant table-subsets for the workload;
• ranking remaining table-subsets using a different metric than that used to eliminate low cost table-subsets;
• selecting a set of interesting table-subsets from the ranked remaining table-subsets based on a selected threshold;
• identifying materialized views covering the set of interesting table-subsets that are part of a best solution for each query;
• and
• merging selected parent materialized views and adding them to the set of interesting table-subsets that are part of the best solution for each query to form the set of potential materialized views.

A method of proposing a recommended set of materialized views and indexes for use in a database server for a given workload, the method comprising:

• eliminating low cost table-subsets from a set of syntactically relevant table-subsets for the workload;
• ranking remaining table-subsets using a different metric than that used to eliminate low cost table-subsets;
• selecting a set of interesting table-subsets from the ranked remaining table-subsets based on a selected threshold;
• identifying materialized views covering the set of interesting table-subsets that are part of a best solution for each query to form a set of parent materialized views;
• merging selected parent materialized views and adding them to the set of parent materialized views to form a set of candidate materialized views;
• receiving a set of candidate indexes;
• and
• enumerating over the sets of candidate indexes and materialized views together to form the set of recommended materialized views and indexes.

A computer readable medium having instructions stored thereon for causing a computer to implement a method of selecting materialized views and indexes for a database workload, the method comprising:

• generating a set of syntactically relevant materialized views for the workload;
• proposing materialized views which are likely to be used for at least one query in the workload;
• merging selected materialized views which are likely to be used;
• obtaining a set of candidate indexes for the workload;
• combining materialized views which are likely to be used with merged materialized views and indexes into candidate sets of materialized views and indexes;
• and
• enumerating over the candidate materialized views and indexes to provide a recommended set of materialized views and indexes.