Optimized validation of cached query plans
The subject technology receives a query, the query including a statement for performing the query. The subject technology performs a lookup operation on a stored plan cache based on the query. The subject technology performs, in response to a cache match of the query to a stored query plan in the stored plan cache based on the lookup operation, a validation process of the stored query plan. The subject technology determines whether the stored query plan is valid based on the validation process. The subject technology performs, in response to determining that the stored query plan is valid, a program building process for the stored query plan to generate a final query plan. The subject technology sends the final query plan to an execution node for execution.
1. A network-based database system comprising:
at least one hardware processor; and
a memory storing instructions that cause the at least one hardware processor to perform operations comprising:
receiving a query, the query including a statement for performing the query;
performing a lookup operation on a stored plan cache based on the query;
performing, in response to a cache match of the query to a stored query plan in the stored plan cache based on the lookup operation, a validation process of the stored query plan;
determining whether the stored query plan is valid based on the validation process;
performing, in response to determining that the stored query plan is valid, a program building process for the stored query plan to generate a final query plan; and
sending the final query plan to an execution node for execution.
2. The system of claim 1 , wherein performing the validation process of the stored query plan comprises:
validating an account version; and
validating a set of roles.
3. The system of claim 2 , wherein the operations further comprise:
determining that the account version has changed; and
in response to determining that the account version has changed, performing a compilation process on the query to generate a compiled query plan.
4. The system of claim 2 , wherein determining that the account version has changed is based on a pair of values, the pair of values comprising a first value and a second value, the first value comprising a first version and the second value comprising a second version.
5. The system of claim 4 , wherein the second value is updated for a role hierarchy change under an account.
6. The system of claim 2 , wherein the operations further comprise:
determining that the set of roles has changed; and
in response to determining that the set of roles has changed, performing a compilation process on the query to generate a compiled query plan.
7. The system of claim 2 , wherein the operations further comprise:
validating a set of parameters; and
validating a set of related objects.
8. The system of claim 7 , wherein the operations further comprise:
validating a set of expression properties; and
validating a set of security policies.
9. The system of claim 1 , wherein the operations further comprise:
in response to a cache miss of the query in the stored plan cache based on the lookup operation or determining that the stored query plan is invalid based on the validation process, performing a compilation process on the query to generate a compiled query plan;
performing a cacheability analysis process to determine whether the compiled query plan is cacheable; and
registering, in response to the compiled query plan being cacheable, the compiled query plan in the stored plan cache.
10. The system of claim 9 , wherein performing the compilation process on the query to generate the compiled query plan comprises determining a set of object types corresponding to the query, and registering the compiled query plan comprises determining that the set of object types are included in an allowed object list.
11. A method comprising:
receiving a query, the query including a statement for performing the query;
performing a lookup operation on a stored plan cache based on the query;
performing, in response to a cache match of the query to a stored query plan in the stored plan cache based on the lookup operation, a validation process of the stored query plan;
determining whether the stored query plan is valid based on the validation process;
performing, in response to determining that the stored query plan is valid, a program building process for the stored query plan to generate a final query plan; and
sending the final query plan to an execution node for execution.
12. The method of claim 11 , wherein performing the validation process of the stored query plan comprises:
validating an account version; and
validating a set of roles.
13. The method of claim 12 , further comprising:
determining that the account version has changed; and
in response to determining that the account version has changed, performing a compilation process on the query to generate a compiled query plan.
14. The method of claim 12 , wherein determining that the account version has changed is based on a pair of values, the pair of values comprising a first value and a second value, the first value comprising a first version and the second value comprising a second version.
15. The method of claim 14 , wherein the second value is updated for a role hierarchy change under an account.
16. The method of claim 12 , further comprising:
determining that the set of roles has changed; and
in response to determining that the set of roles has changed, performing a compilation process on the query to generate a compiled query plan.
17. The method of claim 12 , further comprising:
validating a set of parameters; and
validating a set of related objects.
18. The method of claim 17 , further comprising:
validating a set of expression properties; and
validating a set of security policies.
19. The method of claim 11 , further comprising:
in response to a cache miss of the query in the stored plan cache based on the lookup operation or determining that the stored query plan is invalid based on the validation process, performing a compilation process on the query to generate a compiled query plan;
performing a cacheability analysis process to determine whether the compiled query plan is cacheable; and
registering, in response to the compiled query plan being cacheable, the compiled query plan in the stored plan cache.
20. A non-transitory computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:
receiving a query, the query including a statement for performing the query;
performing a lookup operation on a stored plan cache based on the query;
performing, in response to a cache match of the query to a stored query plan in the stored plan cache based on the lookup operation, a validation process of the stored query plan;
determining whether the stored query plan is valid based on the validation process;
performing, in response to determining that the stored query plan is valid, a program building process for the stored query plan to generate a final query plan; and
sending the final query plan to an execution node for execution.