Purpose
This statement is used to create a new table in the database.
Syntax
CREATE [hint_options] [TEMPORARY] TABLE [IF NOT EXISTS] table_name
(table_definition_list) [table_option_list] [partition_option] [[MERGE_ENGINE = {delete_insert | partial_update}] table_column_group_option] [IGNORE | REPLACE] [AS] select;
CREATE TABLE [TEMPORARY] [IF NOT EXISTS] table_name
LIKE table_name;
table_definition_list:
table_definition [, table_definition ...]
table_definition:
column_definition_list
| [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
| [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY}
[index_name] index_desc
| [CONSTRAINT [constraint_name]] FOREIGN KEY
[index_name] index_desc
REFERENCES reference_definition
[match_action][opt_reference_option_list]
| [ASYNC] [FULLTEXT] {INDEX | KEY} [index_name] [index_type] (key_part,...) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)]
[index_option_list] [index_column_group_option]
| [ASYNC] SEARCH {INDEX | KEY} [index_name] (search_index_column [, search_index_column ...])
| index_json_clause
| [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state
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)]
| column_name data_type
[GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
[opt_generated_column_attribute]
skip_index_option_list:
skip_index_option [,skip_index_option ...]
skip_index_option:
MIN_MAX
| SUM
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}
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
key_part:
{index_col_name [(length)] | (expr)} [ASC | DESC]
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)
table_option_list:
table_option [ table_option ...]
table_option:
[DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| table_tablegroup
| block_size
| lob_inrow_threshold [=] num
| compression
| AUTO_INCREMENT [=] INT_VALUE
| COMMENT string_value
| ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
| PCTFREE [=] num
| parallel_clause
| DUPLICATE_SCOPE [=] 'none|cluster'
| TABLE_MODE [=] 'table_mode_value'
| auto_increment_cache_size [=] INT_VALUE
| READ {ONLY | WRITE}
| ORGANIZATION [=] {INDEX | HEAP}
| enable_macro_block_bloom_filter [=] {True | False}
| DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
| SEMISTRUCT_ENCODING_TYPE [=] 'encoding' # Deprecated. Use SEMISTRUCT_PROPERTIES instead.
| MICRO_BLOCK_FORMAT_VERSION [=] {1|2}
| STORAGE_CACHE_POLICY (storage_cache_policy_option)
| 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}
| TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
| PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
| EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
| TIME_ZONE = {'default' | 'time_zone'}
| BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}
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) [STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}]
| PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
[subpartition_option] PARTITIONS partition_count
| PARTITION BY RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] (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 = {"hot" | "auto" | "none"}]
| SUBPARTITION BY LIST(expression)
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "none"}
| timeline_strategy_list
timeline_strategy_list:
BOUNDARY_COLUMN = column_name [,BOUNDARY_COLUMN_UNIT = {"s"| "ms"}] ,HOT_RETENTON = intnum retention_time_unit
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION partition_name
VALUES LESS THAN {(expression_list) | MAXVALUE}
[STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}]
range_subpartition_list:
range_subpartition [, range_subpartition ...]
range_subpartition:
SUBPARTITION subpartition_name
VALUES LESS THAN {(expression_list) | MAXVALUE}
[STORAGE_CACHE_POLICY = {"hot" | "auto" | "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
table_column_group_option/index_column_group_option:
WITH COLUMN GROUP(all columns)
| WITH COLUMN GROUP(each column)
| WITH COLUMN GROUP(all columns, each column)
index_json_clause:
[UNIQUE] INDEX idx_json_name((CAST(json_column_name->'$.json_field_name' AS UNSIGNED ARRAY)))
| INDEX idx_json_name(column_name, [column_name, ...] (CAST(json_column_name->'$.json_field_name' AS CHAR(n) ARRAY)))
Parameters
Parameters |
Description |
|---|---|
| hint_options | Optional. Specifies hint options. You can manually specify direct load hints, including APPEND, DIRECT, and NO_DIRECT. The corresponding hint format is /*+ [APPEND \ | DIRECT(need_sort,max_error,load_type)] parallel(N) \ | NO_DIRECT */. For more information about bulk data import using the CREATE TABLE AS SELECT statement, see the Use the CREATE TABLE AS SELECT statement for direct load section in Full Direct Load. |
| TEMPORARY | Optional. Specifies to create the table as a temporary table. |
| IF NOT EXISTS | If specified IF NOT EXISTS, even if the table to be created already exists, no error will be reported. If this parameter is not specified and the table to be created already exists, the system will report an error. |
| IGNORE \ | REPLACE | Optional. Specifies how to handle rows with duplicate unique key values when you use the CREATE TABLE ... SELECT statement. If rows with duplicate unique key values exist and neither IGNORE nor REPLACE is specified, an error will be returned.
|
| PRIMARY KEY | Specify the primary key for the created table. If not specified, a hidden primary key is used. OceanBase AI Database supports modifying the primary key of a table or adding a primary key to a table by using the ALTER TABLE statement. For more information, see ALTER TABLE. |
| FOREIGN KEY | Specify a foreign key for the created table. If you do not specify a foreign key name, the name is generated in the format of table name + OBFK + creation time. (For example, the foreign key created for the t1 table at 00:00:00 on August 1, 2021 is named t1_OBFK_1627747200000000.) A foreign key allows cross-referencing of related data across tables. When an UPDATE or DELETE operation affects a key value in the parent table that matches a row in the child table, the result depends on the reference operations of the ON UPDATE and ON DELETE clauses:
SET DEFAULT Operation. |
| FULLTEXT | Optional. Specifies to create a full-text index. For more information about creating a full-text index, see the Create a full-text index section in Create an index.
NoticeThe current version only supports creating local full-text indexes. |
| ASYNC | Optional. When used together with FULLTEXT, it indicates creating an asynchronous full-text index (ASYNC FULLTEXT); when used together with SEARCH, it indicates creating an asynchronous search index (ASYNC SEARCH INDEX). ASYNC cannot be used alone, nor can it be used for normal or unique indexes. It is only supported on heap tables (ORGANIZATION = HEAP); the same index type on the same table does not support coexistence of both synchronous and asynchronous indexes. Both asynchronous full-text indexes and asynchronous search indexes are supported starting from V4.6.2.1. For more information, see the "Asynchronous Full-text Index" section in Asynchronous Full-text Index and Search index in the "Asynchronous search index" section. |
| SEARCH [INDEX \ | KEY] | Optional. Specifies to create a search index. Can be used with ASYNC Create an asynchronous search index by using the JOIN clause. For more information, see Search index. |
| 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. |
| KEY \ | INDEX | Specify a key or index for the created table. If you do not specify an index name, the first column referenced by the index will be used as the index name. If the name is duplicated, it will be named in the format of underscores (_ ) + a serial number. (For example, if an index created on the c1 column has a duplicate name, the index will be named c1_2.) You can use the SHOW INDEX statement to view the indexes on a table. |
| 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 the index is as follows: First, sort by the values of the specified columns in the order specified. If two or more columns are specified and have the same value, then sort by the values of the next specified column in the order specified. This process continues until all specified columns have been evaluated. The values are first sorted by the first column in index_col_name. 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.
NoticeCreating a function-based index on a generated column is prohibited in the current version of OceanBase AI Database. |
| ROW_FORMAT | Specifies whether to enable the encoding storage format for the table.
|
| [GENERATED ALWAYS] AS (expr) [VIRTUAL \ | STORED] | Create a generated column, expr is an expression used to calculate the column value.
|
| BLOCK_SIZE | Specifies the microblock size of a table. |
| lob_inrow_threshold | Used to configure the LOB field INROW threshold. When the data size of a LOB field exceeds this threshold, the excess part is converted to OUTROW and stored in the LOB Meta table. The default value is controlled by the ob_default_lob_inrow_threshold variable. |
| COMPRESSION | Specifies the compression algorithm for a table. Valid values:
|
| CHARSET \ | CHARACTER SET | Specify the default character set for columns in a table. For more information about supported character sets, see Character sets. |
| COLLATE | Specify the default collation for columns in a table. For more information about available collations, see Collation. |
| table_tablegroup | Specify the table's tablegroup. |
| AUTO_INCREMENT | Specifies the initial value for the auto-increment column in a table. OceanBase AI Database supports using auto-increment columns as partition keys. |
| COMMENT | Comment. Case-insensitive. |
| PCTFREE | Specifies the percentage of reserved space for macroblocks. |
| parallel_clause | Specify the table-level parallelism:
|
| DUPLICATE_SCOPE | Specify the attributes of the replicated table. Valid values:
cluster-level replicated tables. |
| CHECK | Limits the range of values in a column.
SHOW CREATE TABLE Commandinformation_schema.TABLE_CONSTRAINTS Viewinformation_schema.CHECK_CONSTRAINTS |
| constraint_name | The constraint name, which can contain up to 64 characters.
|
| expression | Constraint expression.
|
| MERGE_ENGINE = {delete_insert \ | partial_update} | Optional. Specifies the update model for the table. Valid values:
NoteThe value of the |
| table_column_group_option/index_column_group_option | Specifies the columnar storage options for a table or index. The specific explanations are as follows:
|
| SKIP_INDEX | Specifies the skip index attribute for a column. Valid values:
Note
|
| index_json_clause | Specify the clause for creating a multi-valued index. For more information about multi-valued indexes, see the Create a multi-valued index section in Create an index.
NoticeThe multi-valued index feature is currently in the experimental stage and is not recommended for use in production environments to avoid affecting system stability. |
| json_column_name | Specifies the name of the column of the JSON data type in a table. |
| idx_json_name | Specifies the name of the multi-valued index to be created. |
| json_field_name | The name of the field for which you want to create an index in the JSON column. |
| 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 | Optional. 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:
|
| ORGANIZATION [=] {INDEX \ | HEAP} | Specifies the storage order of data rows in a table, that is, specifies the table organization mode. Valid values:
ORGANIZATION is not specified, its value is the same as the value of the default_table_organization. |
| 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) | Specifies the dynamic partition management attributes of a table to enable automatic partition creation and deletion. dynamic_partition_policy_list is the list of configurable parameters for the dynamic partitioning strategy, separated by commas. For detailed descriptions, see dynamic_partition_policy_option. |
| PARTITION BY RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] | It is used to specify the creation of an auto-partitioned table. For more information, see the syntax for creating a table with automatic partitioning in Automatic partition splitting. |
| SEMISTRUCT_ENCODING_TYPE | Optional. Specifies the semi-structured encoding type. Valid values:
Notice
|
| 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. |
| MICRO_BLOCK_FORMAT_VERSION | Optional. Specifies the version number of the table's microblock storage format. Valid values: [1,+∞).
|
| STORAGE_CACHE_POLICY | Optional. In shared storage mode, specifies the hotspot caching strategy for tables, partitions, and indexes. It indicates that the specified data is cached from object storage to the local cloud disk; otherwise, the system adapts the caching. For more information, see storage_cache_policy below. |
| ASYNC_INDEX_PARAMS [=] 'async_index_params_string' | Optional. Specifies the table-level parameters related to asynchronous indexing. Currently, the supported parameters are ASYNC_REFRESH_INTERVAL, REFRESH_ON_ERROR, DELETE_PURGE_THRESHOLD, and MERGE_SEGMENT_THRESHOLD. For more information, see ASYNC_INDEX_PARAMS in ALTER TABLE. |
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 subsequently (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: Indicates moderate. In this mode, the probability of triggering a major compaction after a minor compaction is medium.SUPER: Super. In this mode, a major compaction is triggered after a minor compaction with a high probability.EXTREME: Extreme. In this mode, a major compaction is triggered after a minor compaction with a higher probability.
For more information about major compactions, see Adaptive compaction.
tokenizer_option
SPACE: The default value, which 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 should be 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 method 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. It is used through theanalysisparameter inPARSER_PROPERTIES.
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. Default value: 84. Value range: 10 to 84.ngram_token_size: The token length forNGRAM. This parameter is valid only when the tokenizer isNGRAM. 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: Valid only 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 to forms such as{"analyzer": "standard"}. For a complete list of built-in types and their corresponding languages, see the description of theanalyzer_typeparameter in TOKENIZE.
Example:
CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT,
FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word'));
CREATE TABLE docs (
id BIGINT PRIMARY KEY,
content TEXT,
FULLTEXT INDEX ft_idx_content(content)
WITH PARSER analyzer
PARSER_PROPERTIES=(analysis='{"analyzer": "standard"}')
);
dynamic_partition_policy_option
ENABLE = {true | false}: Optional. Specifies whether to enable dynamic partition management. Modifiable. Valid values:true: The default value. It specifies to enable dynamic partition management.false: Indicates that dynamic partition management is disabled.
TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: The time unit of the partition, which is the interval for automatically creating partition boundaries. This parameter is required and cannot be modified. Valid values:hour: Partitions are divided by hour.day: Partitions are divided by day.week: Partitions are divided by week.month: Partitions are divided by month.year: Partitions are divided by year.
PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the time for precreating partitions. When you call this API to schedule dynamic partition management, partitions are precreated so that max_partition_upper_bound > now() + precreate_time. The value can be modified. 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 you need to pre-create multiple partitions, the interval between partition boundaries is
TIME_UNIT. - The boundary of the first pre-created partition is the existing largest partition boundary rounded up by
TIME_UNIT.
EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the partition expiration time. When you schedule a dynamic partition management task, all partitions that meet the condition partition upper bound < now() - expire_time will be deleted. The value can be modified. Valid values:-1: the default value, which indicates 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.
TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone information used when comparing the current time with the partitioning key of a time type (date,datetime, oryear) value. This parameter cannot be modified. Valid values:default: the default value, which indicates that no additional time zone is configured and the tenant's time zone is used. For all time zones other than those listed above, thetime_zonefield must be set todefault.time_zone: The custom time zone offset, for example,+8:00.
BIGINT_PRECISION = {'none'| 'ms' | 's'}: Optional. Specifies the timestamp precision of the partitioning key of thebiginttype. This parameter cannot be modified. Valid values:none: The default value, indicating no precision (the partitioning key is not of thebiginttype).ms: millisecond precision.s: seconds.
For more information about creating a dynamic partitioned table, see Create a dynamic partitioned table.
Example:
CREATE TABLE tbl2 (col1 INT, col2 DATETIME)
DYNAMIC_PARTITION_POLICY(
ENABLE = true,
TIME_UNIT = 'hour',
PRECREATE_TIME = '3 hour',
EXPIRE_TIME = '1 day',
TIME_ZONE = '+8:00',
BIGINT_PRECISION = 'none')
PARTITION BY RANGE COLUMNS (col2)(
PARTITION P0 VALUES LESS THAN ('2024-11-11 13:30:00')
);
storage_cache_policy
Create table-level storage_cache_policy
The STORAGE_CACHE_POLICY option is optional. If you do not specify the STORAGE_CACHE_POLICY option, the value of the tenant parameter default_storage_cache_policy is used by default during table creation. The storage_cache_policy_option defines different attributes in key-value pairs. The semantics of each attribute are as follows:
GLOBAL = {"hot"|"auto"|"none"}: Specifies the hotspot caching 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 cache disk.auto: Indicates that the hot data for the specified table is automatically identified by the system.none: Indicates that the index 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. The hotspot cache timeline strategy uses time to determine whether partition data is hot data. Based on the configured strategy, the system automatically adjusts the partition data on the local cache disk.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
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.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.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: year.MONTH: the month.WEEK: week.DAY: the number of days.HOUR: the hour.MINUTE: the number of minutes.
Create partition-level storage_cache_policy
The partition-level STORAGE_CACHE_POLICY is optional and can have the following values:
hot: Indicates that all data in the specified partition is hot data. If sufficient cache disk space is available, all data in the partition will be cached to the local cache disk.auto: Indicates that the system automatically identifies the hot data for the specified partition.none: The default value. It indicates that the hotspot cache policy for this partition follows the value ofSTORAGE_CACHE_POLICYof its parent partition.
Examples
Create a database table.
obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY, c2 VARCHAR(50)); Query OK, 0 rows affectedCreate a table with indexes.
obclient> CREATE TABLE tbl2 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2)); Query OK, 0 rows affectedCreate a table named
tbl3with a functional index.obclient> CREATE TABLE tbl3 (c1 INT, c2 INT, INDEX i1 ((c1+1)), UNIQUE KEY ((c1+c2))); Query OK, 0 rows affected obclient> SHOW CREATE TABLE tbl3; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl3 | CREATE TABLE `tbl3` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL, KEY `i1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Create a table with
8hash partitions.obclient> CREATE TABLE tbl4 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8; Query OK, 0 rows affectedCreate a table with RANGE partitions as the primary partitioning method and KEY partitions as the subpartitioning method.
obclient> CREATE TABLE tbl5 (c1 INT, c2 INT, c3 INT) PARTITION BY RANGE(c1) SUBPARTITION BY KEY(c2, c3) SUBPARTITIONS 5 (PARTITION p0 VALUES LESS THAN(0), PARTITION p1 VALUES LESS THAN(100)); Query OK, 0 rows affectedCreate a table with one column in GBK and one column in UTF-8.
obclient> CREATE TABLE tbl6 (c1 VARCHAR(10), c2 VARCHAR(10) CHARSET GBK COLLATE gbk_bin) DEFAULT CHARSET utf8 COLLATE utf8mb4_general_ci; Query OK, 0 rows affectedEnable encoding and use
zstdcompression, with macroblock reserved space set to5%.obclient> CREATE TABLE tbl7 (c1 INT, c2 INT, c3 VARCHAR(64)) COMPRESSION 'zstd_1.0' ROW_FORMAT DYNAMIC PCTFREE 5; Query OK, 0 rows affectedCreate table
tbl8and set the degree of parallelism to3.obclient> CREATE TABLE tbl8(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3; Query OK, 0 rows affectedUse an auto-increment column as the partitioning key.
obclient> CREATE TABLE tbl9(inv_id BIGINT NOT NULL AUTO_INCREMENT,c1 BIGINT, PRIMARY KEY (inv_id) ) PARTITION BY HASH(inv_id) PARTITIONS 8; Query OK, 0 rows affectedSpecify a foreign key for table
ref_t2. When anUPDATEoperation affects the key value in the parent table that matches a row in the subtable, perform aSET NULLoperation.obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY, C2 INT); Query OK, 0 rows affected obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY, C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON UPDATE SET NULL); Query OK, 0 rows affectedCreate a table named
tbl10with aCHECKconstraint and view the constraint information.obclient> CREATE TABLE tbl10 (col1 INT, col2 INT, col3 INT, CONSTRAINT equal_check1 CHECK(col1 = col3 * 2)); Query OK, 0 rows affected obclient> SHOW CREATE TABLE tbl10; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl10 | CREATE TABLE `tbl10` ( `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL, `col3` int(11) DEFAULT NULL, CONSTRAINT `equal_check1` CHECK ((`col1` = (`col3` * 2))) ) 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 table
tbl11based on tabletbl10and view its information.obclient> CREATE TABLE tbl11 LIKE tbl10; Query OK, 0 rows affected obclient> SHOW CREATE TABLE tbl11; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl11 | CREATE TABLE `tbl11` ( `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL, `col3` int(11) DEFAULT NULL, CONSTRAINT `tbl11_OBCHECK_1650793233327894` CHECK ((`col1` = (`col3` * 2))) ) 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 replicated table
dup_t1at theclusterlevel.Log in to the sys tenant and create a unit.
obclient> CREATE RESOURCE UNIT IF NOT EXISTS 2c5g MAX_CPU 2, MEMORY_SIZE '5G'; Query OK, 0 rows affectedCreate a resource pool with a unit num of 2.
obclient> CREATE RESOURCE POOL tenant_pool UNIT = '2c5g', UNIT_NUM = 2, ZONE_LIST = ('z1', 'z2', 'z3'); Query OK, 0 rows affectedCreate a user tenant named
obmysqland specify its locality distribution.obclient> CREATE TENANT obmysql resource_pool_list=('tenant_pool'), LOCALITY = "F@z1, F@z2, R@z3", PRIMARY_ZONE = "z1" SET ob_tcp_invited_nodes='%'; Query OK, 0 rows affectedLog in to the
obmysqluser tenant created in step 3 and create a replicated table.obclient> USE test; Database changed obclient> CREATE TABLE dup_t1(c1 INT) DUPLICATE_SCOPE = 'cluster'; Query OK, 0 rows affected(Optional) View the broadcast log stream information. The replicated table will be created on this log stream.
obclient> SELECT * FROM oceanbase.DBA_OB_LS WHERE FLAG LIKE "%DUPLICATE%"; +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+ | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN | DROP_SCN | SYNC_SCN | READABLE_SCN | FLAG | UNIT_LIST | +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+ | 1003 | NORMAL | z1;z2 | 0 | 0 | 1683267390195713284 | NULL | 1683337744205408139 | 1683337744205408139 | DUPLICATE | | +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+ 1 row in set(Optional) View the replica distribution of the replicated table in the sys tenant. The
REPLICA_TYPEfield indicates the replica type.obclient> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TABLE_NAME = "dup_t1"; +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+ | TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE | DUPLICATE_SCOPE | +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+ | 1002 | test | dup_t1 | 500002 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1003 | z1 | 11.xxx.xxx.xxx | 36125 | LEADER | FULL | CLUSTER | | 1002 | test | dup_t1 | 500002 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1003 | z1 | 11.xxx.xxx.xxx | 36124 | FOLLOWER | READONLY | CLUSTER | | 1002 | test | dup_t1 | 500002 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1003 | z2 | 11.xxx.xxx.xxx | 36127 | FOLLOWER | FULL | CLUSTER | | 1002 | test | dup_t1 | 500002 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1003 | z2 | 11.xxx.xxx.xxx | 36126 | FOLLOWER | READONLY | CLUSTER | | 1002 | test | dup_t1 | 500002 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1003 | z3 | 11.xxx.xxx.xxx | 36128 | FOLLOWER | READONLY | CLUSTER | | 1002 | test | dup_t1 | 500002 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1003 | z3 | 11.xxx.xxx.xxx | 36129 | FOLLOWER | READONLY | CLUSTER | +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+ 6 rows in setInsert data into and read from replicated tables just like regular tables. For a read request, if a proxy is used, it may be routed to any OBServer node. If connecting directly to an OBServer node, the read request will be executed on that node as long as the local replica is readable.
obclient> INSERT INTO dup_t1 VALUES(1); Query OK, 1 row affected obclient> SELECT * FROM dup_t1; +------+ | c1 | +------+ | 1 | +------+ 1 row in set
Create a columnar storage table named
tbl1_cg.obclient> CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);Create a table with a columnstore index.
obclient> CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX i1 (col2) WITH COLUMN GROUP(each column));Create a columnstore table with a columnstore index.
obclient> CREATE TABLE tbl3_cg (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX i1 (col2) WITH COLUMN GROUP(each column)) WITH COLUMN GROUP(each column);Set the skip index attribute for columns when creating a table.
obclient> 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) );Create table
tbl1, which has an integer columncol1. Specify that this operation should be executed with a degree of parallelism of 5, and specify that the data for the new tabletbl1will come from the query results of tabletbl2.obclient> CREATE /*+ parallel(5) */ TABLE tbl1 (col1 INT) AS SELECT col1 FROM tbl2;Create a table named
tbwith the macroblock-level Bloom filter persistence feature enabled.obclient> CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) enable_macro_block_bloom_filter = True;Create table
tbl1and specify the semi-structured encoding type asencodingand the frequency threshold as90.obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT) SEMISTRUCT_PROPERTIES=(ENCODING_TYPE=ENCODING, FREQ_THRESHOLD=90);Create table
tbto enable the new flat row storage format.obclient> CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) micro_block_format_version = 2;
Create a table-level STORAGE_CACHE_POLICY
Manually specify hot data.
CREATE TABLE test_table1 (c1 INT, c2 INT) storage_cache_policy (global = "hot");Specify hot data using the timeline method.
CREATE TABLE test_part_func_expr ( id INT, event_time DATETIME NOT NULL, data VARCHAR(100)) STORAGE_CACHE_POLICY ( BOUNDARY_COLUMN = event_time, HOT_RETENTION =1 YEAR ) PARTITION BY RANGE COLUMNS(`event_time`) ( PARTITION p_prev3h VALUES LESS THAN ("2018-01-01 00:00:00"), PARTITION p_prev2h VALUES LESS THAN ("2019-01-01 00:00:00"), PARTITION p_prev1h VALUES LESS THAN ("2020-01-01 00:00:00"), PARTITION p_current VALUES LESS THAN ("2021-01-01 00:00:00"), PARTITION p_next1h VALUES LESS THAN ("2022-01-01 00:00:00"), PARTITION p_max VALUES LESS THAN MAXVALUE );
Create partition-level STORAGE_CACHE_POLICY
The STORAGE_CACHE_POLICY of a partition when creating the partitioned table.
CREATE TABLE tbl3 (col1 INT, col2 INT, col3 INT) PARTITION BY RANGE(col1) SUBPARTITION BY KEY(col2, col3) SUBPARTITIONS 5 (PARTITION p0 VALUES LESS THAN(0) STORAGE_CACHE_POLICY = "hot", PARTITION p1 VALUES LESS THAN(100) STORAGE_CACHE_POLICY = "auto" );The STORAGE_CACHE_POLICY of a subpartition specified when creating a partitioned table.
CREATE TABLE tbl4 (col1 INT, col2 INT) PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2) (PARTITION p0 VALUES LESS THAN(100) (SUBPARTITION sp0 VALUES LESS THAN(2020) STORAGE_CACHE_POLICY = "hot", SUBPARTITION sp1 VALUES LESS THAN(2021)), PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION sp2 VALUES LESS THAN(2020), SUBPARTITION sp3 VALUES LESS THAN(2021) STORAGE_CACHE_POLICY = "hot", SUBPARTITION sp4 VALUES LESS THAN(2022)) );Specify the STORAGE_CACHE_POLICY for both indexes and table partitions when creating a table.
CREATE TABLE tbl5 ( c1 INT, c2 VARCHAR(50), INDEX idx1 (c1) STORAGE_CACHE_POLICY = (GLOBAL = "hot"), INDEX idx2 (c2) STORAGE_CACHE_POLICY = (GLOBAL = "auto") ) PARTITION BY RANGE (c1) (PARTITION p1 VALUES LESS THAN (100) STORAGE_CACHE_POLICY = "hot", PARTITION p2 VALUES LESS THAN (200) STORAGE_CACHE_POLICY = "auto", PARTITION p3 VALUES LESS THAN MAXVALUE STORAGE_CACHE_POLICY = "none");Example description:
- When creating table
tbl5, two indexes were created simultaneously, each specifying a different storage caching strategy. - The table is partitioned by the
c1column using RANGE partitioning, and each partition has a specified storage and caching strategy. - The STORAGE_CACHE_POLICY for indexes and table partitions can be set independently, enabling fine-grained storage management.
- When creating table
Create an asynchronous index
Example of creating an asynchronous full-text index:
CREATE TABLE doc_async (
c1 INT,
content VARCHAR(255),
ASYNC FULLTEXT INDEX idx_content(content)
) ORGANIZATION HEAP;
Example of creating an async search index:
CREATE TABLE t (
id BIGINT,
doc JSON,
ASYNC SEARCH INDEX idx_search (doc)
) ORGANIZATION HEAP ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';