Purpose
This statement is used to modify the structure of an existing table, such as altering the table and its attributes, adding columns, modifying columns and their attributes, and dropping columns.
Syntax
alter_table_stmt:
ALTER TABLE table_name alter_table_action_list;
alter_table_action_list:
alter_table_action [, alter_table_action ...]
alter_table_action:
ADD [COLUMN] column_definition
[ opt_position_column ]
| ADD [COLUMN] (column_definition_list)
| ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY}
[index_name] index_desc
| ADD [CONSTRAINT [constraint_name]] FOREIGN KEY
[index_name] index_desc
REFERENCES reference_definition
[match_action][opt_reference_option_list]
| ADD PRIMARY KEY (column_name)
| ADD CONSTRAINT [constraint_name] CHECK (expression)[[NOT] ENFORCED]
| ADD PARTITION (range_partition_list)
| ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option]
| ADD [ASYNC] FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...]) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)] [FTS_INDEX_TYPE [=] {MATCH | PHRASE_MATCH}] [LOCAL]
| ADD [ASYNC] SEARCH INDEX [index_name] (search_index_column [, search_index_column ...])
| ADD COLUMN GROUP([all columns, ]each column) [DELAYED]
| ALTER [COLUMN] column_name {
SET DEFAULT const_value
| DROP DEFAULT
}
| ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
| ALTER INDEX index_name [VISIBLE | INVISIBLE] [STORAGE_CACHE_POLICY = {"hot"|"auto"|"none"}]
| ALTER INDEX index_name
[VISIBLE | INVISIBLE]
| CHANGE [COLUMN] column_name column_definition
| DROP [COLUMN] column_name [, DROP COLUMN column_name ...]
| DROP {PARTITION | SUBPARTITION} partition_name_list
| DROP TABLEGROUP
| DROP FOREIGN KEY fk_name
| DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
| DROP CHECK constraint_name
| DROP COLUMN GROUP([all columns, ]each column)
| DROP CLUSTERING KEY
| MODIFY [COLUMN] column_definition
| RENAME COLUMN old_col_name [TO|AS] new_col_name
| RENAME [TO|AS] table_name
| RENAME {INDEX | KEY} old_index_name [TO|AS] new_index_name
| [SET] table_option_list
| TRUNCATE {PARTITION | SUBPARTITION} partition_name_list
| EXCHANGE PARTITION partition_name WITH TABLE origin_table_name WITHOUT VALIDATION
| REORGANIZE PARTITION partition_name INTO (split_range_partition_list)
| PARTITION BY {RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] [range_partition_list]
| partition_option}
| MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)
| ALTER {PARTITION | SUBPARTITION} partition_name STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}
| FORCE
| REMOVE TTL
| {ENABLE | DISABLE} ROW LEVEL SECURITY
| {FORCE | NO FORCE} ROW LEVEL SECURITY
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [COMMENT string_value] [SKIP_INDEX(skip_index_option_list)] [ opt_position_column ]
skip_index_option_list:
skip_index_option [,skip_index_option ...]
skip_index_option:
MIN_MAX
| SUM
opt_position_column:
FIRST | BEFORE | AFTER column_name
index_desc:
(column_desc_list) [index_type] [index_option_list]
match_action:
MATCH {SIMPLE | FULL | PARTIAL}
opt_reference_option_list:
reference_option [,reference_option ...]
reference_option:
ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [(length)] [ASC | DESC]
key_part:
{index_col_name [(length)] | (expr)} [ASC | DESC]
search_index_column:
column_name [WITH (search_col_option_list)]
search_col_option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
index_type:
USING BTREE
index_option_list:
index_option [ index_option ...]
index_option:
[GLOBAL | LOCAL]
| block_size
| compression
| STORING(column_name_list)
| COMMENT string_value
| STORAGE_CACHE_POLICY (storage_cache_policy_option)
index_column_group_option:
WITH COLUMN GROUP([all columns, ]each column)
tokenizer_option:
SPACE
| NGRAM
| BENG
| IK
| NGRAM2
| analyzer
parser_properties_list:
parser_properties, [parser_properties]
parser_properties:
min_token_size = int_value
| max_token_size = int_value
| ngram_token_size = int_value
| ik_mode = 'char_value'
| min_ngram_size = int_value
| max_ngram_size = int_value
| analysis = json_string
table_option_list:
table_option [ table_option ...]
table_option:
TABLEGROUP = tablegroup_name
| block_size
| lob_inrow_threshold [=] num
| compression
| AUTO_INCREMENT [=] INT_VALUE
| COMMENT string_value
| parallel_clause
| TABLE_MODE [=] 'table_mode_value'
| AUTO_INCREMENT_CACHE_SIZE [=] INT_VALUE
| READ {ONLY | WRITE}
| DUPLICATE_SCOPE= 'none | cluster'
| enable_macro_block_bloom_filter [=] {True | False}
| DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
| SEMISTRUCT_ENCODING_TYPE [=] 'encoding' # Replaced by SEMISTRUCT_PROPERTIES.
| SEMISTRUCT_PROPERTIES [=] (encoding_type=encoding|none[,freq_threshold=INT_VALUE])
| STORAGE_CACHE_POLICY (storage_cache_policy_option)
| CLUSTER BY (column_name_list)
| HMS_CATALOG_NAME [=] string_value
| COLUMN_NAME_CASE_SENSITIVE [=] {True | False}
| DELTA_FORMAT [=] 'flat | encoding'
| SKIP_INDEX_LEVEL [=] {1 | 0}
| TTL col_name + INTERVAL interval_num ttl_unit BY COMPACTION
| ASYNC_INDEX_PARAMS [=] 'async_index_params_string'
parallel_clause:
{NOPARALLEL | PARALLEL integer}
table_mode_value:
NORMAL
| QUEUING
| MODERATE
| SUPER
| EXTREME
dynamic_partition_policy_list:
dynamic_partition_policy_option [, dynamic_partition_policy_option ...]
dynamic_partition_policy_option:
ENABLE = {true | false}
| PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
| EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
partition_option:
PARTITION BY HASH(expression)
[subpartition_option] PARTITIONS partition_count
| PARTITION BY KEY([column_name_list])
[subpartition_option] PARTITIONS partition_count
| PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
[subpartition_option] (range_partition_list)
subpartition_option:
SUBPARTITION BY HASH(expression)
SUBPARTITIONS subpartition_count
| SUBPARTITION BY KEY(column_name_list)
SUBPARTITIONS subpartition_count
| SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
(range_subpartition_list)
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "cold" | "none"}
| timeline_strategy_list
timeline_strategy_list:
BOUNDARY_COLUMN = column_name
| BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
| HOT_RETENTION = intnum retention_time_unit
| MIXED_RETENTION = intnum retention_time_unit
| GRANULARITY = "partition"
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
split_range_partition_list:
PARTITION split_partition_name VALUES LESS THAN (value)
[, PARTITION split_partition_name VALUES LESS THAN (value) ...]
, PARTITION split_partition_name VALUES LESS THAN (source_value)
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION partition_name
VALUES LESS THAN {(expression_list) | MAXVALUE}
[STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]
range_subpartition_list:
range_subpartition [, range_subpartition ...]
range_subpartition:
SUBPARTITION subpartition_name
VALUES LESS THAN {(expression_list) | MAXVALUE}
[STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]
expression_list:
expression [, expression ...]
column_name_list:
column_name [, column_name ...]
partition_name_list:
partition_name [, partition_name ...]
partition_count | subpartition_count:
INT_VALUE
add_subpartition_option:
{SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
[, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
| {SUBPARTITION subpartition_name VALUES IN list_partition_expr
[, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}
Parameters
Parameters |
Description |
|---|---|
| ADD [COLUMN] | Supports adding generated columns. |
| [FIRST \ | BEFORE \ | AFTER column_name] | Add the new column as the first column of the table or column_name Before/after the column. Currently, OceanBase AI Database supports setting the column position only in the ADD COLUMN syntax. |
| CHANGE [COLUMN] | You can rename a column and modify its definition, but only by adding specific character data types. VARCHAR, VARBINARY, CHAR(the length of the data, etc.) |
| MODIFY [COLUMN] | Modifies column attributes. |
| ALTER [COLUMN] {SET DEFAULT const_value \ | DROP DEFAULT} | Modifies the default value of a column. |
| DROP [COLUMN] | Delete a column.
NoticePrimary key columns cannot be deleted. |
| ADD FOREIGN KEY | Add a foreign key. If the foreign key name is not specified, the table name will be used. OBFK+ Create a time-based name. (For example, the foreign key created for the t1 table at 00:00:00 on August 1, 2021 is named t1_OBFK_1627747200000000) Foreign keys enable cross-table reference of related data. When an UPDATE or DELETE operation affects the key value in the parent table that matches a row in the child table,e, the result depends on ON UPDATE and ON DELETE Reference operations for clauses:
SET DEFAULT Operation. |
| ADD PRIMARY KEY | Adds a primary key. You can specify one or more columns as the primary key. If multiple columns are specified, they form a composite primary key. |
| ADD FULLTEXT [INDEX \ | KEY] | Create a full-text index. For more information, see the Create a full-text index section in Create an index. |
| ADD ASYNC FULLTEXT [INDEX \ | KEY] | Add asynchronous full-text indexes. In FULLTEXT Added before ASYNC Keywords. ASYNC Can only be used with FULLTEXT or SEARCH It cannot be used alone or for regular or unique indexes. It is only supported on heap tables (ORGANIZATION = HEAP). The same index type on the same table does not support both synchronous and asynchronous indexes. The tokenizer parameters are the same as those for a synchronous full-text index. This feature is supported starting from V4.6.2.1. For more information, including descriptions and examples, see the "Asynchronous Full-Text Index" section in Asynchronous Full-Text Index. |
| ADD SEARCH INDEX | Add a search index. Syntax: ADD [ASYNC] SEARCH INDEX [index_name] (search_index_column [, search_index_column ...]). Among them, search_index_column Supported column_name [WITH (...)]; WITH Applicable only to JSON columns. Supported values: INCLUDE_PATHS, EXCLUDE_PATHS(II), INCLUDE_TYPES and NESTED_PATHS. For more information about column-level options, see CREATE INDEX in the "Search Index Parameters" section. |
| ADD ASYNC SEARCH INDEX | Add asynchronous search index. Add ASYNC before the SEARCH keyword. Searching indexes synchronously and asynchronously on the same table are mutually exclusive. This feature is supported starting from V4.6.2.1. For more information, see Search index in the "Search indexes asynchronously" section. |
| WITH PARSER tokenizer_option | Optional. Specifies the tokenizer for the full-text index. For more information, see tokenizer_option. |
| PARSER_PROPERTIES[=](parser_properties_list) | Optional. Specifies the attributes of the parser. For more information, see parser_properties. |
| FTS_INDEX_TYPE [=] {MATCH \ | PHRASE_MATCH} | Optional. Specifies the type of the full-text index. MATCH Indicates a term matching index, which supports matching and scoring based on the BM25 algorithm. The stored content includes tokens, document IDs, word frequencies, document lengths, and other information. PHRASE_MATCH indicates the phrase match index, which can support phrase queries more efficiently. It is a functional superset of MATCH and also stores the position list of words. |
| ALTER INDEX | Modify index visibility. When the index status is INVISIBLE, the SQL optimizer will not choose this index. |
| key_part | Create (function) indexes. |
| index_col_name | Specify the column names for the index. Each column name can be sorted in ascending order (ASC), but not in descending order (DESC). The default is ascending order. The sorting method for creating the index is as follows: First, the values in the first column of index_col_name are sorted first. For records with the same value in this column, the values in the next column are used for sorting, and so on. |
| expr | Represents a valid function-based index expression, which can be a boolean expression, for example: c1=c1.Notice Creating functional indexes on generated columns is prohibited in the current version of OceanBase AI Database. |
| ADD [PARTITION] | Adds a partition to a partitioned table. |
| DROP {PARTITION \ | SUBPARTITION} | Delete partition:
|
| TRUNCATE {PARTITION \ | SUBPARTITION} | Delete partition data:
Notice When deleting partition data, please try to ensure that no active transactions or queries exist on that partition. Otherwise, it may cause SQL statement errors or other exceptions. |
| RENAME COLUMN old_col_name [TO\ | AS] new_col_name | Renames a column. Only the column name is modified; the column definition remains unchanged.
Notice
|
| RENAME [TO\ | AS] table_name | Rename a table.
NoticeDuring the |
| RENAME {INDEX \ | KEY} | Rename an index or key. |
| DROP [TABLEGROUP] | Drop a table group. |
| DROP [FOREIGN KEY] | Drop the foreign key. |
| DROP [PRIMARY KEY] | Delete the primary key.
NoteThe primary key cannot be deleted in the following cases:
|
| [SET] table_option | To set table-level attributes, you can specify the following parameters:
|
| CHECK | Change CHECK Constraints. The following operations are supported:
|
| [NOT] ENFORCED | Whether to enforce the name of constraint_name of the CHECK Constraints.
|
| ADD COLUMN GROUP([all columns, ]each column) [DELAYED] | Converts a table into a columnar storage table. The specific options are as follows:
|
| DROP COLUMN GROUP([all columns, ]each column) | Removes the storage format of a table. The specific explanations are as follows:
|
| DROP CLUSTERING KEY | This statement is used to drop a clustered column of a table.
Note
|
| index_column_group_option | Specifies the index options. The specific descriptions are as follows:
|
| SKIP_INDEX | Modifies the skip index attribute of a column. Valid values:
Notice
|
| EXCHANGE PARTITION partition_name WITH TABLE origin_table_name | Specifies the partition to exchange. Wherein, partition_name Represents the partition name of a partitioned table during partition exchange. origin_table_name Represents the name of the source table in a partition exchange. It can be a non-partitioned table or a partitioned table with only one partition. For more information about partition exchange, see Partition exchange. |
| TABLE_MODE | Optional. Specifies the major compaction trigger threshold and strategy, which controls the major compaction behavior after data minor compaction. For detailed information about the valid values, see table_mode_value. |
| AUTO_INCREMENT_CACHE_SIZE | Specifies the number of auto-increment values cached. The default value is 0, which means this parameter is not configured. In this case, the system uses the tenant-level parameter (auto_increment_cache_size) as the cache size for auto-increment columns. |
| READ {ONLY \ | WRITE} | Specify the read and write permissions for the table. The details are as follows:
|
| DUPLICATE_SCOPE | Used to modify the attributes of a replicated table.
|
| enable_macro_block_bloom_filter [=] {True \ | False} | Specifies whether to persist the macroblock-level Bloom filter. Valid values:
|
| DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list) | Modifies the dynamic partition management attributes of a table. dynamic_partition_policy_list List of configurable parameters for the dynamic partitioning strategy. Separate parameters with commas. For more information, see dynamic_partition_policy_option. |
| REORGANIZE PARTITION | It is used to manually split a partition. The relevant parameters are as follows:
NoticeIn OceanBase AI Database, the current version only supports splitting one partition into multiple partitions; it does not support splitting multiple partitions into multiple partitions. |
| PARTITION BY | This statement is used to modify the attributes of an automatic partitioning table or its partitioning rules. The relevant parameters are as follows:
|
| MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option) | Adds a subpartition.
NoticeAdding a subpartition is not supported when the subpartition type is HASH or KEY. |
| add_subpartition_option | Represents the definition of a new subpartition. |
| range_partition_expr | Represents the RANGE/RANGE COLUMNS partitioning expression. |
| list_partition_expr | Represents the LIST or LIST COLUMNS partitioning expression. |
| FORCE | Drop obsolete columns.
NoteWhen columns are dropped, they still occupy physical storage space even if they are no longer used. To remove these obsolete columns and reclaim the associated space, you need to drop them. |
| ENABLE ROW LEVEL SECURITY | Row-level security (RLS) is enabled. By default, it is disabled for newly created tables. |
| DISABLE ROW LEVEL SECURITY | RLS is disabled. Policy definitions on the table are retained but do not take effect. |
| FORCE ROW LEVEL SECURITY | Table owners are also subject to RLS restrictions. By default, a newly created table is NO FORCE. |
| NO FORCE ROW LEVEL SECURITY | Default value. Not mandatory for table owners: when ENABLE is already set, the table owner still bypasses RLS for that table. It can also be used to revoke previous FORCE, restore owner exemption. |
| REMOVE TTL | Indicates the TTL strategy for dropping a table. |
| SEMISTRUCT_ENCODING_TYPE | Optional. Specifies the semi-structured encoding type. Valid values:
Notice
|
| COLUMN_NAME_CASE_SENSITIVE [=] {True \ | False} | Specifies whether the generated column name is case-sensitive.
|
| DELTA_FORMAT [=] 'flat \ | encoding' | Specifies the storage format for incremental data. Valid values:
NoteModifying the |
| SKIP_INDEX_LEVEL [=] {1 \ | 0} | Specifies whether to generate skip index aggregation information for incremental SSTables based on the baseline behavior. Valid values:
|
| TTL col_name + INTERVAL interval_num ttl_unit BY COMPACTION | Modify the table's TTL strategy. The details are as follows:
|
| SEMISTRUCT_PROPERTIES | Optional. Specifies the semi-structured encoding type, which must be in key-value pairs. Valid values:
For more information, see Use semi-structured encoding. |
| ASYNC_INDEX_PARAMS [=] 'async_index_params_string' | Optional. Sets table-level parameters related to asynchronous indexes. Specify the parameters in key-value pair strings, separated by commas (multiple keys are separated with commas). You only need to specify the keys to be modified; unspecified keys will retain their original values. The currently supported keys are as follows:
NoteThe above parameters are supported starting from V4.6.2.1. |
| STORAGE_CACHE_POLICY | Optional. Specifies the hot and cold storage strategy for tables and indexes in shared storage mode. For more information, see storage_cache_policy. |
| ALTER {PARTITION \ | SUBPARTITION} partition_name STORAGE_CACHE_POLICY | Specifies the hot/cold storage policy for a partition in shared storage mode. For more information, see partition_storage_cache_policy. |
| CLUSTER BY (column_name_list) | This statement is used to add or modify clustered columns. For more information about setting clustered columns, see the Modify clustered columns of an existing table section in Modify a table.
NoticeModifying clustered columns of an existing table triggers data reorganization, which may take a long time. Perform this operation during off-peak hours. |
| HMS_CATALOG_NAME [=] string_value | Specifies the name of the catalog to access when connecting to the Hive Metastore Service (HMS). |
table_mode_value
Note
In the TABLE_MODE modes listed below, all modes except for NORMAL mode represent QUEUING tables. This type of QUEUING table is the most basic table type, and the several modes listed later (except for NORMAL mode) represent more aggressive major compaction strategies.
NORMAL: The default value, indicating normal. In this mode, the probability of triggering a major compaction after a minor compaction is extremely low.QUEUING: In this mode, the probability of triggering a major compaction after a minor compaction is low.MODERATE: Moderate. In this mode, the probability of triggering a major compaction after a minor compaction is moderate.SUPER: Super. In this mode, a major compaction is more likely to be triggered after a minor compaction.EXTREME: Extreme. In this mode, the probability of triggering a major compaction after a minor compaction is higher.
For more information about major compactions, see Adaptive compaction.
tokenizer_option
SPACE: The default value. Indicates that the text is segmented by spaces. The following attributes can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] NGRAM: Indicates the word segmentation method based on N-gram (Chinese). The following attributes can be specified:AttributeValue rangengram_token_size [1, 10] NGRAM2: Indicates that the text is split into consecutive characters within the range ofmin_ngram_sizetomax_ngram_size. The following attributes can be specified:AttributeValue rangemin_ngram_size [1, 16] max_ngram_size [1, 16] BENG: word segmentation based on Beng (Basic English). The following attributes can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] IK: Indicates the word segmentation method based on IK (Chinese). Currently, only theutf-8character set is supported. The following attributes can be specified:AttributeValue rangeik_mode smartmax_word
analyzer: the text analyzer (Analyzer). It is used through theanalysisparameter inPARSER_PROPERTIES.
You can use the TOKENIZE function to view the tokenization results of a text string using the specified tokenizer and JSON-formatted parameters.
parser_properties
min_token_size: The minimum token length. The default value is 3, and the valid range is 1 to 16.max_token_size: the maximum token length. The default value is 84, and the valid range is 10 to 84.ngram_token_size: The token length forNGRAM. This parameter is valid only for theNGRAMtokenizer. The default value is 2, and the valid range is 1 to 10.ik_mode: the stemming mode of theIKstemmer. Valid values:smart: The default value. This mode prioritizes identifying longer words, reducing the number of splits and ensuring each character belongs to only one word. It also attempts to merge numerals with measure words into a single word in the output.max_word: Outputs recognizable words as much as possible. Longer words and the shorter words within them are retained. The same character can appear in multiple words.
min_ngram_size: Only valid for theNGRAM2tokenizer. Specifies the minimum number of characters for a generated n-gram. Value range:[1, 16].max_ngram_size: Only valid for theNGRAM2tokenizer. Specifies the maximum number of characters for a generated n-gram. The value range is[1, 16]. The tokenizer generates consecutive character segments with lengths between the minimum and maximum values.analysis: Valid only whenWITH PARSER analyzeris specified. Specifies the JSON definition string for the analyzer. Built-in analyzers can be abbreviated as{"analyzer": "standard"}and so on. For a complete list of built-in types and their corresponding languages, see theanalyzer_typeparameter description in TOKENIZE.
Example:
Create table
tbl1.CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);Add a full-text index named
fidxto tabletbl1.ALTER TABLE tbl1 ADD FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word');Add a full-text index using the built-in
englishanalyzer to thearticlestable.ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_body(body) WITH PARSER analyzer PARSER_PROPERTIES=(analysis='{"analyzer": "english"}');
dynamic_partition_policy_option
ENABLE = {true | false}: Specifies whether to enable dynamic partition management. Valid values:true: The default value. It specifies to enable dynamic partition management.false: Indicates that dynamic partition management is disabled.
PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Specifies the precreation time. When you schedule a dynamic partition management task, partitions are precreated so that max_partition_upper_bound > now() + precreate_time. Valid values:-1: The default value, which indicates not to pre-create partitions.0: Indicates that only the current partition is to be pre-created.n {hour | day | week | month | year}: Indicates to pre-create partitions for the specified time span. For example,3 hourmeans to pre-create partitions for the next 3 hours.
Note
- If multiple partitions need to be pre-created, the interval between partition boundaries is
TIME_UNIT. - The boundary of the first pre-created partition is the existing maximum partition boundary rounded up by
TIME_UNIT.
EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the partition expiration time. When dynamic partition management is scheduled, all partitions whose upper_bound < now() - expire_time are deleted. This parameter can be modified. Valid values:-1: The default value, indicating that the partition never expires.0: Indicates that all partitions except the current one have expired.n {hour | day | week | month | year}: the partition expiration time. For example,1 dayindicates the partition expires in 1 day.
For more information about modifying a dynamically partitioned table, see Modify a dynamic partitioned table.
Example:
ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
ENABLE = true,
PRECREATE_TIME = '1 day',
EXPIRE_TIME = '-1'
);
storage_cache_policy
Different attributes in storage_cache_policy_option are defined in Key-Value format. The semantics of each attribute are as follows:
GLOBAL = {"hot" | "auto" | "cold" | "none"}: Specifies the hot, auto, cold, or none storage strategy for all table data. Valid values:hot: Indicates that all data in the specified table is hot data. If sufficient cache disk space is available, all data under the table will be cached to the local cloud disk.auto: Indicates that the system automatically identifies the hot data for the specified table.cold: Indicates that the data of the specified table is cold data and will not be cached to the local cache disk. For data that has already been persisted to the cache, cache eviction will be actively triggered. Subsequent accesses to the table data can be stored in the macroblock memory cache and will not be persisted to the macroblock cache.none: Indicates that the index's policy follows the value of the primary table'sSTORAGE_CACHE_POLICY.Notice
The
noneattribute can only be used for indexes.
timeline_strategy_list: The list of timeline strategy parameters, separated by commas. Timeline strategies use time to determine whether partition data is stored in hot or cold storage. The system automatically adjusts the data on local cache disks based on the configured strategies.Note
Timeline: Hot data is cached based on the partitioning range defined by RANGE partitioning. When certain time conditions are met, the data in that partition is identified as hot data. When using the timeline strategy, note the following considerations:
- Only supported for RANGE-partitioned tables (RANGE partitioning can be at the primary or secondary level), because time is required to determine whether a partition has expired. However, it cannot be a dual RANGE partition, as it would be impossible to determine which RANGE partition's time to use.
- When using the timeline strategy, the partition expression can only contain column names; expression conversion is not supported. For example,
PARTITION BY RANGE COLUMNS(expr(col3)). BOUNDARY_COLUMNmust be the partitioning key. If the partitioning key consists of multiple columns,BOUNDARY_COLUMNmust be the first column, used to determine whether the partition has expired.
BOUNDARY_COLUMN = column_name: Identifies the column for hot data detection. Supports integer types (BIGINTorINT, in Unix timestamp format) and time types (TIMESTAMP,DATE,DATETIME, orYEAR).- If the
BOUNDARY_COLUMNtype is integer, the table's partitioning method supports RANGE and RANGE COLUMNS partitioning types. - If
BOUNDARY_COLUMNis of a time type, the table can only use the RANGE COLUMNS partitioning method.
- If the
BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: Specifies the time unit for theBOUNDARY_COLUMNparameter. This parameter is supported only whenBOUNDARY_COLUMNis an integer. When an integer value is used as a timestamp, a timestamp unit must be specified; otherwise, it may lead to incorrect time parsing. Valid values:When the partitioning column is of type
INT, the value ofBOUNDARY_COLUMN_UNITcan only bes.When the partitioning column is of the
BIGINTtype, the value ofBOUNDARY_COLUMN_UNITcan besorms.Notice
If the format is not a Unix timestamp, the time represented by the
INTtype cannot be correctly identified.
HOT_RETENTION = intnum retention_time_unit: Specifies the time range for hot data.intnum: an integer.retention_time_unit: the time unit. Valid values:YEAR: the year.MONTH: the month.WEEK: week.DAY: day.HOUR: the hour.MINUTE: the number of minutes.
Notice
You must set at least one of the parameters
HOT_RETENTIONandMIXED_RETENTIONin the timeline syntax.MIXED_RETENTION = intnum retention_time_unit:- If
MIXED_RETENTIONis set, data within the time range ofHOT_RETENTION + MIXED_RETENTIONis consideredcolddata. - If
MIXED_RETENTIONis not set, the data remainsautoafter reachingHOT_RETENTION. - If
HOT_RETENTIONis not set, the time range of [0, MIXED_RETENTION] representsautodata, and the data afterMIXED_RETENTIONrepresentscolddata.
- If
GRANULARITY = "partition": Optional. Specifies the operation granularity for hot and cold data storage, indicating that caching data is handled at the partition level.
partition_storage_cache_policy
STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}: Specifies the hot, warm, or cold storage policy for partition data. Valid values:
hot: specifies the partition as a hot storage. If there is sufficient cache disk space, all data in the partition will be cached to the local cloud disk.auto: Indicates that the system automatically identifies hot data in the specified partition.cold: Indicates that the data in the specified partition is cold data and will not be cached to the local cache disk. The system will proactively trigger cache eviction for data already persisted to the cache. Subsequent table data accesses can be stored in macroblock memory cache without being persisted to the macroblock cache.none: The default value. This means the partition's policy follows the value of the primary table'sSTORAGE_CACHE_POLICYfield.
Examples
Add and drop columns
Create the sample table tbl1 and execute the DESCRIBE command to view its information.
obclient> CREATE TABLE tbl1 (c1 INT(11) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+------------+----------+--------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+----------+--------+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50)| YES | | NULL | |
+-------+------------+----------+--------+---------+-------+
Add the
c3column and execute theDESCRIBEcommand to view table information.obclient> ALTER TABLE tbl1 ADD c3 INT; Query OK, 1 row affected obclient> DESCRIBE tbl1; +-------+------------+----------+--------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+----------+--------+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c2 | varchar(50)| YES | | NULL | | | c3 | int(11) | YES | | NULL | | +-------+------------+----------+--------+---------+-------+ 3 row affectedDrop the
c3column and execute theDESCRIBEcommand to view table information.obclient> ALTER TABLE tbl1 DROP c3; Query OK, 1 row affected obclient> DESCRIBE tbl1; +-------+------------+----------+--------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+----------+--------+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c2 | varchar(50)| YES | | NULL | | +-------+------------+----------+--------+---------+-------+ 2 row affectedRename the
c2column toc3in tabletbl1, and execute theDESCRIBEstatement to view the table information.obclient> ALTER TABLE tbl1 CHANGE COLUMN c2 c3 VARCHAR(50); Query OK, 1 row affected obclient> DESCRIBE tbl1; +-------+------------+----------+--------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+----------+--------+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c3 | varchar(50)| YES | | NULL | | +-------+------------+----------+--------+---------+-------+ 2 rows affectedAdd column
c4to tabletbl1, set it as the first column, and execute theDESCRIBEcommand to view the table information.obclient> ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST; Query OK, 1 row affected obclient> DESCRIBE tbl1; +-------+------------+----------+--------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+----------+--------+---------+-------+ | c4 | int(11) | YES | | NULL | | | c1 | int(11) | NO | PRI | NULL | | | c3 | varchar(50)| YES | | NULL | | +-------+------------+----------+--------+---------+-------+ 3 row affectedAdd the
c2column after thec1column and execute theDESCRIBEcommand to view table information.obclient> ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1; Query OK, 1 row affected obclient> DESCRIBE tbl1; +-------+------------+----------+--------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+----------+--------+---------+-------+ | c4 | int(11) | YES | | NULL | | | c1 | int(11) | NO | PRI | NULL | | | c2 | int(11) | YES | | NULL | | | c3 | varchar(50)| YES | | NULL | | +-------+------------+----------+--------+---------+-------+ 4 row affectedAdd the
c5column before thec4column and execute theDESCRIBEcommand to view the table information.obclient> ALTER TABLE tbl1 ADD COLUMN c5 INT BEFORE c4; Query OK, 1 row affected obclient> DESCRIBE tbl1; +-------+------------+----------+--------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+----------+--------+---------+-------+ | c5 | int(11) | YES | | NULL | | | c4 | int(11) | YES | | NULL | | | c1 | int(11) | NO | PRI | NULL | | | c2 | int(11) | YES | | NULL | | | c3 | varchar(50)| YES | | NULL | | +-------+------------+----------+--------+---------+-------+ 5 row affectedAdd a foreign key constraint
fk1for tabletbl2. When anUPDATEoperation affects the key value in the parent table that matches a row in the subtable, perform aSET NULLoperation.obclient> CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT); Query OK, 0 row affected obclient> CREATE TABLE tbl3(c1 INT PRIMARY KEY,c2 INT); Query OK, 0 row affected obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1) ON UPDATE SET NULL; Query OK, 0 row affectedDrop the foreign key constraint
fk1on tabletbl3.obclient> ALTER TABLE tbl2 DROP FOREIGN KEY fk1; Query OK, 0 row affectedDrop the primary key of table
tbl2.obclient> ALTER TABLE tbl2 DROP PRIMARY KEY; Query OK, 0 row affected
Index operations
Rename the index
ind1of tabletbl2toind2.obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50)); Query OK, 0 row affected obclient> CREATE INDEX ind1 ON tbl2 (c2 ASC); Query OK, 0 row affected obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2; Query OK, 0 row affectedCreate index
ind3on tabletbl2, referencing columnsc1andc2.obclient> ALTER TABLE tbl2 ADD INDEX ind3 (c1,c2); Query OK, 0 row affectedYou can execute the
SHOW INDEXstatement to view the created indexes.obclient> SHOW INDEX FROM tbl2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | tbl2 | 0 | PRIMARY | 1 | c1 | A | NULL | NULL | NULL | | BTREE | available | | YES | | tbl2 | 1 | ind2 | 1 | c2 | A | NULL | NULL | NULL | YES | BTREE | available | | YES | | tbl2 | 1 | ind3 | 1 | c1 | A | NULL | NULL | NULL | | BTREE | available | | YES | | tbl2 | 1 | ind3 | 2 | c2 | A | NULL | NULL | NULL | YES | BTREE | available | | YES | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ 4 rows in setDrop the index
ind2on tabletbl2.obclient> ALTER TABLE tbl2 DROP INDEX ind2; Query OK, 0 row affectedNote
In actual O&M scenarios, you can use the above methods to achieve atomic index changes.
Add three function-based indexes on
t1_func. One is namedi2, and the other two are automatically generated by the system, following the format offunctional_indexprefix plus a number.obclient> ALTER TABLE t1_func ADD INDEX ((CONCAT(c1,'a'))); Query OK, 0 rows affected obclient> ALTER TABLE t1_func ADD INDEX ((c1+1)); Query OK, 0 rows affected obclient> ALTER TABLE t1_func ADD INDEX i2 ((CONCAT(c1,'a'))); Query OK, 0 rows affected obclient> SHOW CREATE TABLE t1_func; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1_func | CREATE TABLE `t1_func` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `i1` (((`c1` + `c2`) < 1)) BLOCK_SIZE 16384 LOCAL, KEY `functional_index` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL, KEY `functional_index_2` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL, KEY `i2` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setCreate a columnstore index for the table.
Use the following SQL statement to create table
tbl3.CREATE TABLE tbl3 (col1 INT, col2 VARCHAR(50));Create a columnstore index named
idx1_tbl3on thetbl3table, referencing thecol1column.ALTER TABLE tbl3 ADD INDEX idx1_tbl3 (col1) WITH COLUMN GROUP(each column);
Modified indexes are invisible.
Note
After an index is set to invisible, you can see the
/*!80000 INVISIBLE */marker in the table schema.Create table
tbl4.CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT, col3 INT);Create index
idx1_tbl4.CREATE INDEX idx1_tbl4 ON tbl4(col2);The index
idx1_tbl4is modified and becomes invisible.ALTER TABLE tbl4 ALTER INDEX idx1_tbl4 INVISIBLE;View the structure of table
tbl4again.SHOW CREATE TABLE tbl4;The return result is as follows:
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl4 | CREATE TABLE `tbl4` ( `col1` int(11) NOT NULL, `col2` int(11) DEFAULT NULL, `col3` int(11) DEFAULT NULL, PRIMARY KEY (`col1`), KEY `idx1_tbl4` (`col2`) BLOCK_SIZE 16384 LOCAL /*!80000 INVISIBLE */ ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
Specify the table-level hot and cold storage strategy for an index when adding it to an existing table
The syntax is as follows:
ALTER TABLE table_name ADD INDEX index_name(column_name)
STORAGE_CACHE_POLICY (storage_cache_policy_option);
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "none"}
| timeline_strategy_list
timeline_strategy_list:
BOUNDARY_COLUMN = column_name
| BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
| HOT_RETENTION = intnum retention_time_unit
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
Related parameters
You can configure hot and cold storage policies for indexed tables:
For local indexes, you can specify the table-level
STORAGE_CACHE_POLICY. If the table-levelSTORAGE_CACHE_POLICYis not specified, the default isnone, which means the configuration of the data table'sSTORAGE_CACHE_POLICYis used. The hot and cold storage policies for local index partitions then follow the partitioning rules of the data table.For global indexes, in addition to specifying the table-level
STORAGE_CACHE_POLICY, you can also specify a partition-levelSTORAGE_CACHE_POLICYfor the index table if it has partitions. If the table-levelSTORAGE_CACHE_POLICYis not specified for a global index, the default isnone.Note
If the table-level
STORAGE_CACHE_POLICYis not specified for a global index, and the primary table uses the Global strategy, the index follows the primary table'sSTORAGE_CACHE_POLICYby default. If the primary table uses the timeline strategy, the index defaults to thehotstrategy.
When setting the table-level STORAGE_CACHE_POLICY, different options within the storage_cache_policy_option parameter are defined in Key-Value format. The semantics of each option are as follows:
GLOBAL = {"hot" | "auto" | "cold" | "none"}: Specifies the hot, auto, cold, or none storage strategy for index data. Valid values:hot: specifies the index as a hot storage. If there is sufficient cache disk space, all data under the index table will be cached to the local cache disk.auto: Indicates that the system automatically identifies hot data in the specified index table.cold: Indicates that the data of the specified index table is cold data and will not be cached to the local cache disk. For data already persisted to the cache, cache eviction will be actively triggered. Subsequent accesses to the table data can be stored in the macroblock memory cache and will not be persisted to the macroblock cache.none: The default value. This means the index follows theSTORAGE_CACHE_POLICYvalue of the primary table.
timeline_strategy_list: The list of timeline strategy parameters, separated by commas. Timeline strategies use time to determine whether partition data is stored in hot or cold storage. The system automatically adjusts the data on local cache disks based on the configured strategies.Note
Timeline: Hot data is cached based on the partitioning range defined by RANGE partitioning. When certain time conditions are met, the data in that partition is identified as hot data. When using the timeline strategy, note the following considerations:
- Only supported for RANGE-partitioned tables (RANGE partitioning can be at the primary or secondary level), because time is required to determine whether a partition has expired. However, it cannot be a dual RANGE partition, as it would be impossible to determine which RANGE partition's time to use.
- When using the timeline strategy, the partition expression can only contain column names; expression conversion is not supported. For example,
PARTITION BY RANGE COLUMNS(expr(col3)). BOUNDARY_COLUMNmust be the partitioning key. If the partitioning key consists of multiple columns,BOUNDARY_COLUMNmust be the first column, used to determine whether the partition has expired.
BOUNDARY_COLUMN = column_name: specifies the column for identifying hot data. The column name can be of an integer type (BIGINTorINT, in the format of a Unix timestamp) or a time type (TIMESTAMP,DATE,DATETIME, orYEAR).- If
BOUNDARY_COLUMNis of the integer type, the table supports RANGE and RANGE COLUMNS partitioning. - If the
BOUNDARY_COLUMNtype is a time type, the table can only use the RANGE COLUMNS partitioning method.
- If
BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: Specifies the time unit for theBOUNDARY_COLUMNparameter. This parameter is supported only whenBOUNDARY_COLUMNis an integer. When an integer value is used as a timestamp, a timestamp unit must be specified; otherwise, it may lead to incorrect time parsing. Valid values are as follows:- When the partitioning column is of type
INT, the value ofBOUNDARY_COLUMN_UNITcan only bes. - When the partitioning column is of the
BIGINTtype, the value ofBOUNDARY_COLUMN_UNITcan besorms.
- When the partitioning column is of type
HOT_RETENTION = intnum retention_time_unit: Specifies the time range for hot data.intnum: an integer.retention_time_unit: the time unit. Valid values:YEAR: the year.MONTH: the month.WEEK: week.DAY: day.HOUR: the hour.MINUTE: the number of minutes.
Examples
Specify the table-level
STORAGE_CACHE_POLICYfor the index during creation:Create table
tbl3.CREATE TABLE tbl3 (col1 INT, col2 INT, col3 INT);Create index
idx1_tbl3and specify the data in the index table as hot data.CREATE INDEX idx1_tbl3 ON tbl3 (col1) LOCAL STORAGE_CACHE_POLICY (GLOBAL = "hot");
Create an index when creating a table and specify the index table's data as hot data.
CREATE TABLE tbl4 (col1 INT, col2 INT, INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));When adding an index to an existing table, specify the data in the index table as hot data.
ALTER TABLE tbl4 ADD INDEX idx3(col1, col2) GLOBAL STORAGE_CACHE_POLICY (GLOBAL = "hot");
Partition operations
Truncate all data in partitions
M202001andM202002of the partitioned tablet_log_part_by_range.obclient> CREATE TABLE t_log_part_by_range ( log_id bigint NOT NULL , log_value varchar(50) , log_date timestamp NOT NULL ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) ( PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01')) , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01')) , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01')) , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01')) , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01')) ); Query OK, 0 rows affected obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002; Query OK, 0 rows affectedAdd partition
M202006to the partitioned tablet_log_part_by_range.obclient> CREATE TABLE t_log_part_by_range ( log_id bigint NOT NULL , log_value varchar(50) , log_date timestamp NOT NULL ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) ( PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01')) , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01')) , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01')) , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01')) , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01')) ); Query OK, 0 rows affected obclient> ALTER TABLE t_log_part_by_range ADD PARTITION (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01')) );
Modify the hot and cold storage strategy of an index table
You can use the ALTER TABLE statement to modify the hot/cold storage policy (STORAGE_CACHE_POLICY) of an existing index. The syntax is as follows:
ALTER TABLE table_name ALTER INDEX STORAGE_CACHE_POLICY (storage_cache_policy_option);
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "none"}
| timeline_strategy_list
timeline_strategy_list:
| BOUNDARY_COLUMN = column_name
| BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
| HOT_RETENTION = intnum retention_time_unit
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
Related parameters
GLOBAL = {"hot" | "auto" | "none"}: Specifies the hot/cold storage strategy for index data. Valid values:hot: specifies the index as a hot storage. If there is sufficient cache disk space, all data under the index table will be cached to the local cache disk.auto: Indicates that the hot data of the specified index table is automatically identified by the system.none: The default value. This means the index follows theSTORAGE_CACHE_POLICYvalue of the primary table.
timeline_strategy_list: The list of timeline strategy parameters, separated by commas. Timeline strategies use time to determine whether partition data is stored in hot or cold storage. The system automatically adjusts the data on local cache disks based on the configured strategies.Note
Time axis: Hot data is cached based on the partitioning range defined by RANGE partitioning. When certain time conditions are met, the data in that partition is identified as hot data. When using the time axis strategy, note the following considerations:
- Only supported for RANGE-partitioned tables (RANGE partitioning can be at either the primary or secondary level), because time is required to determine whether a partition has expired. However, it cannot be a dual RANGE partition, as it would be impossible to determine which RANGE partition's time to use.
- When using the time axis, the partition expression can only contain column names; expression conversion is not supported. For example,
PARTITION BY RANGE COLUMNS(expr(col3)). BOUNDARY_COLUMNmust be the partitioning key. If the partitioning key consists of multiple columns,BOUNDARY_COLUMNmust be the first column, used to determine whether the partition has expired.
BOUNDARY_COLUMN = column_name: Identifies the column for hot data detection. Supports integer types (BIGINTorINT, in Unix timestamp format) and time types (TIMESTAMP,DATE,DATETIME, orYEAR).- If
BOUNDARY_COLUMNis of the integer type, the table's partitioning method supports RANGE and RANGE COLUMNS partitioning types. - If the
BOUNDARY_COLUMNtype is a time type, the table can only use the RANGE COLUMNS partitioning method.
- If
BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit for theBOUNDARY_COLUMNparameter. This parameter is supported only whenBOUNDARY_COLUMNis an integer. When an integer value is used as a timestamp, a timestamp unit must be specified; otherwise, it may lead to incorrect time parsing. Valid values are as follows:- When the partitioning column is of type
INT, the value ofBOUNDARY_COLUMN_UNITcan only bes. - When the partitioning column is of the
BIGINTtype, the value ofBOUNDARY_COLUMN_UNITcan besorms.
- When the partitioning column is of type
HOT_RETENTION = intnum retention_time_unit: Specifies the time range for hot data.intnum: an integer.retention_time_unit: The time unit. Valid values:YEAR: the year.MONTH: the month.WEEK: week.DAY: day.HOUR: the hour.MINUTE: the number of minutes.
Examples
Change the hot data for index idx2 to be automatically identified by the system.
ALTER TABLE test_tbl2 ALTER INDEX idx2
STORAGE_CACHE_POLICY (GLOBAL = "auto");
Modify the degree of parallelism
Change the degree of parallelism for table
tbl3to2.obclient> ALTER TABLE tbl3 PARALLEL 2;In addition to using the
ALTER TABLE table_name PARALLEL int;statement to modify a table's degree of parallelism, you can also use thePARALLELhint, with the syntaxalter /*+ parallel(int) */ table.
Column type operations
Change the ordinary column
col1of tabletbl4to an auto-increment column.obclient> CREATE TABLE tbl4 (col1 BIGINT(10) NOT NULL,col2 INT); Query OK, 0 rows affected obclient> ALTER TABLE tbl4 MODIFY col1 BIGINT(10) AUTO_INCREMENT; Query OK, 0 rows affectedAdd a primary key
col1for tabletbl4, and then change the primary key tocol2.obclient> ALTER TABLE tbl4 ADD PRIMARY KEY (col1); Query OK, 0 rows affected obclient> ALTER TABLE tbl4 DROP PRIMARY KEY,ADD PRIMARY KEY (`col2`); Query OK, 0 rows affectedAdded a
CHECKconstraint.obclient> CREATE TABLE tbl7(col1 VARCHAR(10),col2 VARCHAR(10)); Query OK, 0 rows affected obclient> ALTER TABLE tbl7 ADD CONSTRAINT my_check CHECK (col1> col2) ; Query OK, 0 rows affectedDrop the
CHECKconstraint on tabletbl7.obclient> ALTER TABLE tbl7 DROP CHECK my_check; Query OK, 0 rows affectedChange the column type to
NOT NULL.obclient> CREATE TABLE dept( deptno NUMBER(2,0), dname VARCHAR(14), location VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno) ); Query OK, 0 rows affected obclient> CREATE TABLE emp( empno NUMBER(4,0) NOT NULL, empname VARCHAR(10) NOT NULL, job VARCHAR(9) NOT NULL, mgr NUMBER(4,0) NOT NULL, hiredate DATE NOT NULL, sal NUMBER(7,2) DEFAULT NULL, comm NUMBER(7,2) DEFAULT NULL, deptno NUMBER(2,0) DEFAULT NULL, CONSTRAINT PK_emp PRIMARY KEY (empno), CONSTRAINT FK_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno) ); Query OK, 0 rows affected obclient> ALTER TABLE emp MODIFY deptno NUMBER(2,0) DEFAULT '12' NOT NULL; Query OK, 0 rows affected
Change column name and add a NOT NULL column constraint
Notice
Modifying column names and adding a not null constraint are only allowed if the following conditions are met:
- You can only change the name and the constraint of one column at a time. Multiple columns are not supported.
- Only the
not nullconstraint is supported for addition. - The statement contains only the
alter_columnclause and no other operations such as creating indexes or partitions. - The column whose name is being changed must be the same column to which the constraint is being added.
obclient> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (2.421 sec)
Add NOT NULL constraints to both columns
Notice
To add the NOT NULL constraint to two columns simultaneously, the following conditions must be met:
- You cannot add constraints to more than two columns.
- You can only add the
NOT NULLconstraint. - The statement must contain only the
ALTER COLUMNclause and no other operations such as creating indexes or partitions. - No other columns than the two columns for which constraints are being added can be modified.
Run the following command to add a NOT NULL constraint on both columns C1 and C2:
obclient> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (2.605 sec)
Change the type of a column with a prefix index
Notice
The following conditions must be met to change the type of a column with a prefix index:
- The original column type is
char. - The target column type is
varchar. - The lengths of the original and target columns must be the same.
Run the following command to create a table named
test_alter5, which contains a column namedc1with a data type ofCHAR(20). Also, create a prefix index namedidx_c1on thec1column:obclient> CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));Run the following command to modify the data type of column
c1tovarchar(20):obclient> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
Change foreign key constraint rules
Run the following command to create table
t1and add a unique constraint on the integer columnc1:obclient> CREATE TABLE t1(c1 int unique key);Execute the following command to create a table named
t2, which contains an integer columnc1, and define a foreign key constraint:obclient> CREATE TABLE t2(c1 int, CONSTRAINT fk_cst_test FOREIGN KEY (c1) REFERENCES t1(c1) on update set null on delete no action);Execute the following command to query the system table
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSfor the update and delete rules of the foreign key constraintfk_cst_test:obclient> SELECT update_rule, delete_rule FROM information_schema.referential_constraints WHERE CONSTRAINT_NAME = 'fk_cst_test';The return result is as follows:
+-------------+-------------+ | update_rule | delete_rule | +-------------+-------------+ | SET NULL | NO ACTION | +-------------+-------------+UPDATE_RULE: ReturnsSET NULL(indicating that the foreign key is set toNULLduring the update).DELETE_RULE: ReturnsNO ACTION(indicating that the check constraint will be checked during deletion and may prevent the operation).
Rename a column
The
RENAME COLUMNstatement does not change the column definition; it only changes the column name. If the target name already exists in the table, theRENAME COLUMNoperation will report an error. However, renaming to the original name will not cause an error.obclient> CREATE TABLE tbl8 (a INT, b INT); Query OK, 0 rows affected obclient> ALTER TABLE tbl8 RENAME COLUMN a TO b; ERROR 1060 (42S21): Duplicate column name 'b' obclient> ALTER TABLE tbl8 RENAME COLUMN a TO a; Query OK, 0 rows affectedIf an index exists on the renamed column, the
RENAME COLUMNstatement can be executed normally and the index definition will be automatically cascaded.obclient> CREATE TABLE tbl9 (a INT, b INT, index idx_a(a)); Query OK, 0 rows affected obclient> SHOW INDEX FROM tbl9; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | tbl9 | 1 | idx_a | 1 | a | A | NULL | NULL | NULL | YES | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 1 row in set obclient> ALTER TABLE tbl9 RENAME COLUMN a TO c; Query OK, 0 rows affected obclient> SHOW INDEX FROM tbl9; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | tbl9 | 1 | idx_a | 1 | c | A | NULL | NULL | NULL | YES | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 1 row in setIf a renamed column is referenced by a prefix index, the
RENAME COLUMNstatement can be executed normally, as prefix indexes support cascade modification.DROP TABLE tbl9; obclient> CREATE TABLE tbl9 (c1 INT PRIMARY KEY, c2 BLOB, c3 INT, INDEX i1 (c2(10))); Query OK, 0 rows affected obclient> ALTER TABLE tbl9 RENAME COLUMN c2 TO c2_, RENAME COLUMN c1 TO c2_1; DESC tbl9; Query OK, 0 rows affected obclient> SHOW INDEX FROM tbl9; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | tbl9 | 0 | PRIMARY | 1 | c2_1 | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | tbl9 | 1 | i1 | 1 | c2_ | A | NULL | 10 | NULL | YES | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 2 rows in setIf a foreign key constraint is defined on the renamed column, the
RENAME COLUMNstatement can be executed normally and the foreign key constraint will be automatically cascaded.obclient> CREATE TABLE tbl10 (a INT PRIMARY KEY); Query OK, 0 rows affected obclient> CREATE TABLE tbl11(b INT, FOREIGN KEY (b) REFERENCES tbl10(a)); obclient> SHOW CREATE TABLE tbl11; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl11 | CREATE TABLE `tbl11` ( `b` int(11) DEFAULT NULL, CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`a`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set obclient> ALTER TABLE tbl10 RENAME COLUMN a TO c; Query OK, 0 rows affected obclient> SHOW CREATE TABLE tbl11; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl11 | CREATE TABLE `tbl11` ( `b` int(11) DEFAULT NULL, CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`c`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
OceanBase AI Database does not support modification or will not automatically cascade modifications in the following scenarios:
If a renamed column is referenced by a generated list expression, the column name cannot be modified; otherwise, an error will occur during execution.
obclient> CREATE TABLE tbl12(a INT, b INT AS (a + 1), c INT, d INT, CONSTRAINT d_check CHECK(d > 0)) PARTITION BY HASH(c + 1) PARTITIONS 2; obclient> ALTER TABLE tbl12 RENAME COLUMN a TO e; ERROR 3108 (HY000): Column 'a' has a generated column dependencyIf a renamed column is referenced by a partitioning expression, the column name cannot be modified, otherwise an error will occur during execution.
obclient> ALTER TABLE tbl12 RENAME COLUMN c TO e; ERROR 3855 (HY000): Column 'c' has a partitioning function dependency and cannot be dropped or renamed.If a renamed column is referenced by a
CHECKconstraint, the column name cannot be modified, otherwise an error will occur during execution.obclient> ALTER TABLE tbl12 RENAME COLUMN d TO e; ERROR 3959 (HY000): Check constraint 'd_check' uses column 'd', hence column cannot be dropped or renamed.If a renamed column is referenced by a functional index, the column name cannot be modified; otherwise, an error will occur during execution.
DROP TABLE IF EXISTS tbl12; obclient> CREATE TABLE tbl12(i INT, INDEX ((i+1))); Query OK, 0 rows affected obclient> ALTER TABLE tbl12 RENAME COLUMN i TO j; ERROR 3837 (HY000): Column 'i' has a functional index dependency and cannot be dropped or renamed.If a renamed column is referenced by a view, the
RENAME COLUMNstatement executes successfully, but querying the view will report an error. You must manually modify the view definition.obclient> CREATE TABLE tbl13(a INT); Query OK, 0 rows affected obclient> CREATE VIEW v1 AS SELECT a + 1 FROM tbl13; Query OK, 0 rows affected obclient> SELECT * FROM v1; Empty set obclient> ALTER TABLE tbl13 RENAME COLUMN a TO b; Query OK, 0 rows affected obclient> SELECT * FROM v1; ERROR 1356 (42S22): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use themA renamed column is referenced by a stored procedure. The
RENAME COLUMNstatement executes successfully, but theCALLprocedure reports an error, requiring manual modification by the user.obclient> CREATE TABLE tbl14(a INT); Query OK, 0 rows affected obclient> CREATE PROCEDURE proc() SELECT a + 1 FROM tbl14; Query OK, 0 rows affected obclient> CALL proc(); Empty set obclient> ALTER TABLE tbl14 RENAME COLUMN a TO b; Query OK, 0 rows affected obclient> CALL proc(); ERROR 1054 (42S22): Unknown column 'a' in 'field list'
Modify the columnar storage attributes of a table
Use the following SQL statement to create table
tbl1.CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));Change the
tbl1table to a hybrid row-column storage table, and then drop the hybrid row-column storage attribute.ALTER TABLE tbl1 ADD COLUMN GROUP(all columns, each column);ALTER TABLE tbl1 DROP COLUMN GROUP(all columns, each column);Change table
tbl1to a columnar storage table, and then drop the columnar storage attribute.ALTER TABLE tbl1 ADD COLUMN GROUP(each column);ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
Modify the skip index attribute of a column
Use the following SQL statement to create a table named test_skidx.
CREATE TABLE test_skidx(
col1 INT SKIP_INDEX(MIN_MAX, SUM),
col2 FLOAT SKIP_INDEX(MIN_MAX),
col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX),
col4 CHAR(10)
);
Modify the skip index attribute of column
col2in tabletest_skidxtoSUMSkip Index.ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);Add a column with a skip index attribute after table creation. Add a
MIN_MAXskip index type for columncol4in tabletest_skidx.ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);Remove a column with a skip index attribute after table creation. Remove the skip index attribute for column
col1in tabletest_skidx.ALTER TABLE test_skidx MODIFY COLUMN col1 INT SKIP_INDEX();or
ALTER TABLE test_skidx MODIFY COLUMN col1 INT;
Modify table attributes
Disable persistent macroblock-level bloom filtering for table tb.
ALTER TABLE tb SET enable_macro_block_bloom_filter = False;
Modify table-level STORAGE_CACHE_POLICY
Change the hot cache for table
tbl1to be automatically identified by the system.ALTER TABLE tbl1 SET STORAGE_CACHE_POLICY (GLOBAL = "auto");Modify table
tbl2to identify partition data within 2 months from the current time as hot data based on the value ofcol3.ALTER TABLE tbl2 SET STORAGE_CACHE_POLICY (BOUNDARY_COLUMN = col3, HOT_RETENTION = 2 MONTH);
Modify the partition-level STORAGE_CACHE_POLICY
Add partition p2 to table
tbl3. The system automatically identifies the hot data that needs to be cached to the local disk in partition p2.ALTER TABLE tbl3 ADD PARTITION (PARTITION p2 VALUES LESS THAN(300) STORAGE_CACHE_POLICY = "auto");Modify the data in the existing partition p1 of table
tbl3to hot data.ALTER TABLE tbl3 ALTER PARTITION p1 STORAGE_CACHE_POLICY = 'hot';The data that needs to be cached to the local disk in subpartition sp3 of table
tbl4is automatically identified by the system.ALTER TABLE tbl4 ALTER SUBPARTITON sp3 STORAGE_CACHE_POLICY = 'auto';
Modify or delete a table's TTL policy
Modify the table's TTL policy. For example, change the data expiration time to 1 hour.
obclient(root@mysql001)[infotest]> ALTER TABLE ttl_tbl1 SET TTL ORA_ROWSCN + INTERVAL 1 HOUR BY COMPACTION;The TTL strategy for deleting tables.
obclient(root@mysql001)[infotest]> ALTER TABLE ttl_tbl1 SET REMOVE TTL;Enable row-level privileges on tables and enforce policy restrictions on table owners as well.
ALTER TABLE hr.employees ENABLE ROW LEVEL SECURITY; ALTER TABLE hr.employees FORCE ROW LEVEL SECURITY;Removed mandatory row-level privileges and disabled them.
ALTER TABLE hr.employees NO FORCE ROW LEVEL SECURITY; ALTER TABLE hr.employees DISABLE ROW LEVEL SECURITY;
Add or modify an async index
Example of adding an asynchronous full-text index (the target table must be a heap table):
ALTER TABLE doc_async ADD ASYNC FULLTEXT INDEX idx_content(content);
Example of adding an asynchronous search index (the target table must be a heap table):
ALTER TABLE t ADD ASYNC SEARCH INDEX idx_search(doc);
The following example shows how to modify the table-level configuration of an async index:
ALTER TABLE doc_async SET ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';
Modify multiple configurations at the same time:
ALTER TABLE doc_async SET ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10, DELETE_PURGE_THRESHOLD=30, MERGE_SEGMENT_THRESHOLD=8';
Skip abnormal rows during incremental refresh:
ALTER TABLE doc_async SET ASYNC_INDEX_PARAMS = 'REFRESH_ON_ERROR=SKIP';