page179
265
You cannot add search optimization to temporary tables and should see the
following error:
Error: Invalid materialized view definition. Source table 'TPC.TPC_
OWNER.LINEITEM_BASELINE_TMP'
should
not
be
temporary.
(line nn)
You cannot enable SOS for temporary tables.
Conclusion
Search optimization cannot be enabled for all table types, as shown in Figure 7-12.
Figure 7-12.
Search optimization table coverage
Rerunning the standard table query after our dynamic table had been created did
not cause the dynamic table to be used. Search optimization was preferred.
SELECT l_comment
FROM tpc.tpc_owner.lineitem_baseline_std
WHERE l_partkey = '31587234';
Disabling Table Search Optimization
You can disable SOS by doing the following:
ALTER TABLE tpc.tpc_owner.lineitem_baseline_std DROP SEARCH OPTIMIZATION;
Note SOS will report StorageCosts after SOS is disabled; this is consistent with
Time Travel and Fail Safe behavior where you would expect search optimization to be
preserved in a consistent manner with object content.
SELECT system$estimate_search_optimization_costs ( 'tpc.tpc_owner.lineitem_
baseline_std' );
Chapter 7
SearCh OptimizatiOn ServiCe