page176
262
Checking the query profile did not show search optimization access but instead a
table scan, as shown in Figure 7-9.
Figure 7-9.
Dynamic table search optimization profile
You can conclude that search optimization at the time of writing is not fully
implemented; the ability to enable SOS indicates it’s a work in progress.
Transient Table
I would not usually enable SOS on a transient table though your use case may require
SOS enabled for specific transient tables. This section is to prove or disprove that SOS
can be enabled for a transient table.
Create a transient table called
lineitem_baseline_trans
. You also summarize
DATE
attribute
l_shipdate
to YYYYMM format and convert the data type to
VARCHAR
.
SET tpc_warehouse_XL = 'tpc_wh_xlarge';
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xl );
CREATE OR REPLACE TRANSIENT TABLE lineitem_baseline_trans
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;
Chapter 7
SearCh OptimizatiOn ServiCe