Working With Search-Optimized Tables¶

Modifying the Table¶

A search access path becomes invalid if the default value of a column is changed.

To use search optimization again after a search access path has become invalid, you must drop the SEARCH OPTIMIZATION property and add the SEARCH OPTIMIZATION property back to the table.

A search access path remains valid if you add, drop, or rename a column:

• If you enabled search optimization for an entire table without specifying specific columns, then when you add a column to a table, the new column is added to the search access path automatically. However, if you used the ON clause when enabling search optimization for a column, new columns are not added automatically.

• When you drop a column from a table, the dropped column is removed from the search access path automatically.

• Renaming a column doesn’t require any changes to the search access path.

If you drop a table, the SEARCH OPTIMIZATION property and search access paths are also dropped. Note that:

• Undropping the table immediately reestablishes search optimization as a property of the table.

• When you drop a table, the search access path has the same data retention period as the table.

If you drop the SEARCH OPTIMIZATION property from the table, the search access path is removed. When you add the SEARCH OPTIMIZATION property back to the table, the maintenance service needs to recreate the search access path. (There is no way to undrop the property.)

Cloning the Table, Schema, or Database¶

If you clone a table, schema, or database, the SEARCH OPTIMIZATION property and search access paths of each table are also cloned. (Cloning a table, schema, or database creates a zero-copy clone of each table and its corresponding search access paths.)

Note that if you use CREATE TABLE … LIKE to create a new empty table with the same columns as the original table, the SEARCH OPTIMIZATION property is not copied to the new table.

Working With Tables in a Secondary Database (Database Replication Support)¶

If a table in the primary database has the SEARCH OPTIMIZATION property enabled, the property is replicated to the corresponding table in the secondary database.

Search access paths in the secondary database are not replicated but are instead rebuilt automatically. Note that this process incurs the same kinds of costs described in Search Optimization Cost Estimation and Management.

Sharing Tables¶

Data providers can use Secure Data Sharing to share tables that have search optimization enabled.

When querying shared tables, data consumers can benefit from any performance improvements made by the search optimization service.

Masking Policies and Row Access Policies¶

The search optimization service is fully compatible with tables that use masking policies and row access policies.

However, when search optimization is enabled, a user who is prevented from seeing a value due to a masking policy or row access policy might be able to deduce with greater certainty whether that value exists. With or without search optimization, differences in query latency can provide hints about the presence or absence of data restricted by a policy, which may constitute a security issue depending on the sensitivity of the data. This effect can be magnified by search optimization since it can make a query that does not return results even faster.

For example, suppose that a row access policy prevents a user from accessing rows with country = 'US', but the data does not include rows with country = 'US'. Now suppose that search optimization is enabled for the country column and that the user runs a query with WHERE country = 'US'. The query returns empty results as expected, but the query might run faster with search optimization than without. In this case, the user can more easily infer that the data does not contain any rows where country = 'US' based on the time taken to run the query.