Create a table

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

This topic describes how to create non-partitioned tables. For information about how to create partitioned tables, see Create a partitioned table.

Create a non-partitioned table

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

A sample statement 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

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

Pay attention to data types when you create table columns. For more information about SQL data types, see Data types.

Create a replicated table

A replicated table is a special type of table in OceanBase Database. A replicated table can read the latest data modifications from any healthy replica. If you do not frequently write data and are more concerned about the operation latency and load balancing, a replicated table is a good choice.

After you create a replicated table, a replica of the replicated table is created on each OBServer node in the tenant. One of these replicas is elected as the leader to receive write requests, and the other replicas serve as followers to receive only read requests.

All followers must report their status, including the replica replay progress (data synchronization progress), to the leader. Generally, the replica replay progress on a follower lags behind that on the leader. A follower is considered by the leader as healthy only if the data latency between the follower and the leader is within the specified threshold. A healthy follower can quickly synchronize data modifications from the leader. If the leader considers a follower as healthy within a period of time, it will grant a lease period to the follower. In other words, the leader believes that the follower can keep healthy and provide strong-consistency read services within the lease period. During this lease period, the leader confirms the replay progress on the follower before each replicated table transaction is committed. The leader returns the commit result of a transaction only after the follower successfully replays the modifications in the transaction. At this time, you can read the modifications in the committed transaction from the follower.

The replicated table feature is already supported in OceanBase Database V3.x. However, the database architecture is significantly modified in OceanBase Database V4.x. Therefore, to adapt to the new architecture of standalone log streams (LSs), OceanBase Database V4.x builds the partition-based readable version verification and LS-based lease granting mechanisms to ensure the correctness in strong-consistency reads.

In addition, OceanBase Database V4.x improves the capability of switching the leader without terminating transactions. In OceanBase Database V4.x, replicated table transactions that are not committed when a leader switch is initiated by you or the load balancer can continue after the leader is switched, which is not supported in OceanBase Database V3.x. Compared with a replicated table of OceanBase Database V3.x, a replicated table of OceanBase Database V4.x has higher transaction write performance and more powerful disaster recovery capabilities. In addition, a replica crash has slighter impacts on read operations.

To create a replicated table, you must add the DUPLICATE_SCOPE option to the CREATE TABLE statement. You can create a replicated table only in a user tenant. You cannot create a replicated table in the sys tenant. The SQL syntax for creating a replicated table is as follows:

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

In the syntax, the DUPLICATE_SCOPE option specifies the attribute of the replicated table. Valid values include:

• none: The table is a normal table.
• cluster: The table is a replicated table. The leader must copy transactions to all full-featured (F) and read-only (R) replicas in the current tenant.

If you do not specify DUPLICATE_SCOPE when you create a table, the default value none takes effect. Currently, OceanBase Database supports only cluster-level replicated tables.

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

When the first replicated table is created for a tenant, the system automatically creates a broadcast log stream for the tenant. Then, subsequent replicated tables of the tenant will all be created in the broadcast log stream. A broadcast log stream differs from a normal log stream in that the broadcast log stream will automatically deploy a replica on each OBServer node of the tenant to ensure that the replicated table can provide strong-consistency reads on any OBServer node in ideal conditions. You can execute the following SQL statement to view the broadcast log stream where the replicated tables of a tenant reside:

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

A sample query result is as follows:

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

In this example, the log stream with the ID of 1003 is a broadcast log stream. All replicated tables of the current tenant are created in this log stream. For more information about broadcast log streams, see Replicas.

After a replicated table is created, you can perform insert and read/write operations on the replicated table as on a normal table. When you connect to OceanBase Database by using OceanBase Database Proxy (ODP), your read requests may be routed to any OBServer node. When you directly connect to OceanBase Database, if the local replica is readable, your read requests will be executed on the OBServer node that you directly connect to. For more information about database connection methods, see Connection methods.

Create a table by copying the data in an existing table

Copy table data

You can execute the CREATE TABLE AS SELECT statement to copy data from a table. The constraints, indexes, default values, and partitions are lost when you copy the data.

A sample statement is as follows:

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

Copy the table schema

You can also execute the CREATE TABLE LIKE statement to copy the schema but not the data of a table.

The syntax is as follows:

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