page177
263
Now attempt to add search optimization to the new
TRANSIENT
table
lineitem_
baseline_trans
.
ALTER TABLE tpc.tpc_owner.lineitem_baseline_trans
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_trans;
Check the search optimization is enabled for the expected attributes, as shown in
Figure 7-10.
Figure 7-10.
Transient table search optimization enabled
Estimating search optimization costs for the transient table indicates SOS is enabled.
SELECT system$estimate_search_optimization_costs ( 'tpc.tpc_owner.lineitem_
baseline_trans' );
A JSON record should be returned indicating search optimization is enabled.
Let’s examine the transient table lineitem_baseline_trans.
SET tpc_warehouse_M = 'tpc_wh_medium';
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_M );
SELECT l_comment
FROM tpc.tpc_owner.lineitem_baseline_trans
WHERE l_partkey = '31587234';
Chapter 7
SearCh OptimizatiOn ServiCe