page164
250
Where explicitly declared, SOS performs optimally against large tables when
returning small subsets of data for highly selective query predicates. Here are some
examples:
•
Table attribute =
<value>
•
Table attribute
IN ( <value_1>, <value_2>… )
You can find more information at
https://docs.snowflake.com/en/user-guide/
search-optimization/point-lookup-queries
.
Partial attribute value and regular expressions can also benefit from SOS.
•
Table attribute
LIKE
(and variants)
•
Table attribute
REGEXP
You can find more information at
https://docs.snowflake.com/en/user-guide/
search-optimization/substring-queries
.
Semi-structured queries can also benefit from SOS. You can find more information
at
https://docs.snowflake.com/en/user-guide/search-optimization/semi-
structured-queries
.
Likewise, geospatial queries can also benefit from SOS. You can find more
information at
https://docs.snowflake.com/en/user-guide/search-optimization/
geospatial-queries
.
Having identified where SOS can benefit queries, you can now examine where SOS
cannot be used.
Excluded Use Scenarios
Not every query will benefit from search optimization; notably SOS does not support
these scenarios:
•
External tables
•
Materialized views
•
Columns defined with a
COLLATE
clause
•
Column concatenation
•
Analytical expressions
Chapter 7
SearCh OptimizatiOn ServiCe