page171
257
Now examine active search optimization.
DESCRIBE SEARCH OPTIMIZATION ON tpc.tpc_owner.lineitem_baseline;
Confirm search optimization is enabled for both methods, as shown in Figure 7-5.
Figure 7-5.
Search optimization methods enabled
You can leave the
GEOGRAPHY
data type for your further investigation.
You can find more information on search optimization at
https://docs.
snowflake.com/en/sql-reference/sql/alter-table#label-alter-table-
searchoptimizationaction
.
Table Type Support
In this section I will cover the different types of tables supported by SOS. We build each
table from the same source table,
snowflake_sample_data.tpch_sf1000.lineitem
, and
then apply the same search optimization criteria before testing the outcome. You then
select an arbitrary high-cardinality value for
l_partkey
used for every query returning
27 rows.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
SELECT l_partkey,
count(*)
FROM tpc.tpc_owner.lineitem_baseline
GROUP BY l_partkey
HAVING count (*) > 2
LIMIT 10;
The steps are identical for each table type; let’s now investigate how SOS works with
each table type.
Chapter 7
SearCh OptimizatiOn ServiCe