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

Creating a non-partitioned table means creating a table with only one partition.

The following example statement creates a non-partitioned table:

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

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),
FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id),
primary key (c_w_id, c_d_id, c_id)
);

In the example, two tables are created, and some constraints are defined on the columns of the tables, including primary keys and foreign keys on different columns. For more information about primary keys and foreign keys, see Define column constraints.

When creating table columns, select the correct 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. This type of table allows you to read the latest modifications of data on any "healthy" replica. For users who require less frequent writes but have high demands for read operation latency and load balancing, replicated tables are an excellent choice.

After you create a replicated table, a replica of the table is created on every OBServer node within its tenant. One of these replicas is elected as the leader and accepts write requests, while the remaining replicas can only accept 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 determines that a replica remains "healthy" for a period, it grants the follower a lease. Simply put, the leader "trusts" the follower to remain "healthy" and provide strongly consistent read service for a certain period. During this "trust" period, the leader verifies the follower's replay progress before committing each replicated table transaction. Only after the follower replays the modifications of the current transaction will the leader notify the user that the transaction has been successfully committed. At this point, you can read the newly committed transaction's modifications on the follower.

The replicated table feature was available in OceanBase Database V3.x. In V4.x, due to significant architectural changes in OceanBase Database, the replicated table feature has been adapted to the new single-machine log stream architecture. It now includes partition-based readable version number verification and a log stream-based lease granting mechanism to ensure the correctness of strongly consistent reads.

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

Replicated table limitations

• Replicated tables:

  • Restrictions on creating replicated tables: The sys tenant and meta tenant 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 writes and reads on a replicated table in the same transaction. Pure write or pure read transactions for a replicated table are not a problem.

  • Attribute conversion:

    • Replicated tables and TableGroups are mutually exclusive. Modifying the TableGroup attribute of a replicated table will cause an error. When converting a regular table to a replicated table, if the regular table belongs to a TableGroup, the attribute change command will report an error.
    • Replicated table conversion depends on load balancing and transfer; ensure the related configuration parameters are enabled.

  • Routing:

    • If a write operation to a replicated table is performed in a transaction, and then the replicated table is queried, if it is randomly routed to a follower, the query may encounter the issue of an unreadable replica. The observer would then internally forward the request to the leader of the replicated table, affecting query performance.

      • ODP V4.3.3 has adjusted the routing strategy. If a write operation to a replicated table is performed, subsequent queries will be routed to the leader of the replicated table.

    • When you perform a query operation that involves joining a replicated table to a regular table, the query is randomly routed to the replicated table. (For JOIN queries, ODP routes the query based on the first table.) In this case, the query might be routed to a non-Leader node of the regular table, resulting in a remote execution plan.

    • If the attribute of a regular table changes to a replicated table, ODP cannot detect this change and cannot route the query to the replicated table to distribute the load.

  • If changes are made to a replicated table within a transaction, subsequent queries on that replicated table will behave as follows: when querying the replicated table, the generated execution plan initially selects the local replica. However, because changes have been made, an error indicating that the replica is unreadable is returned. The SQL statement retries by selecting the Leader replica, but since the plan cache cannot be hit, the query performance suffers.

    Note

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

• Broadcast log streams:

  • 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 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 attribute 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 and R replicas of the current tenant.

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

CREATE TABLE dup_t1 (c1 NUMBER,c2 NUMBER) 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. Subsequent replicated tables created will all be based on this broadcast log stream. The difference between a broadcast log stream and a regular log stream is that a broadcast log stream automatically deploys a replica on every OBServer node within the tenant. Under ideal conditions, this ensures that a replicated table can provide strongly consistent reads from any OBServer node. You can use the following SQL statement to view the broadcast log stream where the replicated tables of a tenant reside:

SELECT * FROM SYS.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 | 1684982852976428261 |     NULL | 1684983282912048623 | 1684983282912048623 | DUPLICATE |           |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
1 rows in set

In the example, the log stream with LS_ID 1003 is the broadcast log stream. All replicated tables of the current 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 request may be routed to any OBServer node for execution. If connecting directly, 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

The CREATE TABLE AS SELECT statement replicates the basic data types and data of a table, but does not include attributes such as constraints, indexes, or NOT NULL.

A sample statement is as follows:

CREATE TABLE t2_copy AS SELECT * FROM t2;

You cannot use the CREATE TABLE LIKE statement to copy a table's structure.

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’ (default value) is configured, 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. You can explicitly specify to create a columnstore or hybrid rowstore-columnstore table by using the WITH COLUMN GROUP option. You can also set the default storage format to columnstore or hybrid rowstore-columnstore as the default by configuring the default_table_store_format parameter.

For information about converting between rowstore and columnstore formats, see Change 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 NUMBER PRIMARY KEY, col2 VARCHAR2(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 NUMBER PRIMARY KEY, col2 VARCHAR2(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 details, 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 in a columnstore table increases, the speed of the major compaction may decrease.

Specify the table's update model

To specify the table's 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 to use the partial update mode. 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 to use the full-column update mode. Each update writes a complete row (delete the old row + insert the 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 only allows 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.

Limitations on updating models

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

• The following DML statements are prohibited:

  • UPDATE
  • DELETE
  • MERGE INTO
  • REPLACE

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

• The following DDL operations are prohibited:

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

Examples

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

  obclient> CREATE TABLE mer_tbl1 (col1 NUMBER, col2 NUMBER)
      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 NUMBER, col2 NUMBER)
      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 a table mer_tbl3 with the update model set to append_only.

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

  2. Insert data into the mer_tbl3 table.

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

  3. Query data from the mer_tbl3 table.

     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 the mer_tbl3 table.

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

     The return result is as follows:

     OBE-00600: internal error code, arguments: -4007, update append_only table is not supported
     

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

     obclient> DELETE FROM mer_tbl3 WHERE col1 = 1;
     

     The return result is as follows:

     OBE-00600: internal error code, arguments: -4007, delete from append_only table is not supported
     

References

• CREATE TABLE

• ALTER TABLE