CREATE TABLE

Purpose

This statement creates 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]
    | [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]
    | 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

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]

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

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'

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)
    | 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)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

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. This mode indicates normal operation. In this mode, the probability of triggering a major compaction after a data dump is extremely low.
• QUEUING: In this mode, the probability of triggering a major compaction after a data dump is low.
• MODERATE: This mode indicates moderate operation. In this mode, the probability of triggering a major compaction after a data dump is moderate.
• SUPER: This mode indicates super operation. In this mode, the probability of triggering a major compaction after a data dump is high.
• EXTREME: This mode indicates extreme operation. In this mode, the probability of triggering a major compaction after a data dump is high.

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

tokenizer_option

• SPACE: The default value. This option indicates that the tokenizer splits text by spaces. You can specify the following properties:

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

• NGRAM: This option indicates that the tokenizer splits text by N-grams (Chinese). You can specify the following properties:

  Property	Value range
  ngram_token_size	[1, 10]

• NGRAM2: This option indicates that the tokenizer splits text into consecutive characters of length between min_ngram_size and max_ngram_size. You can specify the following properties:

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

  Note

  For OceanBase Database V4.3.5, the NGRAM2 tokenizer is supported starting from V4.3.5 BP2.

• BENG: This option indicates that the tokenizer splits text by Beng (basic English). You can specify the following properties:

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

• IK: This option indicates that the tokenizer splits text by IK (Chinese). Only the utf-8 character set is supported. You can specify the following property:

  Property	Value range
  ik_mode	smart max_word

  Note

  For OceanBase Database V4.3.5, the IK tokenizer is supported starting from V4.3.5 BP1.

parser_properties

• min_token_size: The minimum token length. Default value: 3. Value range: 1 to 16.

• max_token_size: The maximum token length. Default value: 84. Value range: 10 to 84.

• ngram_token_size: The token length for the NGRAM tokenizer. This property is valid only for the NGRAM tokenizer. Default value: 2. Value range: 1 to 10.

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

  • smart: The default value. This mode uses the dictionary to improve tokenization accuracy. The boundaries of the dictionary words are prioritized, which may reduce unnecessary extensions.
  • max_word: This mode identifies the words defined in the dictionary but does not affect the maximum extension of tokenization. Even if the dictionary defines words, the max_word mode still tries to split the text into more words.

• min_ngram_size: The minimum token length. Value range: [1, 16].

• max_ngram_size: The maximum token length. Value range: [1, 16].

Here is an 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 partitioning. You can modify this option. Valid values:

  • true: The default value. Indicates that dynamic partitioning is enabled.
  • false: Indicates that dynamic partitioning is disabled.

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: Required. Specifies the time unit for partitioning. You cannot modify this option. Valid values:

  • hour: Partitions are created by hour.
  • day: Partitions are created by day.
  • week: Partitions are created by week.
  • month: Partitions are created by month.
  • year: Partitions are created by year.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the time for pre-creation of partitions. When dynamic partitioning is scheduled, partitions are pre-created to ensure that the maximum partition upper bound is greater than now() + precreate_time. You can modify this option. Valid values:

  • -1: The default value. Indicates that no partitions are pre-created.
  • 0: Indicates that only the current partition is pre-created.
  • n {hour | day | week | month | year}: Indicates the time span for pre-creation of partitions. For example, 3 hour indicates that partitions are pre-created for the past 3 hours.

  Note

  • When multiple partitions are pre-created, the partition boundary interval is TIME_UNIT.
  • The boundary of the first pre-created partition is the ceiling of the current maximum partition boundary based on TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the time for partition expiration. When dynamic partitioning is scheduled, all partitions with an upper bound less than now() - expire_time are deleted. You can modify this option. Valid values:

  • -1: The default value. Indicates that partitions do not expire.
  • 0: Indicates that all partitions except the current one expire.
  • n {hour | day | week | month | year}: Indicates the time for partition expiration. For example, 1 day indicates that partitions expire after 1 day.

• TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone for determining the current time and the partition key values of the date, datetime, and year types. You cannot modify this option. Valid values:

  • default: The default value. Indicates that the tenant time zone is used. For other types, the time_zone field must be default.
  • time_zone: Indicates a custom time zone offset. For example, +8:00.

• BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}: Optional. Specifies the timestamp precision of the bigint type partition key. You cannot modify this option. Valid values:

  • none: The default value. Indicates that no precision is specified (the partition key is not of the bigint type).
  • us: Microsecond precision.
  • ms: Millisecond precision.
  • s: Second precision.

For more information about how to create a dynamic partition table, see Create a dynamic partition table.

Here is an 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')
        );

Examples

• Create a database table.

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

• Create a table with an index.

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

• Create a table tbl3 with a function-based 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 a range partition as the primary partition and a key partition as the subpartition.

  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 two columns: one of type gbk and another of type utf8.

  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 5% space reserved for macroblocks.

  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 a table tbl8 and set the 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 the table ref_t2. When an UPDATE operation affects the key values in the parent table that match the rows in the child table, execute the SET NULL operation.

  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 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 a table tbl11 based on the table tbl10 and view the table 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 number 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 a user tenant obmysql and specify the 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 under 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 and read/write data from the replicated table like a regular table. For a read request, if you use a proxy, the read request may be routed to any OBServer node; if you directly connect to an OBServer node, the read request will be executed on the connected OBServer 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 tbl1_cg.

  CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);
  

• Create a table with a columnstore index.

  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.

  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);
  

• Create a table and specify the Skip Index attribute for the column.

  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 table tbl1 with an integer column col1, and specify that the operation uses 5 parallel threads to complete. Also, specify that the data content of the new table tbl1 will be sourced from the query results of table tbl2.

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

• Create a table tb with a persistent bloom filter at the macroblock level.

  CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) enable_macro_block_bloom_filter = True;
  

References

• Create a table
• Change a table