Generating materialized query table candidates

A computer program product comprising a tangible computer readable recordable storage medium including computer useable program code for generating a set of one or more materialized query table (MQT) candidates for a workload, the computer program product including:

• computer useable program code for receiving a workload, wherein the workload comprises a set of one or more queries;
• computer useable program code for generating one or more best matching MQTs (BMQTs) based on one or more query blocks of the one or more queries by removing syntax that is not qualified for a MQT re-write;
• computer useable program code for determining one or more frequently used multi joins in the workload, wherein said multi-join is a group of tables, wherein any one of the tables in said group of tables is joined to at least one other table within the group of tables, and wherein any table in the group of tables can be accessed from any other table in the group of tables by traversing a path that contains one or more tables in the group and one or more joins that link the tables in the group of tables;
• computer useable program code for using the one or more BMQTs and the one or more frequently used multi-joins to generate a set of one or more workload MQTs (WMQTs);
• and
• computer useable program code for grouping one or more, WMQTs and one or more BMQTs into one or more groups to merge into a set of a smaller number of MQTs and to cover the workload.

A system for generating a set of one or more materialized query table (MQT) candidates for a workload, comprising:

• a memory;
• and
• at least one processor coupled to the memory and operative to:
• receive a workload, wherein the workload comprises a set of one or more queries;
• generate one or more best matching MQTs (BMQTs) based on one or more query blocks of the one or more queries by removing syntax that is not qualified for a MQT re-write;
• determine one or more frequently used multi joins in the workload, wherein said multi join is a group of tables, wherein any one of the tables in said group of tables is joined to at least one other table within the group of tables, and wherein any table in the group of tables can be accessed from any other table in the group of tables by traversing a path that contains one or more tables in the group and one or more joins that link the tables in the group of tables;
• use the one or more BMQTs and the one or more frequently used multi-joins to generate a set of one or more workload MQTs (WMQTs);
• and
• group one or more WMQTs and one or more BMQTs into one or more groups to merge into a set of a smaller number of MQTs and to cover the workload.