CREATE TABLE

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] [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]
    | [FULLTEXT] {INDEX | KEY} [index_name] [index_type] (key_part,...) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)] [FTS_INDEX_TYPE [=] {MATCH | PHRASE_MATCH}]
      [index_option_list] [index_column_group_option]
    | SEARCH INDEX [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)] [ON UPDATE CURRENT_TIMESTAMP]
         [VISIBLE | INVISIBLE] [SRID INTNUM]
   | 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

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

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]...)]

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 since V4.4.1. Use SEMISTRUCT_PROPERTIES instead.
    | MICRO_BLOCK_FORMAT_VERSION [=] {1|2}
    | STORAGE_CACHE_POLICY (storage_cache_policy_option)
    | CLUSTER BY (column_name_list)
    | HMS_CATALOG_NAME [=] string_value
    | COLUMN_NAME_CASE_SENSITIVE [=] {True | False}
    | MERGE_ENGINE = {delete_insert | partial_update | append_only}
    | TTL [=] col_name + INTERVAL interval_num ttl_unit BY COMPACTION
    | DELTA_FORMAT [=] 'flat | encoding'
    | SKIP_INDEX_LEVEL [=] {1 | 0}

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" | "cold" | "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)
    | PARTITION BY RANDOM [SIZE('size_value')]

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" | "cold" | "none"}]
    | SUBPARTITION BY LIST(expression)

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" | "block"}

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

table_mode_value

• 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: Indicates super. In this mode, a compaction is triggered after a minor compaction with a high probability.
• EXTREME: Indicates extreme. In this mode, the probability of triggering a compaction after a minor compaction is higher.

For more information about major compactions, see Adaptive major compaction.

tokenizer_option

• SPACE: The default value. Indicates that the text is segmented by spaces. The following attributes can be specified:

  Attribute	Value range
  min_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:

  Attribute	Value range
  ngram_token_size	[1, 10]

• NGRAM2: Indicates that the text is split into consecutive characters within the range of min_ngram_size to max_ngram_size. The following attributes can be specified:

  Attribute	Value range
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

• BENG: word segmentation based on Beng (Basic English). The following attributes can be specified:

  Attribute	Value range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK: Indicates the word segmentation method based on IK (Chinese). Currently, only the utf-8 character set is supported. The following attributes can be specified:

  Attribute	Value range
  ik_mode	smart max_word

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 for NGRAM. This parameter is valid only for the NGRAM tokenizer. The default value is 2, and the valid range is 1 to 10.

• ik_mode: The tokenization mode of the IK tokenizer. Valid values:

  • smart: The default value. It indicates that the vocabulary in the dictionary is used to improve the accuracy of word segmentation, and the boundaries between words in the dictionary are given priority. This may reduce unnecessary expansions.
  • max_word: Indicates that words defined in the dictionary will be recognized, but this will not affect the maximum expansion of word segmentation. Even if a word is defined in the dictionary, the max_word mode will still attempt to split the text into more words.

• min_ngram_size: the minimum length of a n-gram. The value range is [1, 16].

• max_ngram_size: the maximum length of a n-gram. The value range is [1, 16].

Example:

CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT,
    FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word'));

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 precreation time. When you schedule a dynamic partition management task, 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 that partitions are not pre-created.
  • 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 hour means 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 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 you schedule a dynamic partition management task, all partitions whose upper bound < now() - expire_time are deleted. The value 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 day indicates the partition expires in 1 day.

• TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone to use when comparing the current time with the partitioning key of a time type (date, datetime, or year) column. The value 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, the time_zone field must be set to default.
  • time_zone: The custom time zone offset. For example, a time zone offset such as +8:00.

• BIGINT_PRECISION = {'none'| 'ms' | 's'}: Optional. Specifies the timestamp precision for the partitioning key of the bigint type. This parameter cannot be modified. Valid values:

  • none: The default value, indicating no precision (the partitioning key is not of the bigint type).
  • ms: millisecond precision.
  • s: second precision.

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

No action is required at this time.

Examples

• Create a database table.

  obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY, c2 VARCHAR(50));
  Query OK, 0 rows affected
  

• Create an indexed table.

  obclient> CREATE TABLE tbl2 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2));
  Query OK, 0 rows affected
  

• Create a table named tbl3 with 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 8 hash partitions.

  obclient> CREATE TABLE tbl4 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8;
  Query OK, 0 rows affected
  

• Create 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 affected
  

• Create 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 affected
  

• Enable encoding and use zstd compression, with a macroblock reserved space of 5%.

  obclient> CREATE TABLE tbl7 (c1 INT, c2 INT, c3 VARCHAR(64))
        COMPRESSION 'zstd_1.0'
        ROW_FORMAT DYNAMIC
        PCTFREE 5;
  Query OK, 0 rows affected
  

• Create table tbl8 and set the degree of parallelism to 3.

  obclient> CREATE TABLE tbl8(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3;
  Query OK, 0 rows affected
  

• Use 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 affected
  

• Specify a foreign key for table ref_t2 and execute the SET NULL operation when the UPDATE operation affects the key values in the parent table that match rows in the child table.

  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 affected
  

• Create a table named tbl10 with a CHECK constraint 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 set
  

• Create table tbl11 based on table tbl10 and 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 set
  

• Create a replicated table dup_t1 at the cluster level.

  1. 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 affected
     

  2. Create 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 affected
     

  3. Create the user tenant obmysql and 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 affected
     

  4. Log in to the obmysql user 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
     

  5. (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      |
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
     |  1003 | NORMAL | z1;z2        |             0 |           0 | 1683267390195713284 |     NULL | 1683337744205408139 | 1683337744205408139 | DUPLICATE |
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
     1 row in set
     

  6. (Optional) View the replica distribution of the replicated table in the sys tenant. The REPLICA_TYPE field 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 set
     

  7. Insert data into and read from replicated tables just like regular tables. For a read request, if a proxy is used, the request 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 columnstore 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);
  

• Identify the SKIP INDEX attribute for columns during table creation.

  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 a spatial column with an SRID attribute.

  obclient> CREATE TABLE tbl_srid(id INT PRIMARY KEY, geom POINT SRID 0);
  

• Create table tbl1, which has an integer column col1. Specify that this operation should be executed with a degree of parallelism of 5, and specify that the data for the new table tbl1 will come from the query results of table tbl2.

  obclient> CREATE /*+ parallel(5) */ TABLE tbl1 (col1 INT) AS SELECT col1 FROM tbl2;
  

• Create a table named tb with 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 tbl1 and specify the semi-structured encoding type as encoding and the frequency threshold as 90.

  obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT) SEMISTRUCT_PROPERTIES=(ENCODING_TYPE=ENCODING, FREQ_THRESHOLD=90);
  

• Create table tb and enable the new Flat row storage format (version 2).

  obclient> CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) micro_block_format_version = 2;
  

• Create a table with a TTL of 7 days. The TTL column is the internal hidden column ora_rowscn.

  obclient(root@mysql001)[infotest]> CREATE TABLE ttl_tbl1(
    id INT PRIMARY KEY,
    val VARCHER(100)
  ) MERGE_ENGINE = append_only TTL ora_rowscn + INTERVAL 7 DAY BY COMPACTION;
  

• Create a table with a Time-To-Live (TTL) column whose data expires after 7 days. The TTL column is a user-defined time column.

  obclient(root@mysql001)[infotest]> CREATE TABLE ttl_tbl2(
        order_id INT PRIMARY KEY,
        order_time DATETIME NOT NULL,
        payment_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) 
        MERGE_ENGINE = delete_insert TTL order_time + INTERVAL 7 DAY BY COMPACTION;
  

• Create table tbl_e and specify the new encoding incremental data format for the incremental data.

  obclient> CREATE TABLE tbl_e(col1 INT PRIMARY KEY, col2 INT) DELTA_FORMAT = 'encoding';
  

References

• Create a table
• Modify a table