page172
258
Standard Table
Creating
lineitem_baseline_std
with a subset of attributes ensures you have a
consistent starting point for later query profile comparison. I 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 );
Re-creating
lineitem_baseline_std
will take a few minutes:
CREATE OR REPLACE TABLE lineitem_baseline_std
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;
To ensure consistency when checking Search Optimization usage, we now create
EQUALITY
and
SUBSTRING
search access paths for
l_shipmode
:
ALTER TABLE tpc.tpc_owner.lineitem_baseline_std
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.lineitem_baseline_std;
Check search optimization is enabled for the expected attributes, as shown in
Figure 7-6.
Chapter 7
SearCh OptimizatiOn ServiCe