Relational database query optimization to perform query evaluation plan, pruning based on the partition properties
First Claim
1. A method of processing a query in a relational database management system that operates in a computer network to retrieve data from computer storage, the method comprising:
- receiving a query which is executable in connection with partitioned data tables;
generating a plurality of interesting partition property values, each being indicative of a direct benefit for execution of an anticipated operator of the query;
after generating the interesting partition property values, generating a plurality of alternative evaluation plans for consideration in execution of all or part of the query;
generating a first set of properties, including a first partition property, associated with a first alternative evaluation plan;
generating a second set of properties, including a second partition property, associated with a second alternative evaluation plan;
comparing each property of the first set of properties with a corresponding property of the second set of properties;
comparing a first execution cost of the first alternative evaluation plan and a second execution cost of the second alternative evaluation plan;
pruning the second alternative evaluation plan from consideration in response to;
determining that the second execution cost is greater than the first execution cost; and
determining that, for each property of the second set of properties, the property is less general than or equivalent to a corresponding property of the first set of properties, including;
wherein determining that the second partition property is less general than or equivalent to the first partition property is in response to;
(1) identifying that the second partition property does not have any one of the interesting partition property values, and (2) identifying that the first partition property has one of the interesting partition property values, that the second partition property does not have any one of the interesting partition property values, and that the first and the second partition properties have values that are equivalent, andwherein determining that the second partition property is not less general than or equivalent to the first partition property is in response to;
(1) identifying that the first partition property does not have any one of the interesting partition property values and that the second partition property has one of the interesting partition property values, and (2) identifying that the first partition property has one of the interesting partition property values, that the second partition property does not have any one of the interesting partition property values, and that the first and the second partition properties have values that are not equivalent.
1 Assignment
0 Petitions
Accused Products
Abstract
A relational data base management system includes a query processor that uses a query operator partition property to perform QEP pruning and to ensure that data input to a query operator is partitioned appropriately for the operation. The partition property indicates the group of network nodes across which a table is distributed. The query processor also makes use of partition classes that are designated "interesting classes" to perform preoptimization planning and query pruning, and to perform look-ahead partitioning based on partition classes that are identified as being of interest to future operations, thereby more efficiently evaluating complex query statements in an MPP, shared-nothing environment.
319 Citations
2 Claims
-
1. A method of processing a query in a relational database management system that operates in a computer network to retrieve data from computer storage, the method comprising:
-
receiving a query which is executable in connection with partitioned data tables; generating a plurality of interesting partition property values, each being indicative of a direct benefit for execution of an anticipated operator of the query; after generating the interesting partition property values, generating a plurality of alternative evaluation plans for consideration in execution of all or part of the query; generating a first set of properties, including a first partition property, associated with a first alternative evaluation plan; generating a second set of properties, including a second partition property, associated with a second alternative evaluation plan; comparing each property of the first set of properties with a corresponding property of the second set of properties; comparing a first execution cost of the first alternative evaluation plan and a second execution cost of the second alternative evaluation plan; pruning the second alternative evaluation plan from consideration in response to; determining that the second execution cost is greater than the first execution cost; and determining that, for each property of the second set of properties, the property is less general than or equivalent to a corresponding property of the first set of properties, including; wherein determining that the second partition property is less general than or equivalent to the first partition property is in response to;
(1) identifying that the second partition property does not have any one of the interesting partition property values, and (2) identifying that the first partition property has one of the interesting partition property values, that the second partition property does not have any one of the interesting partition property values, and that the first and the second partition properties have values that are equivalent, andwherein determining that the second partition property is not less general than or equivalent to the first partition property is in response to;
(1) identifying that the first partition property does not have any one of the interesting partition property values and that the second partition property has one of the interesting partition property values, and (2) identifying that the first partition property has one of the interesting partition property values, that the second partition property does not have any one of the interesting partition property values, and that the first and the second partition properties have values that are not equivalent.
-
-
2. A relational database management system comprising:
-
a central processing unit of a computer network; and a query processor that operates in the computer network to evaluate queries and retrieve data from computer storage, wherein the query processor is operative to perform query evaluation by; receiving a query which is executable in connection with partitioned data tables; generating a plurality of interesting partition property values, each being indicative of a direct benefit for execution of an anticipated operator of the query; after generating the interesting partition property values, generating a plurality of alternative evaluation plans for consideration in execution of all or part of the query; generating a first set of properties, including a first partition property, associated with a first alternative evaluation plan; generating a second set of properties, including a second partition property, associated with a second alternative evaluation plan; comparing each property of the first set of properties with a corresponding property of the second set of properties; comparing a first execution cost of the first alternative evaluation plan and a second execution cost of the second alternative evaluation plan; pruning the second alternative evaluation plan from consideration in response to; determining that the second execution cost is greater than the first execution cost; and determining that, for each property of the second set of properties, the property is less general than or equivalent to a corresponding property of the first set of properties, including; wherein determining that the second partition property is less general than or equivalent to the first partition property is in response to;
(1) identifying that the second partition property does not have any one of the interesting partition property values, and (2) identifying that the first partition property has one of the interesting partition property values, that the second partition property does not have any one of the interesting partition property values, and that the first and the second partition properties have values that are equivalent, andwherein determining that the second partition property is not less general than or equivalent to the first partition property is in response to;
(1) identifying that the first partition property does not have any one of the interesting partition property values and that the second partition property has one of the interesting partition property values, and (2) identifying that the first partition property has one of the interesting partition property values, that the second partition property does not have any one of the interesting partition property values, and that the first and the second partition properties have values that are not equivalent.
-
Specification