CACHING PLANS WITH USING DATA VALUES
A method of selecting for use a stored execution plan for a dynamic SQL query within a database system. Respective selectivity values are maintained that are associated with one or more predicates in the dynamic SQL query for respective historical data values. Respective confidence level values are maintained that are associated with one or more of the selectivity values. One or more data values are received with which to execute the dynamic SQL query. Respective selectivity values are calculated for one or more of the predicates in the dynamic SQL query for the received data value(s). The stored selectivity values are compared with respective corresponding calculated selectivity values. A stored execution plan is selected for use on detecting substantial equality between the respective pairs of compared values.
1 . A method of selecting for use a stored or previously compiled execution plan for a dynamic SQL query within a database system, the method comprising:
maintaining respective selectivity values associated with one or more predicates in the dynamic SQL query for respective historical data values;
maintaining respective confidence level values associated with one or more of the selectivity values;
receiving one or more data values with which to execute the dynamic SQL query, calculating respective selectivity values for one or more of the predicates in the dynamic SQL query for the received data value(s);
comparing the stored selectivity values with respective corresponding calculated selectivity values; and
selecting for use the stored execution plan on detecting substantial equality between the respective pairs of compared values.
2 . The method of claim 1 wherein the confidence level associated with at least one of the stored selectivity values is relatively high.
3 . The method of claim 2 wherein the stored and calculated selectivity values are numerical values in the range 0.0 to 1.0.
4 . The method of claim 3 further comprising the steps of maintaining respective selectivity tolerance values associated with one or more of the selectivity values; and
selecting for use the stored execution plan on detecting respective differences between the respective pairs of compared values that are less than the respective corresponding selectivity tolerance values.
5 . The method of claim 1 wherein the confidence level associated with at least one of the stored selectivity values is relatively low.
6 . The method of claim 5 wherein the stored and calculated selectivity values are numerical values in the range 0.0 to 1.0.
7 . The method of claim 6 further comprising the steps of:
maintaining respective selectivity tolerance values associated with one or more of the selectivity values;
maintaining a data size value associated with the dynamic SQL query for respective historical data values;
calculating a data size value for the dynamic SQL query for the received data value(s); and
selecting for use the stored execution plan on detecting respective differences between the respective pairs of compared values that are less than the respective corresponding selectivity tolerance values and on detecting substantial equality between the stored data size value and the calculated data size value.