page170
256
For reporting purposes you can also extract the “method” and “target”
programmatically.
SELECT "method",
"target"
FROM TABLE ( RESULT_SCAN ( last_query_id()))
WHERE "active" = 'true';
A drawback to implementing table search optimization is cost. Maintaining all
attributes on high-velocity, low-volume DML environments results in frequent SOS
invocation. We should only consider enabling search optimization for those attributes or
partial attributes used within query predicates, discussed next.
Enabling Attribute Search Optimization
Having explained how to enable search optimization for a table, you can now investigate
how to set up search optimization for both individual attributes and JSON fields within
a
VARIANT
data type for a table. Most data types are supported, though there are some
notable exceptions listed earlier in “Excluded Scenarios.”
Three types of attribute search optimization are supported.
•
EQUALITY
: Match for
NUMBER
,
STRING
,
BINARY
, and
VARIANT
JSON fields
•
SUBSTRING
: Partial match for
STRING BINARY
and
VARIANT
JSON fields
•
GEO
: Match for
GEOGRAPHY
data type
To enable SOS for an individual table attribute, use this:
ALTER TABLE tpc.tpc_owner.lineitem_baseline
ADD SEARCH OPTIMIZATION
ON EQUALITY ( l_shipmode );
Where attributes meet search optimization criteria, you can set both
EQUALITY
and
SUBSTRING
as shown next:
ALTER TABLE tpc.tpc_owner.lineitem_baseline
ADD SEARCH OPTIMIZATION
ON EQUALITY ( l_shipmode ), SUBSTRING ( l_shipmode );
Chapter 7
SearCh OptimizatiOn ServiCe