page174
260
Dynamic Table
Create a dynamic table called
dt_lineitem_baseline_sos
; note that this will take some
time to complete, in my test environment about one hour and 45 minutes. We 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 DYNAMIC TABLE dt_lineitem_baseline_sos
TARGET_LAG = '30 MINUTES'
WAREHOUSE = tpc_wh_xsmall
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 resume the dynamic table.
ALTER DYNAMIC TABLE dt_lineitem_baseline_sos RESUME;
Then refresh the dynamic table.
ALTER DYNAMIC TABLE dt_lineitem_baseline_sos REFRESH;
Set search optimization on the desired attributes.
ALTER TABLE tpc.tpc_owner.dt_lineitem_baseline_sos
ADD SEARCH OPTIMIZATION
ON EQUALITY ( l_shipmode ), SUBSTRING ( l_shipmode ), EQUALITY
( l_partkey );
Now examine active search optimization.
DESCRIBE SEARCH OPTIMIZATION
ON tpc.tpc_owner.dt_lineitem_baseline_sos;
Chapter 7
SearCh OptimizatiOn ServiCe