Use of statistics on views in query optimization
First Claim
1. A computer-implemented method for performing query optimization comprising:
- developing at least one candidate plan for execution of a query;
receiving a user request to collect a view statistic of a view for at least one of said at least one candidate plan, the user request comprising a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic;
collecting the view statistic responsive to the user request and the completeness level;
for said at least one of said at least one candidate plan, using the view statistic to estimate the cost of said candidate plan; and
selecting at least one of said at least one candidate plans as a recommended execution plan,wherein said step of using the view statistic to estimate the cost of said candidate plan comprises using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, and wherein said second expression and said first expression have an equivalent cardinality; and
wherein the step of selecting the recommended execution plan comprises one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator.
2 Assignments
0 Petitions
Accused Products
Abstract
A query optimizer produces a preferred execution plan by utilizing statistics on views. These provide the optimizer with statistical information beyond that available on tables, for example, with information on the result of scalar or relational expressions. Pre-derived accurate statistics are thus available which are used to improve the accuracy of estimation and thus increase the quality and reliability of the query processor. Transformations of expressions and sub-expressions of candidate plans produce equivalent sub-expressions. Where those sub-expressions include references to views, an estimation of the cardinality and other execution statistics for the sub-expression can be made using the view statistics. This can be used to estimate the cardinality for all equivalent sub-expressions, leading to higher accuracy for the estimate.
-
Citations
24 Claims
-
1. A computer-implemented method for performing query optimization comprising:
-
developing at least one candidate plan for execution of a query; receiving a user request to collect a view statistic of a view for at least one of said at least one candidate plan, the user request comprising a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic; collecting the view statistic responsive to the user request and the completeness level; for said at least one of said at least one candidate plan, using the view statistic to estimate the cost of said candidate plan; and selecting at least one of said at least one candidate plans as a recommended execution plan, wherein said step of using the view statistic to estimate the cost of said candidate plan comprises using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, and wherein said second expression and said first expression have an equivalent cardinality; and wherein the step of selecting the recommended execution plan comprises one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator. - View Dependent Claims (2, 3, 4, 5)
-
-
6. A computer readable storage medium having a tangible component for performing query optimization having stored thereon computer readable instructions, said instructions comprising instructions for:
-
developing at least one candidate plan for execution of a query; receiving a user request to collect a view statistic of a view for at least one of said at least one candidate plan, the user request comprising a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic; collecting the view statistic responsive to the user request and the completeness level; for said at least one of said at least one candidate plan, using the view statistic to estimate the cost of said candidate plan; and selecting at least one of said at least one candidate plans as a recommended execution plan, wherein said step of using the view statistic to estimate the cost of said candidate plan comprises using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, and wherein said second expression and said first expression have an equivalent cardinality; and wherein the step of selecting the recommended execution plan comprises one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator. - View Dependent Claims (7, 8, 9, 10)
-
-
11. A system for performing query optimization having stored thereon computer readable instructions, said system comprising:
-
a processor; a view statistics storage comprising storage for at least one view statistic, each of said at least one view statistic relating to a corresponding view and collected pursuant to a user request, the user request comprising a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic; a query optimizer, operably connected to said view statistics storage, for developing at least one candidate plan for a query and using at least one of said at least one view statistic to estimate the cost of said candidate plan, wherein said query optimizer estimates the cost of said candidate plan using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, wherein said second expression and said first expression have an equivalent cardinality, and wherein said query optimizer is further adapted to select a recommended execution plan comprising one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator. - View Dependent Claims (12, 13)
-
-
14. A computer-implemented method for implementing a database comprising:
-
allowing a user to request a view of data from said database, and a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic; collecting a view statistic regarding said view responsive to the request and the completeness level; developing at least one candidate plan for execution of a query; for at least one of said at least one candidate plan, using said view statistic to estimate the cost of said candidate plan; and selecting at least one of said at least one candidate plans as a recommended execution plan, wherein said step of using the view statistic to estimate the cost of said candidate plan comprises using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, and wherein said second expression and said first expression have an equivalent cardinality; and wherein the step of selecting the recommended execution plan comprises one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator. - View Dependent Claims (15, 16, 17, 18)
-
-
19. A computer readable storage medium having a tangible component for implementing a database having stored thereon computer readable instructions, said instructions comprising instructions for:
-
allowing a user to request a view of data from said database, and a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic; collecting a view statistic regarding said view responsive to the request and the completeness level; developing at least one candidate plan for execution of a query; for at least one of said at least one candidate plan, using said view statistic to estimate the cost of said candidate plan; identifying an accuracy reading for the estimate, said accuracy reading reflecting the amount of data from which the view statistic was derived; and selecting based upon the estimate of the cost and the accuracy reading at least one of said at least one candidate plans as a recommended execution plan, wherein said step of using the view statistic to estimate the cost of said candidate plan comprises using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, and wherein said second expression and said first expression have an equivalent cardinality; and wherein the step of selecting the recommended execution plan comprises one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator. - View Dependent Claims (20, 21, 22, 23)
-
-
24. A system for implementing a database comprising:
-
a processor; a user request module for a user to request a view of data from said database, and a completeness level, the completeness level indicating a sampling of data from the view, as selected by the user, that is to be used in collecting the view statistic; a view statistic storage for collecting a view statistic regarding said view responsive to the request and the completeness level; a candidate plan developer for developing at least one candidate plan for execution of a query; an estimator, for at least one of said at least one candidate plan, using said view statistic to estimate the cost of said candidate plan; and a candidate plan selector for selecting at least one of said at least one candidate plans as a recommended execution plan, wherein said estimator estimates the cost of said candidate plan using at least one transformation rule to transform a first expression comprising all or part of said candidate plan and having an associated first expression estimate quality indicator into an equivalent second expression having an associated second expression estimate quality indicator, where said second expression comprises a reference to one or more views matching the first expression, and wherein said second expression and said first expression have an equivalent cardinality; and wherein said candidate plan selector selects the recommended execution plan having one of the first expression and the second expression having the higher of the first expression estimate quality indicator and the second expression estimate quality indicator.
-
Specification