Feedback mechanism providing row-level filtering earlier in a plan
Methods, systems, and computer readable media of executing a database query are provided. One or more predicates are generated based on implicit filtering present within the database query, wherein each predicate specifies a condition with respect to a predicate value. An access path is selected for the database query based on the one or more predicates and the one or more predicates are integrated within the selected access plan. The database query is executed in accordance with the selected access plan. At least one predicate value based on data accessed is updated, as the database query is being executed, to increase filtering of data during remaining execution of the database query.
1. A system for executing a database query comprising:
a processor configured to:
generate one or more predicates based on implicit filtering present within the database query, wherein each of the one or more predicates specifies a condition with respect to a respective predicate value;
select an access path for the database query based on the one or more predicates and integrating the one or more predicates within the selected access plan;
execute a set number of operations of the database query in accordance with the selected access plan;
repeatedly update the respective predicate value of at least one predicate of the one or more predicates based on data accessed, wherein the updating increases filtering of data, and execute the database query until the filtering of the data cannot be improved by an updated predicate value.
2. The system of claim 1 , wherein the filtering present within the database query includes one or more from the group consisting of FETCH FIRST or FETCH FIRST with one or more of ORDER BY, MIN, MAX, DISTINCT, GROUP BY, and UNION.
3. The system of claim 1 , wherein the condition for a predicate is specified by an operator and the respective predicate value.
4. The system of claim 3 , wherein the processor is configured to update the predicate value of at least one predicate with an accessed data value based on accessed data satisfying the condition of the at least one predicate.
5. The system of claim 1 , wherein the processor is configured to select an access path for the database query by selecting the access path with minimum materializations.
6. The system of claim 1 , wherein the processor is further configured to:
reorder an order of applying join operations prior to selecting the access path for the database query.
7. The system of claim 1 , wherein the processor is further configured to:
select the access path based upon a cost function that determines an optimal time during query execution to apply the generated one or more predicates.
8. A computer program product for executing a database query, comprising a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code, when executed by a processor, causes the processor to:
generate one or more predicates based on implicit filtering present within the database query, wherein each of the one or more predicates specifies a condition with respect to a respective predicate value;
select an access path for the database query based on the one or more predicates and integrating the one or more predicates within the selected access plan;
execute a set number of operations of the database query in accordance with the selected access plan;
repeatedly update the respective predicate value of at least one predicate of the one or more predicates based on data accessed, wherein the updating increases filtering of data, and execute the database query until the filtering of the data cannot be improved by an updated predicate value.
9. The computer program product of claim 8 , wherein the filtering present within the database query includes one or more from the group consisting of FETCH FIRST or FETCH FIRST with one or more of ORDER BY, MIN, MAX, DISTINCT, GROUP BY, and UNION.
10. The computer program product of claim 8 , wherein the condition for a predicate is specified by an operator and the respective predicate value.
11. The computer program product of claim 8 , wherein the computer readable code is configured to cause the processor to select an access path for the database query by selecting the access path with minimum materializations.
12. The computer program product of claim 8 , wherein the computer readable code is configured to cause the processor to reorder an order of applying join operations prior to selecting the access path for the database query.
13. The computer program product of claim 8 , wherein the computer readable code is configured to cause the processor to select the access path based upon a cost function that determines an optimal time during query execution to apply the generated one or more predicates.