Caching database query plans that include dynamic conditions
The present disclosure involves systems, software, and computer implemented methods for managing database query plans with dynamic conditions. One example method includes receiving a request to process a query based on a dynamic condition. In response to determining that a plan cache does not include a cached query plan for the query, query plan compilation is triggered to generate a query plan. In response to determining that a cached query plan exists, for each query view, a condition provider procedure is invoked to generate a first filter string. First filter strings are compared to corresponding second filter strings in the cached plan. In response to determining that a first filter string does not match the corresponding second filter string, query plan compilation is triggered. In response to determining that each filter string matches each corresponding second filter string, the database query is executed using the cached query plan.
1. A computer-implemented method executed by at least one processor, the method comprising:
receiving, by the at least one processor, a request from a device to process a database query for at least one view based on at least one dynamic condition specified in a permission table;
determining, by the at least one processor, whether a query plan cache includes a cached query plan for the database query;
in response to determining that the query plan cache does not include a cached query plan for the database query, triggering, by the at least one processor, query plan compilation for the database query to generate a query plan for the database query; and
in response to determining that the query plan cache includes a cached query plan for the database query:
for each view of the at least one view, invoking, by the at least one processor, a condition provider database procedure executed on a computing device to generate a first filter string from the permission table;
determining, by the at least one processor, whether each first filter string matches a corresponding second filter string in the cached query plan;
in response to determining that at least one first filter string does not match the corresponding second filter string in the cached query plan, triggering, by the at least one processor, query plan compilation for the database query; and
in response to determining that each filter string matches each corresponding second filter string in the cached query plan, executing, by the at least one processor, the database query using the cached query plan without compiling the cached query plan.
2. The computer-implemented method of claim 1 , further comprising, executing the database query using the query plan after query plan compilation.
3. The computer-implemented method of claim 1 , further comprising, inserting the query plan into the query plan cache after query plan compilation.
4. The computer-implemented method of claim 1 , wherein query plan compilation comprises:
parsing the database query to generate a global query parse tree;
converting the global query parse tree to a query compiler tree; and
converting the query compiler tree to a query optimizer tree.
5. The computer-implemented method of claim 4 , wherein query plan compilation comprises traversing the query optimizer tree to collect all view nodes that have a dynamic condition.
6. The computer-implemented method of claim 5 , wherein query plan compilation comprises saving, in the query plan, dynamic condition information for collected view nodes.
7. The computer-implemented method of claim 6 , wherein saving dynamic condition information for a first collected view node comprises:
invoking the condition provider database procedure to generate a dynamic filter predicate string from the permission table;
converting the dynamic filter predicate string to a query optimizer predicate;
injecting the query optimizer predicate in the first collected view node in the query optimizer tree;
normalizing a dynamic condition of the first collected view node as a unified filter string; and
saving the unified filter string for the dynamic condition of the first collected view node in the query plan.
8. The computer-implemented method of claim 1 , wherein the condition provider database procedure generates different first filter strings for different users.
9. A system comprising:
one or more computers; and
a non-transitory computer-readable medium coupled to the one or more computers having instructions stored thereon which, when executed by the one or more computers, cause the one or more computers to perform operations comprising:
receiving a request from a device to process a database query for at least one view based on at least one dynamic condition specified in a permission table;
determining whether a query plan cache includes a cached query plan for the database query;
in response to determining that the query plan cache does not include a cached query plan for the database query, triggering query plan compilation for the database query to generate a query plan for the database query; and
in response to determining that the query plan cache includes a cached query plan for the database query:
for each view of the at least one view, invoking a condition provider database procedure executed on a computing device to generate a first filter string from the permission table;
determining whether each first filter string matches a corresponding second filter string in the cached query plan;
in response to determining that at least one first filter string does not match the corresponding second filter string in the cached query plan, triggering query plan compilation for the database query; and
in response to determining that each filter string matches each corresponding second filter string in the cached query plan, executing the database query using the cached query plan without compiling the cached query plan.
10. The system of claim 9 , further comprising, executing the database query using the query plan after query plan compilation.
11. The system of claim 9 , further comprising, inserting the query plan into the query plan cache after query plan compilation.
12. The system of claim 9 , wherein query plan compilation comprises:
parsing the database query to generate a global query parse tree;
converting the global query parse tree to a query compiler tree; and
converting the query compiler tree to a query optimizer tree.
13. The system of claim 12 , wherein query plan compilation comprises traversing the query optimizer tree to collect all view nodes that have a dynamic condition.
14. The system of claim 13 , wherein query plan compilation comprises saving, in the query plan, dynamic condition information for collected view nodes.
15. A computer program product encoded on a non-transitory storage medium, the product comprising non-transitory, computer readable instructions for causing one or more processors to perform operations comprising:
receiving a request from a device to process a database query for at least one view based on at least one dynamic condition specified in a permission table;
determining whether a query plan cache includes a cached query plan for the database query;
in response to determining that the query plan cache does not include a cached query plan for the database query, triggering query plan compilation for the database query to generate a query plan for the database query; and
in response to determining that the query plan cache includes a cached query plan for the database query:
for each view of the at least one view, invoking a condition provider database procedure executed on a computing device to generate a first filter string from the permission table;
determining whether each first filter string matches a corresponding second filter string in the cached query plan;
in response to determining that at least one first filter string does not match the corresponding second filter string in the cached query plan, triggering query plan compilation for the database query; and
in response to determining that each filter string matches each corresponding second filter string in the cached query plan, executing the database query using the cached query plan without compiling the cached query plan.
16. The computer program product of claim 15 , further comprising, executing the database query using the query plan after query plan compilation.
17. The computer program product of claim 15 , further comprising, inserting the query plan into the query plan cache after query plan compilation.
18. The computer program product of claim 15 , wherein query plan compilation comprises:
parsing the database query to generate a global query parse tree;
converting the global query parse tree to a query compiler tree; and
converting the query compiler tree to a query optimizer tree.
19. The computer program product of claim 18 , wherein query plan compilation comprises traversing the query optimizer tree to collect all view nodes that have a dynamic condition.
20. The computer program product of claim 19 , wherein query plan compilation comprises saving, in the query plan, dynamic condition information for collected view nodes.