page178
264
The query ran in 1.7 seconds.
Checking the query profile as shown in Figure 7-11 proves search optimization was
successfully used.
Figure 7-11.
TRANSIENT table search optimization profile
Temporary Table
I would not usually attempt to enable SOS on a temporary table. This section is to prove
or disprove SOS can be enabled for a temporary table. Note the keywords
TEMP
and
VOLATILE
are synonyms for
TEMPORARY
.
Create a temporary table called
lineitem_baseline_temp
. You can also summarize
DATE
attribute
l_shipdate
to YYYYMM format and convert data the type to
VARCHAR
:
SET tpc_warehouse_XL = 'tpc_wh_xlarge';
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xl );
CREATE OR REPLACE TEMPORARY TABLE lineitem_baseline_tmp
AS
SELECT l_shipmode, l_partkey, l_comment,
TO_VARCHAR (
DATE_PART ( YEAR, l_shipdate )||
DATE_PART ( MONTH, l_shipdate )) AS l_shipdate_yyyymm
FROM snowflake_sample_data.tpch_sf1000.lineitem;
Now attempt to add search optimization to the new
TEMPORARY
table
lineitem_
baseline_tmp
.
ALTER TABLE tpc.tpc_owner.lineitem_baseline_tmp
ADD SEARCH OPTIMIZATION
ON EQUALITY ( l_shipmode ), SUBSTRING ( l_shipmode ), EQUALITY
( l_partkey );
Chapter 7
SearCh OptimizatiOn ServiCe