Partition access method for query optimization

A computer program product for determining an access method for a partition in a partition table for query optimization, the computer program product comprising:

• one or more computer readable storage media and program instructions stored on the one or more computer readable storage media, the program instructions executable by a processor, the program instructions comprising:
• program instructions to receive a query;
• program instructions to determine whether there is a partition table utilized by the query;
• responsive to determining there is a partition table utilized by the query, program instructions to determine each partition of one or more partitions in the partition table meets a plurality of conditions of the query;
• program instructions to collect a plurality of partition level statistics for each partition of one or more partitions in the partition table meeting the plurality of conditions of the query;
• program instructions to determine, based a cost for one or more access methods for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query, wherein an access method for each partition of the one or more partitions in the partition table meeting the conditions of the query is one of an index scan or a table scan;
• program instructions to determine, based on a lowest cost access method for each of the one or more access methods for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query;
• and
• program instructions to determine a lower cost access path by comparing a cost for an access path using one of the table scan or the index determined for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query to a cost for an access path for using one of a table scan or an index scan on the one or more partitions in the partition table meeting the plurality of conditions of the query.

A computer system for determining an access method for a partition in a partition table for query optimization, the computer system comprising:

• one or more computer processors;
• one or more computer readable storage media;
• and
• program instructions stored on the one or more computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising:
• program instructions to receive a query;
• program instructions to determine whether there is a partition table utilized by the query;
• responsive to determining there is a partition table utilized by the query, program instructions to determine each partition of one or more partitions in the partition table meeting a plurality of conditions of the query;
• program instructions to collect a plurality of partition level statistics for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query;
• program instructions to determine, based, at least in part, on the plurality of partition level statistics, a cost for one or more access methods for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query, wherein an access method for each partition of the one or more partitions in the partition table meeting the conditions of the query is one of an index scan or a table scan;
• program instructions to determine, based on a lowest cost access method of the one or more access methods for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query;
• and
• program instructions to determine a lower cost access path by comparing a cost for an access path using one of the table scan or the index determined for each partition of the one or more partitions in the partition table meeting the plurality of conditions of the query to a cost for an access path for using one of a table scan or an index scan on the one or more partitions in the partition table meeting the plurality of conditions of the query.