page317
405
Materialized views should not be used where the contents are largely similar to
the parent table. Materialized views should not be used for simple queries, i.e., those
without aggregates or filters. High-velocity, low-volume DML operations may overwhelm
the materialized view service capability to maintain materialized views before the next
batch of changes arrive.
Maintaining materialized views incurs both compute cost and additional
storage costs.
Search Optimization
Search optimization prebuilds optimized data structures called search access paths
predicated upon high-cardinality attribute values spread across many micro-partitions.
Search optimization prefers accessing very small subsets of data via equality predicates
mapped via “search access paths.” Snowflake recommends usage for tables of 100GB or
larger; costs will be prohibitive for tables of less than 10GB. Search optimization may
be implemented against tables with clustering keys where predicates do not match the
clustering key or for unclustered tables.
Search optimization should not be used for accessing large sets of filtered data
or for “search access paths” built on low-cardinality data sets. Inequality predicate
matches are not suitable for search optimization. You can find more information on
supported predicate types at
https://docs.snowflake.com/en/user-guide/search-
optimization/queries-that-benefit#supported-predicate-types.
High-velocity, low-volume DML operations may overwhelm the search optimization
service capability to maintain “search access paths” before the next batch of
changes arrive.
Maintaining search optimization incurs both compute cost and additional
storage costs.
Refer to Chapter 7 for a detailed investigation of search optimization.
Query Acceleration
Query acceleration adds processing units to an existing warehouse as demand increases
without increasing size. This is useful where the workload does not justify spinning up
additional warehouse clusters, but the occasional availability of extra processing units
would prevent queueing. The query optimizer may make use of extra processing units to
ChapTer 10
OpTimizing perfOrmanCe