Query optimization technique for obtaining improved cardinality estimates using statistics on automatic summary tables
AssigneeInternational Business Machines Corporation
InventorDavid E. Simmen
Life of this patent
Estimating the term…
Abstract
A technique for optimizing execution of a query that accesses data stored on a data store connected to a computer. Statistics on one or more automatic summary tables are used to determine an optimal query execution plan for the query. In particular, improved cardinality estimates are generated for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query. These cardinality estimates are used to make more accurate cost estimates, thus improving the likelihood of determining the optimal query execution plan.
Claims18
A method of optimizing execution of a query that accesses data stored on a data store connected to a computer, comprising:
- generating cardinality estimates for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query, wherein the statistics of the one or more automatic summary tables are used to improve a combined selectivity estimate of one or more predicates of the query, wherein the predicates are applied by one of the automatic summary tables, and wherein the selectivity estimate comprises a ratio of a cardinality of the automatic summary table to a product of cardinalities of base tables referenced in the automatic summary table and the query;
- using the generated cardinality estimates to determine an optimal query execution plan for the query;
- and
- executing the optimal query execution plan for the query in order to access the data stored on the data store connected to a computer and then output the accessed data.
A method of optimizing execution of a query that accesses data stored on a data store connected to a computer, comprising:
- generating cardinality estimates for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query, wherein the statistics of the one or more automatic summary tables are used to improve a combined selectivity estimate of one or more predicates of the query, wherein zero or more predicates of the query are applied by one of the automatic summary tables, and wherein the remaining predicates are eligible to be applied on the automatic summary table;
- using the generated cardinality estimates to determine an optimal query execution plan for the query;
- and
- executing the optimal query execution plan for the query in order to access the data stored on the data store connected to a computer and then output the accessed data.
An apparatus for optimizing execution of a query, comprising:
- a computer having a data store coupled thereto, wherein the data store stores data one or more computer programs, performed by the computer, for:
- generating cardinality estimates for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query, wherein the statistics of the one or more automatic summary tables are used to improve a combined selectivity estimate of one or more predicates of the query, wherein the predicates are applied by one of the automatic summary tables, and wherein the selectivity estimate comprises a ratio of a cardinality of the automatic summary table to a product of cardinalities of base tables referenced in the automatic summary table and the query;
- using the generated cardinality estimates to determine an optimal query execution plan for the query;
- and
- executing the optimal query execution plan for the query in order to access the data stored on the data store connected to a computer and then output the accessed data.
An apparatus for optimizing execution of a query, comprising:
- a computer having a data store coupled thereto, wherein the data store stores data one or more computer programs, performed by the computer, for:
- generating cardinality estimates for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query, wherein the statistics of the one or more automatic summary tables are used to improve a combined selectivity estimate of one or more predicates of the query, wherein zero or more predicates of the query are applied by one of the automatic summary tables and wherein the remaining predicates are eligible to be applied on the automatic summary table;
- using the generated cardinality estimates to determine an optimal query execution plan for the query;
- and
- executing the optimal query execution plan for the query in order to access the data stored on the data store connected to a computer and then output the accessed data.
An article of manufacture comprising a non-transitory computer readable storage medium embodying one or more instructions executable by a computer to optimizing execution of a query that accesses data stored on a data store connected to the computer, comprising:
- generating cardinality estimates for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query, wherein the statistics of the one or more automatic summary tables are used to improve a combined selectivity estimate of one or more predicates of the query, wherein the predicates are applied by one of the automatic summary tables, and wherein the selectivity estimate comprises a ratio of a cardinality of the automatic summary table to a product of cardinalities of base tables referenced in the automatic summary table and the query;
- using the generated cardinality estimates to determine an optimal query execution plan for the query;
- and
- executing the optimal query execution plan for the query in order to access the data stored on the data store connected to a computer and then output the accessed data.
An article of manufacture comprising a non-transitory computer readable storage medium embodying one or more instructions executable by a computer to optimizing execution of a query that accesses data stored on a data store connected to the computer, comprising:
- generating cardinality estimates for one or more query execution plans for the query using statistics of one or more automatic summary tables that vertically overlap the query, wherein the statistics of the one or more automatic summary tables are used to improve a combined selectivity estimate of one or more predicates of the query, wherein zero or more predicates of the query are applied by one of the automatic summary tables, and wherein the remaining predicates are eligible to be applied on the automatic summary table;
- using the generated cardinality estimates to determine an optimal query execution plan for the query;
- and
- executing the optimal query execution plan for the query in order to access the data stored on the data store connected to a computer and then output the accessed data.
Dependencies are inferred from the claim text.