Create a table

You can use the CREATE TABLE statement to create a table.

This section describes how to create a non-partitioned table. For information about how to create and use a partitioned table, see Create a partitioned table.

Create a non-partitioned table

A non-partitioned table is a table that has only one partition.

The sample statement for creating a non-partitioned table is as follows:

obclient>CREATE TABLE table_name1(w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);

Query OK, 0 rows affected (0.09 sec)

obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT null
, c_discount decimal(4, 4)
, c_credit char(2)
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, c_ytd_payment decimal(12, 2)
, c_payment_cnt int
, c_credit_lim decimal(12, 2)
, c_street_1 varchar(20)
, c_street_2 varchar(20)
, c_city varchar(20)
, c_state char(2)
, c_zip char(9)
, c_phone char(16)
, c_since date
, c_delivery_cnt int
, c_data varchar(500)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);

Query OK, 0 rows affected

The example creates two tables and defines constraints on their columns, including primary keys and foreign keys. For more information about primary keys and foreign keys, see Define column constraints.

When creating table columns, select the appropriate data types. For detailed descriptions of SQL data types, see Data types.

Create a replicated table

A replicated table is a special type of table in OceanBase Database. Data modifications are immediately visible in all healthy replicas. Replicated tables are an excellent choice for users who require low write frequency but high read performance and load balancing.

After you create a replicated table, a replica of the table is created on all OBServer nodes in the tenant. One of these replicas is elected as the leader and receives write requests, while the remaining replicas can only receive read requests.

All replicas must report their status to the leader, primarily the replay progress, which is the data synchronization progress. Generally, the replay progress of followers lags slightly behind that of the leader. As long as the lag does not exceed a certain threshold, the leader considers the replica to be in a "healthy" state and capable of quickly replaying the modifications on the leader. After the leader deems a replica "healthy" for a period of time, it grants the follower a lease. Simply put, the leader "trusts" that the follower will remain "healthy" and can provide strongly consistent read services during this period. During this "trust" period, the leader verifies the follower's replay progress before committing each replicated table transaction. Only after the follower has replayed the modifications of the transaction will the leader notify the user that the transaction has been committed successfully. At this point, users can read the modifications of the newly committed transaction from the follower.

The copy table feature has been available since OceanBase Database V3.x. For V4.x, due to the significant architectural changes in OceanBase Database, the copy table feature in V4.x has been adapted to the new single-machine log stream architecture. It incorporates partition-based readable version number verification and a lease granting mechanism based on log streams to ensure the correctness of strong-consistency reads.

Furthermore, the replication table feature in V4.x has been enhanced to support leader switchover without killing transactions. When a leader switchover is initiated by a user or load balancing, uncommitted replication table transactions will not be interrupted as they were in V3.x; instead, they can continue after the leader switchover. Compared to V3.x, V4.x also offers improved write transaction performance and stronger disaster recovery capabilities, with less impact on read operations when a replica fails.

Limitations on copying tables

• Copy table:

  • Limitations on creating replicated tables: The sys and meta tenants do not have broadcast log streams and therefore do not support creating replicated tables.

  • Write performance is affected by the number of nodes: Because writes to a replicated table need to be synchronized to all replicas, the larger the number of nodes, the greater the impact on write performance.

    • Solution: Avoid performing both write and read operations on a replicated table within the same transaction. Transactions that perform only write or only read operations on a replicated table are acceptable.

  • Attribute conversion:

    • Copy tables and tablegroups are mutually exclusive. Modifying the tablegroup attribute of a copied table will result in an error. When converting a regular table to a copied table, if the regular table belongs to a certain tablegroup, the attribute change command will report an error.
    • Table conversion by copying depends on load balancing and transfer. Ensure that the related parameters are enabled.

  • Routing:

    • If a write operation is performed on a replicated table during a transaction, and then the same table is queried, if the query is randomly routed to a follower, it may encounter an unreadable replica. In this case, Observer internally forwards the request to the leader of the replicated table, which degrades query performance.

      • The routing strategy of ODP V4.3.3 has been adjusted. For write operations on a replicated table, subsequent queries are routed to the leader of the replicated table.

    • When you copy a JOIN query from a table to an ordinary table, the query will be randomly routed according to the copied table (for a JOIN query, ODP routes it based on the first table). In this case, it may be routed to a non-Leader node of the ordinary table, resulting in a remote plan.

    • When a regular table is changed to a replicated table, ODP cannot detect this change and cannot route requests to the replicated table to distribute load.

  • During a transaction, if changes are made to a replicated table, subsequent query operations: When querying the replicated table, the generated execution plan will select the local replica. However, because changes have been made, an error indicating that the replica is unreadable is reported. The SQL retries and selects the Leader replica. At this point, the plan cache cannot be hit, leading to poor query performance.

    Note

    In a transaction, if changes are made to a replicated table in OceanBase Database, the system always prioritizes selecting the Leader replica of the replicated table during queries, rather than directly choosing the local replica. This ensures improved query efficiency and avoids incorrectly selecting a replica.

• Broadcast log stream:

  • Each user tenant can have at most one broadcast log stream.
  • Attribute conversion between a broadcast log stream and a regular log stream is not supported.
  • Broadcast log streams cannot be manually deleted; they are currently deleted when the tenant is deleted.

Syntax for creating a replicated table

The syntax for creating a replicated table is to add the DUPLICATE_SCOPE option after the CREATE TABLE statement. Only user tenants can create replicated tables; the sys tenant cannot create them. The SQL statement for creating a replicated table is as follows:

CREATE TABLE table_name column_definition DUPLICATE_SCOPE='none | cluster';

Here, the DUPLICATE_SCOPE parameter specifies the attributes of the replicated table. Valid values are as follows:

• none: Indicates that the table is a regular table.
• cluster: Indicates that the table is a replicated table, and the Leader must replicate the transaction to all F replicas and R replicas of the current tenant.

If the DUPLICATE_SCOPE parameter is not specified when creating a table, the default value is none.

CREATE TABLE dup_t1 (c1 int,c2 int) DUPLICATE_SCOPE= 'cluster';

When the first replicated table for a tenant is created, the system simultaneously creates a special log stream—the broadcast log stream. All newly created replicated tables thereafter are created on this broadcast log stream. The difference between a broadcast log stream and a regular log stream is that the broadcast log stream automatically deploys a replica on every OBServer node within the tenant. Under ideal circumstances, this ensures that a replicated table can provide strongly consistent reads from any OBServer node. You can use the following SQL to view the broadcast log stream where the tenant's replicated tables reside:

SELECT * FROM oceanbase.DBA_OB_LS WHERE flag LIKE "%DUPLICATE%";

An example of the query result is shown below.

+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
| 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 rows in set

In the example, the log stream with LS_ID 1003 is the broadcast log stream, and all replicated tables of the tenant are created on this log stream. For more information about broadcast log streams, see Replica introduction.

After a replicated table is successfully created, it can be used for insertion and read/write operations just like a regular table. The difference is that for read requests, if connecting to the database via a proxy, the read request may be routed to any OBServer node for execution. If connecting directly to the database, as long as the local replica is readable, the system will execute the read request on the directly connected OBServer node. For more information about database connection methods, see Overview of connection methods.

Create a new table by replicating data from an existing table

Replicate table data

You can use the CREATE TABLE AS SELECT statement to replicate table data, but the structure is not completely identical, and information such as constraints, indexes, default values, and partitions is lost.

A sample statement is as follows:

obclient>CREATE TABLE t1_copy AS SELECT * FROM t1;
Query OK, 3 rows affected

Copy table structure

You can use the CREATE TABLE LIKE statement to copy a table's structure, but not its data.

A sample statement is as follows:

obclient>CREATE TABLE t1_like like t1;
Query OK, 0 rows affected

Create a rowstore table

OceanBase Database supports creating rowstore tables and converting between rowstore and columnstore formats.

When the parameter default_table_store_format='row' is set (which is the default), tables are created as rowstore tables by default. When default_table_store_format is set to a value other than row, you can create a rowstore table by specifying the WITH COLUMN GROUP(all columns) option.

For information about converting between rowstore and columnstore formats, see Modify a table. For information about creating a columnstore index, see Create an index.

Specify WITH COLUMN GROUP(all columns) to create a rowstore table.

Example:

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

Create a columnstore table

OceanBase Database supports creating columnstore tables, converting between rowstore and columnstore formats, and creating columnstore indexes. By default, when you create a table in OceanBase Database, it is a rowstore table. You can explicitly specify to create a columnstore table or a hybrid rowstore-columnstore table by setting the WITH COLUMN GROUP option.

For information about converting between rowstore and columnstore formats, see Modify a table. For information about creating columnstore indexes, see Create an index.

Specify WITH COLUMN GROUP(all columns, each column) to create a hybrid rowstore-columnstore table.

Example:

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

Specify WITH COLUMN GROUP(each column) to create a columnstore table.

Example:

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

When you create and use a columnstore table, if you import a large amount of data, you need to perform a major compaction to improve read performance and collect statistics to adjust execution strategies.

• Major compaction: After bulk data import, it is recommended to perform a major compaction. This helps improve read performance because the major compaction organizes fragmented data, making it more contiguous in physical storage and reducing disk I/O during reads. After data import, trigger a major compaction within the tenant to ensure all data is compacted to the baseline layer. For more information, see MAJOR AND MINOR.

• Statistics collection: After the major compaction is complete, it is recommended to collect statistics. This is crucial for the optimizer to generate effective query plans and execution strategies. Execute GATHER_SCHEMA_STATS to collect statistics for all tables, and monitor the collection progress through the view GV$OB_OPT_STAT_GATHER_MONITOR.

Note that as the data volume of a columnstore table increases, the speed of the major compaction may decrease.

Create a heap-organized table

OceanBase Database supports two types of table organization: Index Organized Table and Heap Organized Table.

Limitations

• The Unique Key Local or the Primary Key of a heap-organized table must include all partition keys.

• When creating an index on a heap-organized table, the index name cannot be the same as the name of the first column of the primary key.

• The following DDL operations are not currently supported for heap-organized tables in OceanBase Database:

  • Primary key constraint operations
  • Modifying a column into the primary key
  • Changing the type length of a primary key column from larger to smaller
  • Adding a primary key column
  • Modifying the primary key to an auto-increment column
  • Dropping a primary key column
  • Partition splitting

Syntax for specifying table organization

To specify the table organization when creating a table, add the ORGANIZATION option after the CREATE TABLE statement. The SQL statement is as follows:

CREATE TABLE table_name column_definition ORGANIZATION [=] {INDEX | HEAP};

The ORGANIZATION parameter specifies the storage order of data rows in a table. Valid values are as follows:

• INDEX: Indicates that the table is a clustered index table.
• HEAP: Indicates that the table is a heap table.

If the ORGANIZATION option is not specified, its value is the same as the value of the default_table_organization parameter.

Examples:

• Specify the value of the ORGANIZATION attribute as HEAP when creating a table.

  CREATE TABLE ora_tbl1 (col1 INT, col2 INT) ORGANIZATION = HEAP;
  

• When creating a table, do not specify ORGANIZATION.

  After modifying the tenant-level parameter default_table_organization, create a table. For more information about the default_table_organization parameter, see default_table_organization.

  Note

  The default_table_organization parameter applies only to user tenants in MySQL-compatible mode of OceanBase Database. The sys (system) tenant and user tenants in Oracle-compatible mode do not support it.

  1. Set the value of the default_table_organization parameter to HEAP.

     ALTER SYSTEM SET default_table_organization = 'HEAP';
     

  2. Create a table named ora_tbl2.

     CREATE TABLE ora_tbl2(col1 INT, col2 INT);
     

  3. View the definition of the ora_tbl2 table.

     SHOW CREATE TABLE ora_tbl2;
     

     The return result is as follows:

     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table    | Create Table                                                                                                                                                                                                                                                                                                                 |
     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | ora_tbl2 | CREATE TABLE `ora_tbl2` (
       `col1` int(11) DEFAULT NULL,
       `col2` int(11) DEFAULT NULL
     ) ORGANIZATION HEAP DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

Specify clustered columns when creating a table

To specify clustered columns when creating a table, add the CLUSTER BY table option after the CREATE TABLE statement. The SQL statement is as follows:

CREATE TABLE table_name (table_definition_list) CLUSTER BY (column_name_list);

column_name_list:
    column_name [, column_name ...]

Parameter description:

column_name: Indicates the clustered column used for sorting.

Example:

Create a table named cb_tbl1 sorted by col2 and col4.

obclient> CREATE TABLE cb_tbl1 (
    col1 INT PRIMARY KEY,
    col2 INT,
    col3 DECIMAL(10, 2),
    col4 TIMESTAMP
    )
    CLUSTER BY (col2, col4);

Create a temporary table

A temporary table is a special session-level table used primarily for temporarily storing intermediate data.

Limitations

• Temporary table routing: Since temporary tables need to support load balancing like regular tables, they cannot be bound to a specific server by default. Consider disabling load balancing when using them.

• In OceanBase Database at the Serializable isolation level, write operations on temporary tables created within the same transaction are not supported.

• If the current session has a temporary table with the same name as a regular table, it is not recommended to use the database-level statistics collection feature in that session, as the results may be unexpected.

• Temporary tables are cleaned up through background logic. To avoid significant impact on business operations from their DDL, the cleanup speed is limited. Therefore, scenarios with extensive use of temporary tables are advised to manually delete them promptly.

• DBLink cannot access temporary tables.

• When connecting to an OBServer via ODP, you need to manually adjust the server_protocal, client_session_id_version, and proxy_id parameters.

  The specific steps are as follows:

  • Run the following command to modify the communication protocol configuration of OBProxy.

    ALTER proxyconfig SET server_protocol = 'OceanBase 2.0';
    

  • Run the following command to set the algorithm for generating the Client Session ID to version 2.

    ALTER proxyconfig SET client_session_id_version = 2;
    

  • Run the following command to change the ODP ID to 1. Different ODPs require different numbers to ensure the generated Client Session IDs do not conflict.

    ALTER proxyconfig SET proxy_id = 1;
    

Usage example

Create a temporary table named tbl1:

CREATE TEMPORARY TABLE tbl1(col1 INT);

Specify the table's update model

To specify the table update model when creating a table, add the MERGE_ENGINE table option after the CREATE TABLE statement. The SQL statement is as follows:

CREATE TABLE table_name column_definition
    MERGE_ENGINE = {delete_insert | partial_update | append_only};

The MERGE_ENGINE parameter specifies the table's update model. Valid values are as follows:

• partial_update: Indicates that the partial update model is used. Each update only records the modified columns (delta), which saves storage space. However, queries require merging multiple data copies to obtain the latest values. This mode is suitable for OLTP scenarios with frequent updates and low query requirements.
• delete_insert: Indicates that the full-column update model is used. Each update writes a complete row (delete old row + insert new row), prioritizing query performance. This mode supports skip indexing for incremental data (Memtable/Delta SSTable). During queries, incremental data can be filtered and pushed down. If the filtering result does not involve updating baseline data, baseline and incremental data can be processed in batches separately, reducing read amplification. This mode is suitable for OLAP scenarios with a high proportion of incremental data, frequent execution of complex queries, or batch processing and analysis.
• append_only: Indicates that the table's update mode allows only INSERT operations and prohibits all other DML and DDL operations that modify stored data. To clean up expired data in this mode, you can use the TTL feature. For detailed usage restrictions, see [Usage restrictions of the update model](#Usage restrictions of the update model).

If the MERGE_ENGINE option is not specified, its value is the same as the value of the default_table_merge_engine parameter.

Application scenarios

For more information about configuration scenarios, see Configuration best practices.

Usage restrictions of the update model

When the table update model MERGE_ENGINE is set to append_only, the following restrictions apply:

• The following DML syntax is prohibited:

  • UPDATE
  • DELETE
  • MERGE INTO
  • REPLACE

• The INSERT [IGNORE] xxx ON DUPLICATE KEY UPDATE syntax is not prohibited, but an error is reported when actual data modification occurs.

• The following DDL is prohibited:

  • TRUNCATE tables and partitions
  • Partition exchange
  • Drop partitions
  • Drop columns
  • Reduce column range
  • Dynamic partitioning
  • Clear obsolete columns
  • Add a column (with DEFAULT VALUE)
  • Add an auto-increment column

Examples

• Create table mer_tbl1 with the update model set to full column update (delete_insert).

  obclient> CREATE TABLE mer_tbl1 (col1 INT, col2 INT)
      MERGE_ENGINE = delete_insert;
  

• Create a columnstore table mer_tbl2 with the update model set to full column update (delete_insert).

  obclient> CREATE TABLE mer_tbl2 (col1 INT, col2 INT)
      MERGE_ENGINE = delete_insert
      WITH COLUMN GROUP(each column);
  

• Create a table in append_only mode and perform INSERT, SELECT, UPDATE, and DELETE operations.

  1. Create table mer_tbl3 with the update model set to append_only.

     obclient> CREATE TABLE mer_tbl3 (col1 INT PRIMARY KEY, col2 VARCHAR(100))
         MERGE_ENGINE = append_only;
     

  2. Insert data into table mer_tbl3.

     obclient> INSERT INTO mer_tbl3 VALUES(1, 'oceanbase');
     

  3. Query data from table mer_tbl3.

     obclient> SELECT * FROM mer_tbl3;
     

     The return result is as follows:

     +------+-----------+
     | col1 | col2      |
     +------+-----------+
     |    1 | oceanbase |
     +------+-----------+
     1 row in set
     

  4. An error occurs when updating data in table mer_tbl3.

     obclient> UPDATE mer_tbl3 SET col2 = 'database' WHERE col1 = 1;
     

     The return result is as follows:

     ERROR 1235 (0A000): update append_only table is not supported
     

  5. An error occurs when deleting data from table mer_tbl3.

     obclient> DELETE FROM mer_tbl3 WHERE col1 = 1;
     

     The return result is as follows:

     ERROR 1235 (0A000): delete from append_only table is not supported
     

References

• CREATE TABLE

• ALTER TABLE