Snowflake Inc.

Nicola Dan Onose

Thierry Cruanes

Jiaqi Yan

Subramanian Muralidhar

Damien Carru

Ryan Michael Thomas Shelly

Brian Smith

Jaeyun Noh

Boyung Lee

Xuelai Cui

Allison Waingold Lee

William H Waddington

Yongsik Yoon

Sangyong Hwang

Li Yan

Prashant Gaharwar

Vineet Garg

Shrainik Jain

Bicheng Gao

Kevin Ye Jie Li

Andrew Xue

Karan Chadha

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.

Optimized validation of cached query plans

The subject technology receives a query. The subject technology performs a lookup operation on a shadow cache. The subject technology performs a validation process on the particular query plan for determining a cache hit on the shadow cache. The subject technology performs a program building process to update the particular query plan with additional information for executing the particular query plan. The subject technology performs a compilation process to compile an updated program of the updated particular query plan. The subject technology determines that the updated particular query plan is cacheable based at least in part on the compilation process. The subject technology registers the updated particular query plan as a new entry in the shadow cache. The subject technology sends the updated program to an execution node for executing the updated particular query plan.

Query plan cache shadow modes

Techniques described herein can allow users to share cached results of an original query with other users while protecting sensitive information. The techniques described herein can check whether the other users have access to the underlying data queried before allowing those users to see the stored query results. That is, the system may perform privilege checks on the shared users before giving them access to the stored query results but without having to re-run the original query.

Privilege based access checks for query results

A source table can be provided by a provider account in a data system. A secure view of the source table is provided to one or more consumer accounts, the secure view limiting access to a subset of data in the source table. A plan to execute a command using the secure view may be generated, the plan including a secure view boundary on a subset of operations defining the secure view. The plan may be modified to move a first operation that was outside the secure view boundary to within the secure view boundary to generate a second plan to optimize performance in view of limitations or restrictions placed by the secure view.

Performance optimizations for secure objects evaluations

The subject technology receives a query, the query including a statement for performing the query. The subject technology performs a first lookup operation on a multi-phase cache based on the query. The subject technology performs, in response to a first cache miss of the multi-phase cache, parsing of the statement from the query. The subject technology performs, based on the parsing, a compilation process on the query to generate a compiled query plan, the compilation process determining an optimization and a generalization for the query. The subject technology determines that the compiled query plan is cacheable. The subject technology registers, in response to the compiled query plan being cacheable, a dummy entry in the multi-phase cache.

Multi-phase query plan caching

The subject technology receives a query, the query including a set of statements for performing the query. The subject technology populates a compilation context based at least in part the query. The subject technology invokes a compiler to perform a compilation process based on the compilation context. The subject technology performs a lookup operation on a stored plan cache for an exact match based on information from the compilation context. The subject technology, in response to determining an exact match, determines whether the particular query plan requires re-compilation based on a data dependent optimization. The subject technology determines whether a plan cache entry corresponding to the particular query plan includes a data property constraint. The subject technology determines whether the data property constraint still holds based on a set of data properties.

Query plan cache in database systems

Caching query plans in database systems

The subject technology receives a query, the query including a set of statements for performing the query. The subject technology populates a compilation context based at least in part the query. The subject technology provides the compilation context to a compiler. The subject technology invokes the compiler to perform a compilation process based on the compilation context, the compilation process comprising performing a lookup operation on a stored plan cache for an exact match based on information from the compilation context, the stored plan cache including a set of stored query plans, and determining whether the exact match of a particular query plan is found in the stored plan cache to avoid compiling the query using the compilation context.

Query plan caching for networked database systems

Growing community of inventors

San Jose, CA, United States of America

Nicola Dan Onose