Alter Standard Table - Search Optimization

Last updated: January 27, 2026

Adds or drops search optimization service for the table. Search optimization improves query performance for point lookups and substring searches.

Known limitations:

This change type does not support automatic rollback.
Snowflake snapshots created with Liquibase Secure 5.1 are incompatible with earlier versions due to enhanced object type detection for TABLES and VIEWS. Regenerate all snapshots with 5.1 after upgrading to avoid false differences in diff and diff-changelog operations.

Available attributes

Attribute	Type	Description	Required
`catalogName`	String	Name of the catalog (database) containing the table	No
`schemaName`	String	Name of the schema containing the table	No
`tableName`	String	Name of the table to alter	Yes
`ifExists`	Boolean	Only execute if the table exists	No

alterSearchOptimization nested attributes

Attribute	Type	Description	Required
`add`	Boolean	If true, adds search optimization	No*
`drop`	Boolean	If true, drops search optimization	No*
`on`	String	Column specification for search optimization	No

*Exactly one of add or drop must be specified.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:pro-snowflake="http://www.liquibase.org/xml/ns/pro-snowflake"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/pro-snowflake
        http://www.liquibase.org/xml/ns/pro-snowflake/liquibase-pro-snowflake-latest.xsd">

    <!-- Add search optimization -->
    <changeSet id="add-search-optimization" author="examples">
        <pro-snowflake:alterStandardTable tableName="SEARCHABLE_DATA">
            <pro-snowflake:alterSearchOptimization add="true" on="name, description"/>
        </pro-snowflake:alterStandardTable>
    </changeSet>

    <!-- Drop search optimization -->
    <changeSet id="drop-search-optimization" author="examples">
        <pro-snowflake:alterStandardTable tableName="SIMPLE_TABLE">
            <pro-snowflake:alterSearchOptimization drop="true"/>
        </pro-snowflake:alterStandardTable>
    </changeSet>

</databaseChangeLog>

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "add-search-optimization",
        "author": "examples",
        "changes": [
          {
            "alterStandardTable": {
              "tableName": "SEARCHABLE_DATA",
              "alterSearchOptimization": {
                "add": true,
                "on": "name, description"
              }
            }
          }
        ]
      }
    },
    {
      "changeSet": {
        "id": "drop-search-optimization",
        "author": "examples",
        "changes": [
          {
            "alterStandardTable": {
              "tableName": "SIMPLE_TABLE",
              "alterSearchOptimization": {
                "drop": true
              }
            }
          }
        ]
      }
    }
  ]
}

databaseChangeLog:
  # Add search optimization
  - changeSet:
      id: add-search-optimization
      author: examples
      changes:
        - alterStandardTable:
            tableName: SEARCHABLE_DATA
            alterSearchOptimization:
              add: true
              on: "name, description"

  # Drop search optimization
  - changeSet:
      id: drop-search-optimization
      author: examples
      changes:
        - alterStandardTable:
            tableName: SIMPLE_TABLE
            alterSearchOptimization:
              drop: true

--liquibase formatted sql

--changeset examples:add-search-optimization
-- Add search optimization
ALTER TABLE MYDB.PUBLIC.SEARCHABLE_DATA ADD SEARCH OPTIMIZATION ON name, description;

--changeset examples:drop-search-optimization
-- Drop search optimization
ALTER TABLE MYDB.PUBLIC.SIMPLE_TABLE DROP SEARCH OPTIMIZATION;

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:pro-snowflake="http://www.liquibase.org/xml/ns/pro-snowflake"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/pro-snowflake
        http://www.liquibase.org/xml/ns/pro-snowflake/liquibase-pro-snowflake-latest.xsd">

    <!-- Add search optimization -->
    <changeSet id="add-search-optimization" author="examples">
        <pro-snowflake:alterStandardTable tableName="SEARCHABLE_DATA">
            <pro-snowflake:alterSearchOptimization add="true" on="name, description"/>
        </pro-snowflake:alterStandardTable>
    </changeSet>

    <!-- Drop search optimization -->
    <changeSet id="drop-search-optimization" author="examples">
        <pro-snowflake:alterStandardTable tableName="SIMPLE_TABLE">
            <pro-snowflake:alterSearchOptimization drop="true"/>
        </pro-snowflake:alterStandardTable>
    </changeSet>

</databaseChangeLog>

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "add-search-optimization",
        "author": "examples",
        "changes": [
          {
            "alterStandardTable": {
              "tableName": "SEARCHABLE_DATA",
              "alterSearchOptimization": {
                "add": true,
                "on": "name, description"
              }
            }
          }
        ]
      }
    },
    {
      "changeSet": {
        "id": "drop-search-optimization",
        "author": "examples",
        "changes": [
          {
            "alterStandardTable": {
              "tableName": "SIMPLE_TABLE",
              "alterSearchOptimization": {
                "drop": true
              }
            }
          }
        ]
      }
    }
  ]
}

databaseChangeLog:
  # Add search optimization
  - changeSet:
      id: add-search-optimization
      author: examples
      changes:
        - alterStandardTable:
            tableName: SEARCHABLE_DATA
            alterSearchOptimization:
              add: true
              on: "name, description"

  # Drop search optimization
  - changeSet:
      id: drop-search-optimization
      author: examples
      changes:
        - alterStandardTable:
            tableName: SIMPLE_TABLE
            alterSearchOptimization:
              drop: true

--liquibase formatted sql

--changeset examples:add-search-optimization
-- Add search optimization
ALTER TABLE MYDB.PUBLIC.SEARCHABLE_DATA ADD SEARCH OPTIMIZATION ON name, description;

--changeset examples:drop-search-optimization
-- Drop search optimization
ALTER TABLE MYDB.PUBLIC.SIMPLE_TABLE DROP SEARCH OPTIMIZATION;

Alter Standard Table - Search Optimization

Available attributes

alterSearchOptimization nested attributes

XML

JSON

YAML

SQL

More like this

alterStandardTable alterClustering

Snowflake: ALTER TABLE