Method and apparatus for optimizing access path in database

摘要

Apparatuses and methods for optimizing access path are provided. Execution of a query statement is sampled to obtain the information about a plurality of executing instances, the information about each executing instance comprises the literal value of the query statement in the instance and the filter factor corresponding to the literal value. The plurality of instances are grouped into a plurality of groups based on the filter factors corresponding to the literal values. The average filter factor of each group and the occurrence frequency of the group are obtained. The candidate access path created for the average filter factor of each group is accordingly obtained, and by taking the occurrence frequencies as weights, the weighted execution cost of each candidate access path is calculated for each group. The candidate access path with the minimum sum of the weighted execution costs for all groups is determined as the optimal access path.

主权项

1. A method for optimizing an access path in a database, comprising:
sampling execution of a query statement to obtain information about a plurality of executing instances, wherein the information about each executing instance comprises a literal value of the query statement in the executing instance and a filter factor corresponding to the literal value; grouping the plurality of executing instances into a plurality of groups based on the filter factors corresponding to the literal values, and obtaining an average filter factor of each group and an occurrence frequency of the group; obtaining a candidate access path created for the average filter factor of each group; and by taking the occurrence frequencies as weights, calculating a weighted execution cost of each candidate access path for each group, and determining the candidate access path with a minimum sum of the weighted execution costs for all groups as an optimal access path, wherein said grouping the plurality of executing instances into a plurality of groups based on the filter factors corresponding to the literal values and obtaining the average filter factor of each group and the occurrence frequency of the group comprises: classifying the plurality of obtained executing instances into a plurality of classes based on the literal values, and obtaining the filter factor and the occurrence frequency of each class; and clustering the plurality of classes into a plurality of groups based on the filter factor of each class in the plurality of classes, and obtaining the average filter factor and the occurrence frequency of each group based on the filter factor and the occurrence frequency of every class, and wherein said clustering the plurality of classes into a plurality of groups based on the filter factor of every class comprises one of the following steps: clustering a portion of the plurality of classes for which distances among the filter factors are within a predetermined threshold into a same group; and dividing values of the filter factors into a plurality of intervals, and clustering the portion of the plurality of classes whose filter factors belong to a same interval into the same group.