> For the complete documentation index, see [llms.txt](https://docs.starrocks.io/llms.txt). This page is also available as Markdown at its `.md` URL.

# ALTER TABLE

ALTER TABLE Modifies an existing table, including:

* [Rename table, partition, rollup, or column](#rename)
* [Modify table comment](#alter-table-comment-from-v31)
* [Modify partitions (add/delete partitions and modify partition attributes)](#modify-partition)
* [Modify the bucketing method and number of buckets](#modify-the-bucketing-method-and-number-of-buckets-from-v32)
* [Modify the tablet size](#modify-the-tablet-size)
* [Modify columns (add/delete columns, change column order, and modify column comment)](#modify-columns-adddelete-columns-change-column-order-modify-column-comment)
* [Create/delete rollup](#modify-rollup)
* [Create/delete index](#modify-indexes)
* [Modify table properties](#modify-table-properties)
* [Atomic swap](#swap)
* [Manual data version compaction](#manual-compaction-from-31)
* [Drop Primary Key Persistent Index](#drop-primary-key-persistent-index-from-339)

tip

This operation requires the ALTER privilege on the destination table.

## Syntax[​](#syntax "Direct link to Syntax")

```sql
ALTER TABLE [<db_name>.]<tbl_name>
alter_clause1[, alter_clause2, ...]

```

`alter_clause` can held the following operations: rename, comment, partition, bucket, column, rollup, index, table property, swap, and compaction.

* rename: renames a table, rollup, partition, or column (supported from **v3.3.2 onwards**).
* comment: modifies the table comment (supported from **v3.1 onwards**).
* partition: modifies partition properties, drops a partition, or adds a partition.
* bucket: modifies the bucketing method and number of buckets.
* column: adds, drops, or reorders columns, modifies column type or comment
* rollup: creates or drops a rollup.
* index: modifies indexes.
* swap: atomic exchange of two tables.
* compaction: performs manual compaction to merge versions of loaded data (supported from **v3.1 onwards**).
* drop persistent index: Drop persistent index for Primary Key table in shared-data cluster (supported from **v3.3.9 onwards**).

## Limits and usage notes[​](#limits-and-usage-notes "Direct link to Limits and usage notes")

* Operations on partition, column, and rollup index cannot be performed in one ALTER TABLE statement.
* One table can have only one ongoing schema change operation at a time. You cannot run two schema change commands on a table at the same time.
* Operations on bucket, column and rollup are asynchronous operations. A success message is return immediately after the task is submitted. You can run the [SHOW ALTER TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md) command to check the progress, and run the [CANCEL ALTER TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CANCEL_ALTER_TABLE.md) command to cancel the operation.
* Operations on rename, comment, partition, index and swap are synchronous operations, and a command return indicates that the execution is finished.

### Rename[​](#rename "Direct link to Rename")

Rename supports modification of table name, rollup, and partition name.

#### Rename a table[​](#rename-a-table "Direct link to Rename a table")

```sql
ALTER TABLE <tbl_name> RENAME <new_tbl_name>

```

#### Rename a rollup[​](#rename-a-rollup "Direct link to Rename a rollup")

```sql
ALTER TABLE [<db_name>.]<tbl_name>
RENAME ROLLUP <old_rollup_name> <new_rollup_name>

```

#### Rename a partition[​](#rename-a-partition "Direct link to Rename a partition")

```sql
ALTER TABLE [<db_name>.]<tbl_name>
RENAME PARTITION <old_partition_name> <new_partition_name>

```

#### Rename a column[​](#rename-a-column "Direct link to Rename a column")

From v3.3.2 onwards, StarRocks supports renaming columns.

```sql
ALTER TABLE [<db_name>.]<tbl_name>
RENAME COLUMN <old_col_name> [ TO ] <new_col_name>

```

note

* After renaming a column from A to B, adding a new column named A is not supported.
* Materialized views built on a renamed column will not take effect. You must rebuild them upon the column with the new name.

### Alter table comment (from v3.1)[​](#alter-table-comment-from-v31 "Direct link to Alter table comment (from v3.1)")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name> COMMENT = "<new table comment>";

```

### Modify partition[​](#modify-partition "Direct link to Modify partition")

#### ADD PARTITION(S)[​](#add-partitions "Direct link to ADD PARTITION(S)")

You must strictly follow the respective syntax to add range partitions or list partitions.

note

* Adding expression partitions is not supported.
* Please note that `PARTITION BY date_trunc(column)` and `PARTITION BY time_slice(column)` are considered range partitioning, despite their format of expression partitioning. Therefore, you can use the following syntax for range partitions to add new partitions to tables use such partitioning strategies.

Syntax：

* Range partitions

  ```sql
  ALTER TABLE
      ADD { single_range_partition | multi_range_partitions } [distribution_desc] ["key"="value"];

  single_range_partition ::=
      PARTITION [IF NOT EXISTS] <partition_name> VALUES partition_key_desc

  partition_key_desc ::=
      { LESS THAN { MAXVALUE | value_list }
      | [ value_list , value_list ) } -- Note that [ represents a left-closed interval.

  value_list ::=
      ( <value> [, ...] )

  multi_range_partitions ::=
      { PARTITIONS START ("<start_date_value>") END ("<end_date_value>") EVERY ( INTERVAL <N> <time_unit> )
      | PARTITIONS START ("<start_integer_value>") END ("<end_integer_value>") EVERY ( <granularity> ) } -- The partition column values still need to be enclosed in double quotes even if the partition column values specified by START and END are integers. However, the interval values in the EVERY clause do not need to be enclosed in double quotes.

  ```

* List partitions

  ```sql
  ALTER TABLE
      ADD PARTITION <partition_name> VALUES IN (value_list) [distribution_desc] ["key"="value"];

  value_list ::=
      value_item [, ...]

  value_item ::=
      { <value> | ( <value> [, ...] ) }

  ```

Parameters:

* Partition-related parameters:

  * For range partitions, you can add a single range partition (`single_range_partition`) or multiple range partitions in batch (`multi_range_partitions`).
  * For list partitions, you can only add a single list partition.

* `distribution_desc`:

  You can set the number of buckets for the new partition separately, but you cannot set the bucketing method separately.

* `"key"="value"`:

  You can set properties for the new partition. For details, see [CREATE TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md#properties).

Examples:

* Range partitions

  * If the partition column is specified as `event_day` at table creation, for example `PARTITION BY RANGE(event_day)`, and a new partition needs to be added after table creation, you can execute:

    ```sql
    ALTER TABLE site_access ADD PARTITION p4 VALUES LESS THAN ("2020-04-30");

    ```

  * If the partition column is specified as `datekey` at table creation, for example `PARTITION BY RANGE (datekey)`, and multiple partitions need to be added in batch after table creation, you can execute:

    ```sql
    ALTER TABLE site_access
        ADD PARTITIONS START ("2021-01-05") END ("2021-01-10") EVERY (INTERVAL 1 DAY);

    ```

* List partitions

  * If a single partition column is specified at table creation, for example `PARTITION BY LIST (city)`, and a new partition needs to be added after table creation, you can execute:

    ```sql
    ALTER TABLE t_recharge_detail2
    ADD PARTITION pCalifornia VALUES IN ("Los Angeles","San Francisco","San Diego");

    ```

  * If multiple partition columns are specified at table creation, for example `PARTITION BY LIST (dt,city)`, and a new partition needs to be added after table creation, you can execute:

    ```sql
    ALTER TABLE t_recharge_detail4 
    ADD PARTITION p202204_California VALUES IN
    (
        ("2022-04-01", "Los Angeles"),
        ("2022-04-01", "San Francisco"),
        ("2022-04-02", "Los Angeles"),
        ("2022-04-02", "San Francisco")
    );

    ```

#### DROP PARTITION(S)[​](#drop-partitions "Direct link to DROP PARTITION(S)")

* Drop a single partition:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP PARTITION [ IF EXISTS ] <partition_name> [ FORCE ]

```

* Drop partitions in batch (Supported from v3.4.0):

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP PARTITIONS [ IF EXISTS ]  { partition_name_list | multi_range_partitions } [ FORCE ]

partition_name_list ::= ( <partition_name> [, ... ] )

multi_range_partitions ::=
    { START ("<start_date_value>") END ("<end_date_value>") EVERY ( INTERVAL <N> <time_unit> )
    | START ("<start_integer_value>") END ("<end_integer_value>") EVERY ( <granularity> ) } -- The partition column values still need to be enclosed in double quotes even if the partition column values are integers. However, the interval values in the EVERY clause do not need to be enclosed in double quotes.

```

Notes for `multi_range_partitions`:

* It only applies to Range Partitioning.

* The parameters involved is consistent with those in [ADD PARTITION(S)](#add-partitions).

* It only supports partitions with a single Partition Key.

* Drop partitions with Common Partition Expression (Supported from v3.5.0):

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP PARTITIONS WHERE <expr>

```

From v3.5.0 onwards, StarRocks supports dropping partitions using Common Partition Expression. You can specify a WHERE clause with an expression to filter the partitions to drop.

* The expression declares the partitions to be dropped. Partitions that meet the condition in the expression will be dropped in batch. Be cautious when proceeding.

* The expression can only contain partition columns and constants. Non-partition columns are not supported.

* Common Partition Expression applies to List partitions and Range partitions differently:

  * For tables with List partitions, StarRocks supports deleting partitions filtered by the Common Partition Expression.
  * For tables with Range partitions, StarRocks can only filter and delete partitions using the partition pruning capability of FE. Partitions correspond to predicates that are not supported by partition pruning cannot be filtered and deleted.

Example:

```sql
-- Drop the data earlier than the last three months. Column `dt` is the partition column of the table.
ALTER TABLE t1 DROP PARTITIONS WHERE dt < CURRENT_DATE() - INTERVAL 3 MONTH;

```

note

* Keep at least one partition for partitioned tables.
* If FORCE is not specified, you can recover the dropped partitions by using the [RECOVER](https://docs.starrocks.io/docs/sql-reference/sql-statements/backup_restore/RECOVER.md) command within a specified period (1 day by default).
* If FORCE is specified, the partitions will be deleted directly regardless of whether there are any unfinished operations on the partitions, and they cannot be recovered. Thus, generally, this operation is not recommended.

#### Add a temporary partition[​](#add-a-temporary-partition "Direct link to Add a temporary partition")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name> 
ADD TEMPORARY PARTITION [IF NOT EXISTS] <partition_name>
{ single_range_partition | multi_range_partitions | list_partitions }
[DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]

-- For details of single_range_partition and multi_range_partitions, see ADD PARTITION(S) section in this page.

list_partitions::= 
    PARTITION <partition_name> VALUES IN (value_list)

value_list ::=
    value_item [, ...]

value_item ::=
    { <value> | ( <value> [, ...] ) }

```

#### Use a temporary partition to replace the current partition[​](#use-a-temporary-partition-to-replace-the-current-partition "Direct link to Use a temporary partition to replace the current partition")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
REPLACE PARTITION <partition_name>
partition_desc ["key"="value"]
WITH TEMPORARY PARTITION
partition_desc ["key"="value"]
[PROPERTIES ("key"="value", ...)]

```

#### Drop a temporary partition[​](#drop-a-temporary-partition "Direct link to Drop a temporary partition")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP TEMPORARY PARTITION <partition_name>

```

#### Modify partition properties[​](#modify-partition-properties "Direct link to Modify partition properties")

**Syntax**

```sql
ALTER TABLE [<db_name>.]<tbl_name>
MODIFY PARTITION { <partition_name> | ( <partition1_name> [, <partition2_name> ...] ) | (*) }
SET ("key" = "value", ...);

```

**Usages**

* The following properties of a partition can be modified:

  * storage_medium
  * storage_cooldown_ttl or storage_cooldown_time
  * replication_num

* For the table that has only one partition, the partition name is the same as the table name. If the table is divided into multiple partitions, you can use `(*)`to modify the properties of all partitions, which is more convenient.

* Execute `SHOW PARTITIONS FROM <tbl_name>` to view the partition properties after modification.

### Modify the bucketing method and number of buckets (from v3.2)[​](#modify-the-bucketing-method-and-number-of-buckets-from-v32 "Direct link to Modify the bucketing method and number of buckets (from v3.2)")

Syntax:

```sql
ALTER TABLE [<db_name>.]<table_name>
[ partition_names ]
[ distribution_desc ]

partition_names ::= 
    (PARTITION | PARTITIONS) ( <partition_name> [, <partition_name> ...] )

distribution_desc ::=
    DISTRIBUTED BY RANDOM [ BUCKETS <num> ] |
    DISTRIBUTED BY HASH ( <column_name> [, <column_name> ...] ) [ [ DEFAULT ] BUCKETS <num> ]

```

Example:

For example, the original table is a Duplicate Key table where hash bucketing is used and the number of buckets is automatically set by StarRocks.

```sql
CREATE TABLE IF NOT EXISTS details (
    event_time DATETIME NOT NULL COMMENT "datetime of event",
    event_type INT NOT NULL COMMENT "type of event",
    user_id INT COMMENT "id of user",
    device_code INT COMMENT "device code",
    channel INT COMMENT ""
)
DUPLICATE KEY(event_time, event_type)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id);

-- Insert data of several days
INSERT INTO details (event_time, event_type, user_id, device_code, channel) VALUES
-- Data of November 26th
('2023-11-26 08:00:00', 1, 101, 12345, 2),
('2023-11-26 09:15:00', 2, 102, 54321, 3),
('2023-11-26 10:30:00', 1, 103, 98765, 1),
-- Data of November 27th
('2023-11-27 08:30:00', 1, 104, 11111, 2),
('2023-11-27 09:45:00', 2, 105, 22222, 3),
('2023-11-27 11:00:00', 1, 106, 33333, 1),
-- Data of November 28th
('2023-11-28 08:00:00', 1, 107, 44444, 2),
('2023-11-28 09:15:00', 2, 108, 55555, 3),
('2023-11-28 10:30:00', 1, 109, 66666, 1);

```

#### Modify the bucketing method only[​](#modify-the-bucketing-method-only "Direct link to Modify the bucketing method only")

> **NOTICE**
>
> * The modification is applied to all partitions in the table and cannot be applied to specific partitions only.
> * Although only the bucketing method needs to be modified, the number of buckets still needs to be specified in the command using `BUCKETS <num>`. If `BUCKETS <num>` is not specified, it means that the number of buckets is automatically determined by StarRocks.

* The bucketing method is modified to random bucketing from hash bucketing and the number of buckets remains automatically set by StarRocks.

  ```sql
  ALTER TABLE details DISTRIBUTED BY RANDOM;

  ```

* The keys for hash bucketing are modified to `user_id, event_time` from `event_time, event_type`. And the number of buckets remains automatically set by StarRocks.

  ```sql
  ALTER TABLE details DISTRIBUTED BY HASH(user_id, event_time);

  ```

#### Modify the number of buckets only[​](#modify-the-number-of-buckets-only "Direct link to Modify the number of buckets only")

> **NOTICE**
>
> Although only the number of buckets needs to be modified, the bucketing method still needs to be specified in the command, for example, `HASH(user_id)`.

* Modify the number of buckets for all partitions to 10 from being automatically set by StarRocks.

  ```sql
  ALTER TABLE details DISTRIBUTED BY HASH(user_id) BUCKETS 10;

  ```

* Modify the default number of buckets of the table to 10 from being automatically set by StarRocks **without changing that of existing partitions** (supported from v3.5.8 and v4.0.1 onwards).

  ```sql
  ALTER TABLE details DISTRIBUTED BY HASH(user_id) DEFAULT BUCKETS 10;

  ```

  > **NOTICE**
  >
  > You cannot specify both `partition_names` and `DEFAULT`.

* Modify the number of buckets for specified partitions to 15 from being automatically set by StarRocks.

  ```sql
  ALTER TABLE details PARTITIONS (p20231127, p20231128) DISTRIBUTED BY HASH(user_id) BUCKETS 15 ;

  ```

  > **NOTE**
  >
  > Partition names can be viewed by executing `SHOW PARTITIONS FROM <table_name>;`.

#### Modify both the bucketing method and the number of buckets[​](#modify-both-the-bucketing-method-and-the-number-of-buckets "Direct link to Modify both the bucketing method and the number of buckets")

> **NOTICE**
>
> The modification is applied to all partitions in the table and cannot be applied to specific partitions only.

* Modify the bucketing method from hash bucketing to random bucketing, and change the number of buckets to 10 from being automatically set by StarRocks.

  ```sql
  ALTER TABLE details DISTRIBUTED BY RANDOM BUCKETS 10;

  ```

* Modify the key for hash bucketing, and change the number of buckets to 10 from being automatically set by StarRocks. The key used for hashing bucketing is modified to `user_id, event_time` from the original `event_time, event_type`. The number of buckets is modified to 10 from automatically set by StarRocks.

  ```sql
  ALTER TABLE details DISTRIBUTED BY HASH(user_id, event_time) BUCKETS 10;

  ```

### Modify the tablet size[​](#modify-the-tablet-size "Direct link to Modify the tablet size")

StarRocks supports splitting and merging tablets from v4.1 onwards, allowing dynamic management of tablet size, thus an adaptive mechanism for handling data skew.

Syntax for SPLIT:

```sql
ALTER TABLE <table_name> SPLIT { TABLET | TABLETS }
    [ 
        { PARTITION (<partition_name>) |  PARTITIONS (<partition_name1>, <partition_name2>, ...) } 
    ｜
        { (<tablet_id>) | (<tablet_id1>, <tablet_id2>, <tablet_id3>, ...) }
    ]
[PROPERTIES (
    "tablet_reshard_target_size"="<target_size>")
]

```

Syntax for MERGE:

```sql
ALTER TABLE <table_name> MERGE { TABLET | TABLETS }
    [
        { PARTITION (<partition_name>) | PARTITIONS (<partition_name1>, <partition_name2>, ...) }
    ｜
        { (<tablet_id1>, <tablet_id2>, ...) | (<tablet_id1>, <tablet_id2>, ...) (<tablet_id3>, <tablet_id4>, ...) ... }
    ]
[PROPERTIES (
    "tablet_reshard_target_size"="<target_size>")
]

```

Parameter:

* `tablet_reshard_target_size`: The target size of the tablets after the SPLIT or MERGE operation. Default: 10 GB. You do not need to specify this parameter if you have explicitly specified tablet IDs.

  * A tablet will be split if both the following conditions are met:

    * The size of the tablet is **larger** than `tablet_reshard_target_size`.
    * The number of tablets that are running tablet SPLIT or MERGE is less than the FE configuration `tablet_reshard_max_parallel_tablets` (Default: 10240).

  * A tablet will be merged if both the following conditions are met:

    * The total size of two adjacent tablets is **smaller** than `tablet_reshard_target_size`.
    * The number of tablets that are running tablet SPLIT or MERGE is less than the FE configuration `tablet_reshard_max_parallel_tablets` (Default: 10240).

For detailed examples, see [Split or merge tablets](#split-or-merge-tablets).

### Modify columns (add/delete columns, change column order, modify column comment)[​](#modify-columns-adddelete-columns-change-column-order-modify-column-comment "Direct link to Modify columns (add/delete columns, change column order, modify column comment)")

#### Add a column to the specified location of the specified index[​](#add-a-column-to-the-specified-location-of-the-specified-index "Direct link to Add a column to the specified location of the specified index")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

```

Note:

1. If you add a value column to an Aggregate table, you need to specify agg_type.
2. If you add a key column to a non-Aggregate table (such as a Duplicate Key table), you need to specify the KEY keyword. On an Aggregate table, a column that specifies neither `agg_type` nor `KEY` is created as a key column, which changes the table's aggregation key and rewrites existing data. Set the FE configuration item `allow_implicit_key_column_in_agg_add_column` to `false` to reject such a statement instead, so that you are asked to specify one of the two.
3. You cannot add a column that already exists in the base index to the rollup. (You can recreate a rollup if needed.)

#### Add multiple columns to specified index[​](#add-multiple-columns-to-specified-index "Direct link to Add multiple columns to specified index")

Syntax:

* Add multiple columns

  ```sql
  ALTER TABLE [<db_name>.]<tbl_name>
  ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
  [TO rollup_index_name]
  [PROPERTIES ("key"="value", ...)]

  ```

* Add multiple columns and use AFTER to specify locations of the added columns

  ```sql
  ALTER TABLE [<db_name>.]<tbl_name>
  ADD COLUMN column_name1 column_type [KEY | agg_type] DEFAULT "default_value" AFTER column_name,
  ADD COLUMN column_name2 column_type [KEY | agg_type] DEFAULT "default_value" AFTER column_name
  [, ...]
  [TO rollup_index_name]
  [PROPERTIES ("key"="value", ...)]

  ```

Note:

1. If you add a value column to an Aggregate table, you need to specify `agg_type`.

2. If you add a key column to a non-Aggregate table, you need to specify the KEY keyword. On an Aggregate table, a column that specifies neither `agg_type` nor `KEY` is created as a key column. Set the FE configuration item `allow_implicit_key_column_in_agg_add_column` to `false` to reject such a statement instead.

3. You cannot add a column that already exists in the base index to the rollup. (You can create another rollup if needed.)

#### Add a generated column (from v3.1)[​](#add-a-generated-column-from-v31 "Direct link to Add a generated column (from v3.1)")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
ADD COLUMN col_name data_type [NULL] AS generation_expr [COMMENT 'string']

```

You can add a generated column and specify its expression. [The generated column](https://docs.starrocks.io/docs/sql-reference/sql-statements/generated_columns.md) can be used to precompute and store the results of expressions, which significantly accelerates queries with the same complex expressions. Since v3.1, StarRocks supports generated columns.

#### Drop a column from specified index[​](#drop-a-column-from-specified-index "Direct link to Drop a column from specified index")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP COLUMN column_name
[FROM rollup_index_name];

```

Note:

1. You cannot drop partition column.
2. If the column is dropped from the base index, it will also be dropped if it is included in the rollup.

#### Modify the column type, position, comment, and other properties[​](#modify-the-column-type-position-comment-and-other-properties "Direct link to Modify the column type, position, comment, and other properties")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
MODIFY COLUMN <column_name> 
[ column_type [ KEY | agg_type ] ] [ NULL | NOT NULL ] 
[ DEFAULT "<default_value>"] [ COMMENT "<new_column_comment>" ]
[ AFTER <column_name> | FIRST ]
[ FROM rollup_index_name ]
[ PROPERTIES ("key"="value", ...) ]

```

Note:

1. If you modify the value column in aggregation models, you need to specify agg_type.

2. If you modify the key column in non-aggregation models, you need to specify the KEY keyword.

3. While modifying the type, default value, nullability, and position, you must specify the full definition of the column in the statement.

4. Modifying only the column comment — whether via `MODIFY COLUMN <column_name> COMMENT "<new_column_comment>"` or via a full column definition where only the comment differs — changes only the metadata and does not initiate a Schema Change task. This applies to Primary Key columns, key columns, and regular columns. If the full definition also changes any other attribute of the column, the statement initiates a Schema Change task as usual.

5. The partition column cannot be modified.

6. The following types of conversions are currently supported (accuracy loss is guaranteed by the user).

   * Convert TINYINT/SMALLINT/INT/BIGINT to TINYINT/SMALLINT/INT/BIGINT/DOUBLE.
   * Convert TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL to VARCHAR. VARCHAR supports modification of maximum length.
   * Convert VARCHAR to TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE.
   * Convert VARCHAR to DATE (currently support six formats: "%Y-%m-%d", "%y-%m-%d", "%Y%m%d", "%y%m%d", "%Y/%m/%d, "%y/%m/%d")
   * Convert DATETIME to DATE(only year-month-day information is retained, i.e. `2019-12-09 21:47:05` `<-->` `2019-12-09`)
   * Convert DATE to DATETIME (set hour, minute, second to zero, For example: `2019-12-09` `<-->` `2019-12-09 00:00:00`)
   * Convert FLOAT to DOUBLE
   * Convert INT to DATE (If the INT data fails to convert, the original data remains the same)

7. Conversion from NULL to NOT NULL is not supported.

8. You can modify several properties in a single MODIFY COLUMN clause. However, some combination of properties are not supported.

#### Reorder the columns of specified index[​](#reorder-the-columns-of-specified-index "Direct link to Reorder the columns of specified index")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
ORDER BY (column_name1, column_name2, ...)
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

```

Note:

* All columns in the index must be written.
* The value column is listed after the key column.

#### Modify the sort key[​](#modify-the-sort-key "Direct link to Modify the sort key")

Since v3.0, the sort keys for the Primary Key tables can be modified. v3.3 extends this support to Duplicate Key tables, Aggregate tables, and Unique Key tables.

The sort keys in Duplicate Key tables and Primary Key tables can be combination of any sort columns. The sort keys in Aggregate tables and Unique Key tables must include all key columns, but the order of the columns does not need to be same as the key columns.

Syntax:

```sql
ALTER TABLE [<db_name>.]<table_name>
[ order_desc ]

order_desc ::=
    ORDER BY <column_name> [, <column_name> ...]

```

Example: modify the sort keys in Primary Key tables.

For example, the original table is a Primary Key table where the sort key and the primary key are coupled, which is `dt, order_id`.

```sql
create table orders (
    dt date NOT NULL,
    order_id bigint NOT NULL,
    user_id int NOT NULL,
    merchant_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
) PRIMARY KEY (dt, order_id)
PARTITION BY date_trunc('day', dt)
DISTRIBUTED BY HASH(order_id);

```

Decouple the sort key from the primary key, and modify the sort key to `dt, revenue, state`.

```sql
ALTER TABLE orders ORDER BY (dt, revenue, state);

```

#### Modify a STRUCT column to add or drop a field[​](#modify-a-struct-column-to-add-or-drop-a-field "Direct link to Modify a STRUCT column to add or drop a field")

Beta feature

[Advice on use of Beta features](https://docs.starrocks.io/docs/maturity/features.md)

From v3.2.10 and v3.3.2 onwards, StarRocks supports modifying a STRUCT column to add or drop a field, which can be nested or within an ARRAY type.

Syntax:

```sql
-- Add a field
ALTER TABLE [<db_name>.]<tbl_name> MODIFY COLUMN <column_name>
ADD FIELD field_path field_desc

-- Drop a field
ALTER TABLE [<db_name>.]<tbl_name> MODIFY COLUMN <column_name>
DROP FIELD field_path

field_path ::= [ { <field_name>. | [*]. } [ ... ] ]<field_name>

  -- Note that here `[*]` as a whole is a pre-defined symbol and represents all elements in the ARRAY field 
  -- when adding or removing a field in a STRUCT type nested within an ARRAY type.
  -- For detailed information, see the parameter description and examples of `field_path`.

field_desc ::= <field_type> [ AFTER <prior_field_name> | FIRST ]

```

Parameters:

* `field_path`: The field to be added or removed. This can be a simple field name, indicating a top-dimension field, for example, `new_field_name`, or a Column Access Path that represents a nested field, for example, `lv1_k1.lv2_k2.[*].new_field_name`.
* `[*]`: When a STRUCT type is nested within an ARRAY type, `[*]` represents all elements in the ARRAY field. It is used to add or remove a field in all STRUCT elements nested under the ARRAY field.
* `prior_field_name`: The field preceding the newly added field. Used in conjunction with the AFTER keyword to specify the order of the new field. You do not need to specify this parameter if the FIRST keyword is used, indicating the new field should be the first field. The dimension of `prior_field_name` is determined by `field_path` (specifically, the part preceding `new_field_name`, that is, `level1_k1.level2_k2.[*]`) and does not need to be specified explicitly.

Examples of `field_path`:

* Add or drop a sub-field in a STRUCT field nested within a STRUCT column.

  Suppose there is a column `fx stuct<c1 int, c2 struct <v1 int, v2 int>>`. The syntax to add a `v3` field under `c2` is:

  ```sql
  ALTER TABLE tbl MODIFY COLUMN fx ADD FIELD c2.v3 INT

  ```

  After the operation, the column becomes `fx stuct<c1 int, c2 struct <v1 int, v2 int, v3 int>>`.

* Add or drop a sub-field in every STRUCT field nested within a ARRAY field.

  Suppose there is a column `fx struct<c1 int, c2 array<struct <v1 int, v2 int>>>`. The field `c2` is an ARRAY type, which contains a STRUCT with two fields `v1` and `v2`. The syntax to add a `v3` field to the nested STRUCT is:

  ```sql
  ALTER TABLE tbl MODIFY COLUMN fx ADD FIELD c2.[*].v3 INT

  ```

  After the operation, the column becomes `fx struct<c1 int, c2 array<struct <v1 int, v2 int, v3 int>>>`.

For more usage instructions, see [Example - Column -14](#column).

note

* Currently, this feature is only supported in shared-nothing clusters.
* The table must have the `fast_schema_evolution` property enabled.
* Modifying the Value type of a MAP subfield in a STRUCT type is not supported, regardless of whether the Value type is ARRAY, STRUCT, or MAP.
* Newly added fields cannot have default values or attributes such as Nullable specified. They default to being Nullable, with a default value of null.
* After this feature is used, downgrading the cluster directly to a version that does not support this feature is not allowed.

### Modify rollup[​](#modify-rollup "Direct link to Modify rollup")

#### Create a rollup[​](#create-a-rollup "Direct link to Create a rollup")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name> 
ADD ROLLUP rollup_name (column_name1, column_name2, ...)
[FROM from_index_name]
[PROPERTIES ("key"="value", ...)]

```

PROPERTIES: Support setting timeout time and the default timeout time is one day.

Example:

```sql
ALTER TABLE [<db_name>.]<tbl_name> 
ADD ROLLUP r1(col1,col2) from r0;

```

#### Create rollups in batches[​](#create-rollups-in-batches "Direct link to Create rollups in batches")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
ADD ROLLUP [rollup_name (column_name1, column_name2, ...)
[FROM from_index_name]
[PROPERTIES ("key"="value", ...)],...];

```

Example:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
ADD ROLLUP r1(col1,col2) from r0, r2(col3,col4) from r0;

```

Note:

1. If from_index_name is not specified, then create from base index by default.
2. The columns in the rollup table must be existing columns in from_index.
3. In properties, user can specify the storage format. See CREATE TABLE for details.

#### Drop a rollup[​](#drop-a-rollup "Direct link to Drop a rollup")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP ROLLUP rollup_name [PROPERTIES ("key"="value", ...)];

```

Example:

```sql
ALTER TABLE [<db_name>.]<tbl_name> DROP ROLLUP r1;

```

#### Batch drop rollups[​](#batch-drop-rollups "Direct link to Batch drop rollups")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP ROLLUP [rollup_name [PROPERTIES ("key"="value", ...)],...];

```

Example:

```sql
ALTER TABLE [<db_name>.]<tbl_name> DROP ROLLUP r1, r2;

```

Note: You cannot drop the base index.

### Modify indexes[​](#modify-indexes "Direct link to Modify indexes")

You can create or drop the following indexes:

* [Bitmap index](https://docs.starrocks.io/docs/table_design/indexes/Bitmap_index.md)
* [N-Gram bloom filter index](https://docs.starrocks.io/docs/table_design/indexes/Ngram_Bloom_Filter_Index.md)
* [Full-Text inverted index](https://docs.starrocks.io/docs/table_design/indexes/inverted_index.md)
* [Vector index](https://docs.starrocks.io/docs/table_design/indexes/vector_index.md)

#### Create an index[​](#create-an-index "Direct link to Create an index")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
ADD INDEX index_name (column) [USING { BITMAP | NGRAMBF | GIN | VECTOR } ] [COMMENT '<comment>']

```

For detailed instructions and examples on creating these indexes, see the corresponding tutorials listed above.

#### Drop an index[​](#drop-an-index "Direct link to Drop an index")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP INDEX index_name;

```

### Modify table properties[​](#modify-table-properties "Direct link to Modify table properties")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
SET ("key" = "value")

```

Currently, StarRocks supports modifying the following table properties:

* `replication_num`
* `default.replication_num`
* `default.storage_medium`
* Dynamic partitioning related properties
* `enable_persistent_index`
* `bloom_filter_columns`
* `colocate_with`
* `bucket_size` (supported since 3.2)
* `base_compaction_forbidden_time_ranges` (supported since v3.2.13)
* `enable_statistic_auto_collect`

note

* In most cases, you are only allowed to modify one property at a time. You can only modify multiple properties at a time only if these properties have the same prefix. Currently, only `dynamic_partition.` and `binlog.` are supported.
* You can also modify the properties by merging into the above operation on column. See the [following examples](#examples).

### Swap[​](#swap "Direct link to Swap")

Swap supports atomic exchange of two tables.

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
SWAP WITH <tbl_name>;

```

note

* Unique Key and Foreign Key constraints between OLAP tables will be validated during Swap to ensure that the constraints of the two tables being swapped are consistent. An error will be returned if inconsistencies are detected. If no inconsistencies are detected, Unique Key and Foreign Key constraints will be automatically swapped.
* Materialized views that are depended on the tables being swapped will be automatically set to inactive, and their Unique Key and Foreign Key constraints will be removed and no longer available.

### Manual compaction (from 3.1)[​](#manual-compaction-from-31 "Direct link to Manual compaction (from 3.1)")

StarRocks uses a compaction mechanism to merge different versions of loaded data. This feature can combine small files into large files, which effectively improves query performance.

Before v3.1, compaction is performed in two ways:

* Automatic compaction by system: Compaction is performed at the BE level in the background. Users cannot specify database or table for compaction.
* Users can perform compaction by calling an HTTP interface.

Starting from v3.1, StarRocks offers a SQL interface for users to manually perform compaction by running SQL commands. They can choose a specific table or partition for compaction. This provides more flexibility and control over the compaction process.

Shared-data clusters support this feature from v3.3.0 onwards.

> **NOTE**
>
> From v3.2.13 onwards, you can forbid Base Compaction within certain time range using the property [`base_compaction_forbidden_time_ranges`](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md#forbid-base-compaction).

Syntax:

```sql
ALTER TABLE <tbl_name> [ BASE | CUMULATIVE ] COMPACT [ <partition_name> | ( <partition1_name> [, <partition2_name> ...] ) ]

```

That is:

```sql
-- Perform compaction on the entire table.
ALTER TABLE <tbl_name> COMPACT

-- Perform compaction on a single partition.
ALTER TABLE <tbl_name> COMPACT <partition_name>

-- Perform compaction on multiple partitions.
ALTER TABLE <tbl_name> COMPACT (<partition1_name>[,<partition2_name>,...])

-- Perform cumulative compaction.
ALTER TABLE <tbl_name> CUMULATIVE COMPACT (<partition1_name>[,<partition2_name>,...])

-- Perform base compaction.
ALTER TABLE <tbl_name> BASE COMPACT (<partition1_name>[,<partition2_name>,...])

```

The `be_compactions` table in the `information_schema` database records compaction results. You can run `SELECT * FROM information_schema.be_compactions;` to query data versions after compaction.

### Drop Primary Key Persistent Index (From 3.3.9)[​](#drop-primary-key-persistent-index-from-339 "Direct link to Drop Primary Key Persistent Index (From 3.3.9)")

Syntax:

```sql
ALTER TABLE [<db_name>.]<tbl_name>
DROP PERSISTENT INDEX ON TABLETS(<tablet_id>[, <tablet_id>, ...]);

```

> **NOTE**
>
> StarRocks only supports deleting persistent indexes for cloud-native Primary Key tables in shared-data clusters.

## Examples[​](#examples "Direct link to Examples")

### Table[​](#table "Direct link to Table")

1. Modify the default number of replicas of the table, which is used as the default number of replicas for newly added partitions.

   ```sql
   ALTER TABLE example_db.my_table
   SET ("default.replication_num" = "2");

   ```

2. Modify the actual number of replicas of a single-partition table.

   ```sql
   ALTER TABLE example_db.my_table
   SET ("replication_num" = "3");

   ```

3. Modify data writing and replication mode among replicas.

   ```sql
   ALTER TABLE example_db.my_table
   SET ("replicated_storage" = "false");

   ```

   This example sets the data writing and replication mode among replicas to "leaderless replication", which means data is written to multiple replicas at the same time without differentiating primary and secondary replicas. For more information, see the `replicated_storage` parameter in [CREATE TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md).

### Partition[​](#partition "Direct link to Partition")

1. Add a partition and use the default bucketing mode. The existing partition is \[MIN, 2013-01-01). The added partition is \[2013-01-01, 2014-01-01).

   ```sql
   ALTER TABLE example_db.my_table
   ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");

   ```

2. Add a partition and use the new number of buckets.

   ```sql
   ALTER TABLE example_db.my_table
   ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
   DISTRIBUTED BY HASH(k1);

   ```

3. Add a partition and use the new number of replicas.

   ```sql
   ALTER TABLE example_db.my_table
   ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
   ("replication_num"="1");

   ```

4. Alter the number of replicas for a partition.

   ```sql
   ALTER TABLE example_db.my_table
   MODIFY PARTITION p1 SET("replication_num"="1");

   ```

5. Batch alter the number of replicas for specified partitions.

   ```sql
   ALTER TABLE example_db.my_table
   MODIFY PARTITION (p1, p2, p4) SET("replication_num"="1");

   ```

6. Batch alter the storage medium of all partitions.

   ```sql
   ALTER TABLE example_db.my_table
   MODIFY PARTITION (*) SET("storage_medium"="HDD");

   ```

7. Drop a partition.

   ```sql
   ALTER TABLE example_db.my_table
   DROP PARTITION p1;

   ```

8. Add a partition that has upper and lower boundaries.

   ```sql
   ALTER TABLE example_db.my_table
   ADD PARTITION p1 VALUES [("2014-01-01"), ("2014-02-01"));

   ```

### Rollup[​](#rollup "Direct link to Rollup")

1. Create a rollup `example_rollup_index` based on the base index (k1,k2,k3,v1,v2). Column-based storage is used.

   ```sql
   ALTER TABLE example_db.my_table
   ADD ROLLUP example_rollup_index(k1, k3, v1, v2)
   PROPERTIES("storage_type"="column");

   ```

2. Create a rollup `example_rollup_index2` based on `example_rollup_index(k1,k3,v1,v2)`.

   ```sql
   ALTER TABLE example_db.my_table
   ADD ROLLUP example_rollup_index2 (k1, v1)
   FROM example_rollup_index;

   ```

3. Create a rollup `example_rollup_index3` based on the base index (k1, k2, k3, v1). The rollup timeout time is set to one hour.

   ```sql
   ALTER TABLE example_db.my_table
   ADD ROLLUP example_rollup_index3(k1, k3, v1)
   PROPERTIES("storage_type"="column", "timeout" = "3600");

   ```

4. Drop the rollup `example_rollup_index2`.

   ```sql
   ALTER TABLE example_db.my_table
   DROP ROLLUP example_rollup_index2;

   ```

### Column[​](#column "Direct link to Column")

1. Add a key column `new_col` (non-aggregate column) after the `col1` column of `example_rollup_index`.

   ```sql
   ALTER TABLE example_db.my_table
   ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
   TO example_rollup_index;

   ```

2. Add a value column `new_col` (non-aggregate column) after the `col1` column of `example_rollup_index`.

   ```sql
   ALTER TABLE example_db.my_table
   ADD COLUMN new_col INT DEFAULT "0" AFTER col1
   TO example_rollup_index;

   ```

3. Add a key column `new_col` (aggregate column) after the `col1` column of `example_rollup_index`.

   ```sql
   ALTER TABLE example_db.my_table
   ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
   TO example_rollup_index;

   ```

4. Add a value column `new_col SUM` (aggregate column) after the `col1` column of `example_rollup_index`.

   ```sql
   ALTER TABLE example_db.my_table
   ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
   TO example_rollup_index;

   ```

5. Add multiple columns to `example_rollup_index` (aggregate).

   ```sql
   ALTER TABLE example_db.my_table
   ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
   TO example_rollup_index;

   ```

6. Add multiple columns to `example_rollup_index` (aggregate) and specify the locations of the added columns using `AFTER`.

   ```sql
   ALTER TABLE example_db.my_table
   ADD COLUMN col1 INT DEFAULT "1" AFTER `k1`,
   ADD COLUMN col2 FLOAT SUM AFTER `v2`
   TO example_rollup_index;

   ```

7. Drop a column from `example_rollup_index`.

   ```sql
   ALTER TABLE example_db.my_table
   DROP COLUMN col2
   FROM example_rollup_index;

   ```

8. Modify the column type of col1 of the base index to BIGINT and put it after `col2`.

   ```sql
   ALTER TABLE example_db.my_table
   MODIFY COLUMN col1 BIGINT DEFAULT "1" AFTER col2;

   ```

9. Modify the maximum length of the `val1` column of the base index to 64. The original length is 32.

   ```sql
   ALTER TABLE example_db.my_table
   MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

   ```

10. Reorder the columns in `example_rollup_index`. The original column order is k1, k2, k3, v1, v2.

    ```sql
    ALTER TABLE example_db.my_table
    ORDER BY (k3,k1,k2,v2,v1)
    FROM example_rollup_index;

    ```

11. Perform two operations (ADD COLUMN and ORDER BY) at one time.

    ```sql
    ALTER TABLE example_db.my_table
    ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
    ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;

    ```

12. Alter the bloomfilter columns of the table.

    ```sql
    ALTER TABLE example_db.my_table
    SET ("bloom_filter_columns"="k1,k2,k3");

    ```

    This operation can also be merged into the above column operation (note that the syntax of multiple clauses is slightly different).

    ```sql
    ALTER TABLE example_db.my_table
    DROP COLUMN col2
    PROPERTIES ("bloom_filter_columns"="k1,k2,k3");

    ```

13. Modify the data type of multiple columns in a single statement.

    ```sql
    ALTER TABLE example_db.my_table
    MODIFY COLUMN k1 VARCHAR(100) KEY NOT NULL,
    MODIFY COLUMN v2 DOUBLE DEFAULT "1" AFTER v1;

    ```

14. Add and drop fields in STRUCT-type data.

    **Prerequisites**: Create a table and insert a row of data.

    ```sql
    CREATE TABLE struct_test(
        c0 INT,
        c1 STRUCT<v1 INT, v2 STRUCT<v4 INT, v5 INT>, v3 INT>,
        c2 STRUCT<v1 INT, v2 ARRAY<STRUCT<v3 INT, v4 STRUCT<v5 INT, v6 INT>>>>
    )
    DUPLICATE KEY(c0)
    DISTRIBUTED BY HASH(`c0`) BUCKETS 1
    PROPERTIES (
        "fast_schema_evolution" = "true"
    );
    INSERT INTO struct_test VALUES (
        1, 
        ROW(1, ROW(2, 3), 4), 
        ROW(5, [ROW(6, ROW(7, 8)), ROW(9, ROW(10, 11))])
    );

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v4":2,"v5":3},"v3":4}
    c2: {"v1":5,"v2":[{"v3":6,"v4":{"v5":7,"v6":8}},{"v3":9,"v4":{"v5":10,"v6":11}}]}

    ```

    * Add a new field to a STRUCT-type column.

    ```sql
    ALTER TABLE struct_test MODIFY COLUMN c1 ADD FIELD v4 INT AFTER v2;

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v4":2,"v5":3},"v4":null,"v3":4}
    c2: {"v1":5,"v2":[{"v3":6,"v4":{"v5":7,"v6":8}},{"v3":9,"v4":{"v5":10,"v6":11}}]}

    ```

    * Add a new field to a nested STRUCT type.

    ```sql
    ALTER TABLE struct_test MODIFY COLUMN c1 ADD FIELD v2.v6 INT FIRST;

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v6":null,"v4":2,"v5":3},"v4":null,"v3":4}
    c2: {"v1":5,"v2":[{"v3":6,"v4":{"v5":7,"v6":8}},{"v3":9,"v4":{"v5":10,"v6":11}}]}

    ```

    * Add a new field to a STRUCT type in an array.

    ```sql
    ALTER TABLE struct_test MODIFY COLUMN c2 ADD FIELD v2.[*].v7 INT AFTER v3;

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v6":null,"v4":2,"v5":3},"v4":null,"v3":4}
    c2: {"v1":5,"v2":[{"v3":6,"v7":null,"v4":{"v5":7,"v6":8}},{"v3":9,"v7":null,"v4":{"v5":10,"v6":11}}]}

    ```

    * Drop a field from a STRUCT-type column.

    ```sql
    ALTER TABLE struct_test MODIFY COLUMN c1 DROP FIELD v3;

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v6":null,"v4":2,"v5":3},"v4":null}
    c2: {"v1":5,"v2":[{"v3":6,"v7":null,"v4":{"v5":7,"v6":8}},{"v3":9,"v7":null,"v4":{"v5":10,"v6":11}}]}

    ```

    * Drop a field from a nested STRUCT type.

    ```sql
    ALTER TABLE struct_test MODIFY COLUMN c1 DROP FIELD v2.v4;

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v6":null,"v5":3},"v4":null}
    c2: {"v1":5,"v2":[{"v3":6,"v7":null,"v4":{"v5":7,"v6":8}},{"v3":9,"v7":null,"v4":{"v5":10,"v6":11}}]}

    ```

    * Drop a field from a STRUCT type in an array.

    ```sql
    ALTER TABLE struct_test MODIFY COLUMN c2 DROP FIELD v2.[*].v3;

    ```

    ```plain
    mysql> SELECT * FROM struct_test\G
    *************************** 1. row ***************************
    c0: 1
    c1: {"v1":1,"v2":{"v6":null,"v5":3},"v4":null}
    c2: {"v1":5,"v2":[{"v7":null,"v4":{"v5":7,"v6":8}},{"v7":null,"v4":{"v5":10,"v6":11}}]}

    ```

### Table property[​](#table-property "Direct link to Table property")

1. Alter the Colocate property of the table.

   ```sql
   ALTER TABLE example_db.my_table
   SET ("colocate_with" = "t1");

   ```

2. Alter the dynamic partition property of the table.

   ```sql
   ALTER TABLE example_db.my_table
   SET ("dynamic_partition.enable" = "false");

   ```

   If you need to add dynamic partition properties to a table with no dynamic partition properties configured, you need to specify all the dynamic partition properties.

   ```sql
   ALTER TABLE example_db.my_table
   SET (
       "dynamic_partition.enable" = "true",
       "dynamic_partition.time_unit" = "DAY",
       "dynamic_partition.end" = "3",
       "dynamic_partition.prefix" = "p",
       "dynamic_partition.buckets" = "32"
       );

   ```

3. Alter the storage medium property of the table.

   ```sql
   ALTER TABLE example_db.my_table SET("default.storage_medium"="SSD");

   ```

### Rename[​](#rename-1 "Direct link to Rename")

1. Rename `table1` to `table2`.

   ```sql
   ALTER TABLE table1 RENAME table2;

   ```

2. Rename rollup `rollup1` of `example_table` to `rollup2`.

   ```sql
   ALTER TABLE example_table RENAME ROLLUP rollup1 rollup2;

   ```

3. Rename partition `p1` of `example_table` to `p2`.

   ```sql
   ALTER TABLE example_table RENAME PARTITION p1 p2;

   ```

### Index[​](#index "Direct link to Index")

1. Create a bitmap index for column `siteid` in `table1`.

   ```sql
   ALTER TABLE table1
   ADD INDEX index_1 (siteid) USING BITMAP COMMENT 'site_id_bitmap';

   ```

2. Drop the bitmap index of column `siteid` in `table1`.

   ```sql
   ALTER TABLE table1
   DROP INDEX index_1;

   ```

### Swap[​](#swap-1 "Direct link to Swap")

Atomic swap between `table1` and `table2`.

```sql
ALTER TABLE table1 SWAP WITH table2

```

### Manual compaction[​](#manual-compaction "Direct link to Manual compaction")

```sql
CREATE TABLE compaction_test( 
    event_day DATE,
    pv BIGINT)
DUPLICATE KEY(event_day)
PARTITION BY date_trunc('month', event_day)
DISTRIBUTED BY HASH(event_day) BUCKETS 8
PROPERTIES("replication_num" = "3");

INSERT INTO compaction_test VALUES
('2023-02-14', 2),
('2033-03-01',2);
{'label':'insert_734648fa-c878-11ed-90d6-00163e0dcbfc', 'status':'VISIBLE', 'txnId':'5008'}

INSERT INTO compaction_test VALUES
('2023-02-14', 2),('2033-03-01',2);
{'label':'insert_85c95c1b-c878-11ed-90d6-00163e0dcbfc', 'status':'VISIBLE', 'txnId':'5009'}

ALTER TABLE compaction_test COMPACT;

ALTER TABLE compaction_test COMPACT p203303;

ALTER TABLE compaction_test COMPACT (p202302,p203303);

ALTER TABLE compaction_test CUMULATIVE COMPACT (p202302,p203303);

ALTER TABLE compaction_test BASE COMPACT (p202302,p203303);

```

### Drop Primary Key Persistent Index[​](#drop-primary-key-persistent-index "Direct link to Drop Primary Key Persistent Index")

Drop persistent index on tablets `100` and `101` for Primary Key table `db1.test_tbl` in a shared-data cluster.

```sql
ALTER TABLE db1.test_tbl DROP PERSISTENT INDEX ON TABLETS (100, 101);

```

### SPLIT or MERGE tablets[​](#split-or-merge-tablets "Direct link to SPLIT or MERGE tablets")

* Split all tablets that meet the conditions in the table to a target size of 10 GB (Default).

```sql
ALTER TABLE table1 SPLIT TABLETS;

```

* Split all tablets that meet the conditions in a partition.

```sql
ALTER TABLE table1 SPLIT TABLETS
PARTITION (p1);

```

* Split specific tablets by ID.

```sql
ALTER TABLE table1 SPLIT TABLETS
(9588955, 9588956, 9588957);

```

* Merge all tablets that meet the conditions in the table to a target size of 2 GB.

```sql
ALTER TABLE table1 MERGE TABLETS
PROPERTIES (
    "tablet_reshard_target_size"="2147483648");

```

* Merge all tablets that meet the conditions in specific partitions.

```sql
ALTER TABLE table1 MERGE TABLETS
PARTITIONS (p1, p2, p3)
PROPERTIES (
    "tablet_reshard_target_size"="2147483648");

```

* Merge specific tablets by ID.

```sql
ALTER TABLE table1 MERGE TABLETS
(9588955, 9588956, 9588957)
(9588958, 9588959);

```

## References[​](#references "Direct link to References")

* [CREATE TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md)
* [SHOW CREATE TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/SHOW_CREATE_TABLE.md)
* [SHOW TABLES](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLES.md)
* [SHOW ALTER TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md)
* [DROP TABLE](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/DROP_TABLE.md)