page165
251
•
Casts on table columns (except for fixed-point numbers cast to
strings)
•
Floating-point data types
•
GEOMETRY
data type
You can find more information on excluded scenarios at
https://docs.snowflake.
com/en/user-guide/search-optimization/queries-that-benefit#queries-that-do-
not-benefit-from-search-optimization
.
Search Optimization Implementation
Having identified scenarios where SOS can provide performance optimization, let’s
examine how to implement SOS.
You can start by reusing the previously created TPC environment.
SET tpc_owner_role = 'tpc_owner_role';
SET tpc_warehouse_XS = 'tpc_wh_xsmall';
SET tpc_database = 'tpc';
SET tpc_owner_schema = 'tpc.tpc_owner';
Enabling SOS requires adding the entitlement
ADD SEARCH OPTIMIZATION
to the role
tpc_owner_role
.
USE ROLE securityadmin;
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA IDENTIFIER ( $tpc_owner_schema ) TO
ROLE IDENTIFIER ( $tpc_owner_role );
Set the execution context.
USE ROLE IDENTIFIER ( $tpc_owner_role );
USE DATABASE IDENTIFIER ( $tpc_database );
USE SCHEMA IDENTIFIER ( $tpc_owner_schema );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Chapter 7
SearCh OptimizatiOn ServiCe