Method and apparatus for automatic recommendation and selection of clustering indexes

A method in a data processing system, which comprises a processor and memory, for selecting clustering indexes, the method comprising:

• determining a plurality of baseline run time costs for executing workload queries for a database design;
• simulating multidimensional clustering and record identifier based index clustering to determine clustering workload benefits based on a plurality of clustering designs, wherein the simulations comprises simulations a clustering dimension for each range predicate in a query workload using a multidimensional clustering block index;
• comparing the clustering workload benefits to the plurality of baseline run time costs;
• if the clustering workload benefits are greater than one of the plurality of baseline run time costs, identifying a plurality of clustering dimension solutions originating from a record identifier based index, wherein identifying step comprises determining a multidimensional clustering block index benefit and a record identifier based index benefit associated with each clustering dimension solution, wherein the multidimensional clustering block index benefit indicates an estimated benefit for data access using the multidimensional clustering block index and wherein the record identifier based index benefit indicates an estimated benefit for data access using the record identifier based clustering index;
• responsive to a selection of a clustering design based on the plurality of clustering dimension solutions, identifying a number of recommended clustering dimensions for a database table;
• if more than one recommended clustering dimension is identified, selecting multidimensional clustering block index;
• if only one recommended clustering dimension is identified as a recommended clustering dimension, determining whether a majority of the clustering workload benefits is obtained from one of a record identifier based index and a multidimensional clustering block index;
• if a majority of the clustering workload benefits is obtained from the record identifier based index, selecting the record identifier based index as a clustering index for the recommended clustering dimension;
• and
• if a majority of the clustering workload benefits is obtained from the multidimensional block index, recommending multidimensional clustering for the database table.