Outdated Version

You are viewing an older version of this section. View current production version.

ALTER TABLE

Modify the structure of an existing table.

Syntax

ALTER [ONLINE] TABLE tbl_name
  [alter_specification [, alter_specification] ...] [TIMEOUT timeout_value]

alter_specification:
    ADD [COLUMN] col_name { column_definition | AS computed_column_definition } [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition, ...)
  | ADD [UNIQUE] { INDEX | KEY } [index_name] [index_type] (index_col_name, ...) [index_option] ...
  | DROP [COLUMN] col_name
  | DROP { INDEX | KEY } index_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | CHANGE old_col_name new_col_name
  | RENAME [TO | AS] new_tbl_name
  | MODIFY { INDEX | KEY } index_name SET ([index_kv_option], ...)
  | AUTOSTATS_ENABLED = {ON|OFF}
  | AUTOSTATS_CARDINALITY_MODE = {INCREMENTAL|PERIODIC|OFF}
  | AUTOSTATS_HISTOGRAM_MODE = {CREATE|UPDATE|OFF}
  | AUTOSTATS_SAMPLING = {ON|OFF}
  | AUTO_INCREMENT [=] new_value
  | COMPRESSION [=] { NONE | SPARSE }

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [COMMENT 'string'] [SERIES TIMESTAMP] [SPARSE]

computed_column_definition:
    computed_column_expression PERSISTED data_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING { BTREE | HASH }

index_option:
    KEY_BLOCK_SIZE [=] value
  | BUCKET_COUNT [=] value
  | index_type
  | WITH ([index_kv_option], ...)
  | UNENFORCED [RELY | NORELY]

index_kv_option:
    RESOLUTION = value
  | COLUMNSTORE_SEGMENT_ROWS = value
  | COLUMNSTORE_FLUSH_BYTES = value

Remarks

SingleStore DB supports online ALTER TABLE, which means that you can read and write while the table is being altered. ALTER TABLE on a sharded table is always executed online. Note that online ALTER TABLE will not begin modifying the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before modifying the structure of the table, and ensures consistency of results from queries on the table since the time of execution of ALTER TABLE. As soon as the in-progress reads and writes complete and the ALTER TABLE command begins modifying the table, new reads and writes will proceed as normal. This blocking period usually lasts approximately for milliseconds.

If you are running frequent ALTER TABLE statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since it blocks other queries from starting while it waits for completion of long-running queries.

Refer to the Query Errors topic for resolving query timeout errors due to long running queries in a workload.
Online ALTER TABLE in SingleStore DB cannot be rolled back, so operations that may result in errors are disallowed. For example, you cannot add unique indexes with online ALTER TABLE because there may be duplicate keys in the indexed column.
Use ALTER TABLE CHANGE to rename a column.
ALTER OFFLINE TABLE is supported on REFERENCE tables only. ALTER OFFLINE TABLE operations will cause write queries against the table to fail until the ALTER TABLE completes. Read queries can still be executed against the table as if it were in the state immediately before the ALTER TABLE operation began executing. If the user does not specify whether to do the ALTER TABLE operation online or offline, SingleStore DB will do it online if possible. Note that offline ALTER TABLE completes more quickly than online, but offline blocks writes for as long as the command is executing.
ALTER TABLE must be run on the master aggregator node (see Node Requirements for SingleStore DB Commands).
Columns used to construct a view cannot be dropped. See CREATE VIEW for more information.
computed_column_expression defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof. For more information see Persistent Computed Columns.
On columnstore tables, adding columns, dropping columns, and changing column names are supported, but modifying column types is unsupported.
Only hash indexes can be added or dropped on columnstore tables. These hash indexes are non-unique and single-column, only.
ALTER TABLE tbl_name MODIFY KEY SET (...) modifies configuration variables associated with the index. It is currently only supported on columnstore indexes. See Advanced Columnstore Configuration Options for more information.
ALTER TABLE tbl_name { ENABLE | DISABLE } AUTOSTATS enables or disables automatic statistics collection on the table. There are three categories of autostats - AUTOSTATS_CARDINALITY_MODE, AUTOSTATS_HISTOGRAM_MODE, and AUTOSTATS_SAMPLING. SingleStore DB allows you to independently control how each category of statistics is automatically gathered. Multiple autostats settings can be combined in a single ALTER TABLE statement. See Automatic Statistics for more information.
ALTER TABLE tbl_name AUTO_INCREMENT [=] new_value sets the next auto_increment value that will be generated. It can only be used with reference tables and to increase the internal auto_increment counter. If new_value is smaller than the current value, a warning is shown, displaying the current value.
Altering PRIMARY, SHARD, or UNIQUE keys is unsupported.
TIMEOUT, a value specified in milliseconds, determines the length of time to wait for the ALTER TABLE command to commit across the cluster. If not specified, the default_distributed_ddl_timeout global variable value is used.
The SERIES TIMESTAMP clause specifies a table column as the default timestamp column. This setting can be specified only for a single table column. The column can be either of the following data types: DATE, TIME, DATETIME, or TIMESTAMP. It is recommended to use either DATETIME or DATETIME(6) data type instead of TIMESTAMP because the automatic update behavior of TIMESTAMP is subject to change. See Timestamp Behavior for details.
The SERIES TIMESTAMP clause does not affect the data type of a table column, rather it specifies the behavior of the column in the time series specific functions like FIRST(), LAST(), and TIME_BUCKET.
For an example of how to modify a rowstore table’s columns to use sparse compression, see Example 3. Also, see CREATE TABLE for a general discussion on sparse rowstore compression.
Adding an index on a table having the COMPRESSION = SPARSE table option will store all key columns in their non-sparse format.
Adding an index on a column having the SPARSE column option is not allowed.
Dropping an index on a table having the COMPRESSION = SPARSE option will, for each column, store the column as sparse if possible.
If a table was created with the SPARSE option on individual columns, you will not be able to modify the COMPRESSION = SPARSE table option, and vice-versa. For example, if a table is created using the statement CREATE TABLE t (a INT, b VARCHAR(50) SPARSE, c DATETIME);, you would not be able to run ALTER TABLE t COMPRESSION = SPARSE; successfully.

Info

Successful completion of a new ALTER TABLE will invalidate the plancache for all queries that operate on the affected table, requiring their recompilation.

Examples

Example 1

The following example creates a table with three columns, then alters the table to add a fourth column called middle_initial.

CREATE TABLE customer(
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    birth_date DATE);

INSERT INTO customer VALUES
  ("Joe", "Williams", "1960/1/2"),
  ("Esther", "Johnson", "1962/7/19");

SELECT * FROM customer;
****
+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Esther     | Johnson   | 1962-07-19 |
| Joe        | Williams  | 1960-01-02 |
+------------+-----------+------------+

ALTER TABLE customer ADD column middle_initial CHAR(1);

SELECT * FROM customer;
****
+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther     | Johnson   | 1962-07-19 | NULL           |
| Joe        | Williams  | 1960-01-02 | NULL           |
+------------+-----------+------------+----------------+

UPDATE customer SET middle_initial = "T" WHERE first_name = "Esther";

SELECT * FROM customer;
****
+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther     | Johnson   | 1962-07-19 | T              |
| Joe        | Williams  | 1960-01-02 | NULL           |
+------------+-----------+------------+----------------+

Example 2

The following example uses the customer table from Example 1. This example changes (renames) the middle_initial column to middle_name.

ALTER TABLE customer change middle_initial middle_name;

ALTER TABLE customer modify middle_name VARCHAR(30);

SELECT * FROM customer;
****
+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther     | Johnson   | 1962-07-19 | T           |
| Joe        | Williams  | 1960-01-02 | NULL        |
+------------+-----------+------------+-------------+

UPDATE customer SET middle_name = "Wallace" WHERE last_name = "Williams";

SELECT * FROM customer;
****
+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther     | Johnson   | 1962-07-19 | T           |
| Joe        | Williams  | 1960-01-02 | Wallace     |
+------------+-----------+------------+-------------+

Example 3

Example 3a

The following example demonstrates how to modify existing columns in a table. In this example, column datatypes are changed to use or not to use sparse compression.

Create a table with three columns. Column b is a sparse column.

CREATE TABLE sparse_demo (a INT, b VARCHAR(50) SPARSE, c DATETIME);

Modify column a to use sparse compression. Modify column b to use non-sparse compression.

ALTER TABLE sparse_demo MODIFY COLUMN a INT SPARSE;

ALTER TABLE sparse_demo MODIFY COLUMN b VARCHAR(50);

See the results:

SELECT column_name, is_sparse FROM information_schema.columns
WHERE table_name = 'sparse_demo';
****
+-------------+-----------+
| column_name | is_sparse |
+-------------+-----------+
| a           | YES       |
| b           | NO        |
| c           | NO        |
+-------------+-----------+

Example 3b

The following example demonstrates how to modify all of the columns in the table to use sparse compression.

Create a table with three non-sparse columns.

CREATE TABLE sparse_demo2 (a INT, b VARCHAR(50), c DATETIME);

ALTER TABLE sparse_demo2 COMPRESSION = SPARSE;