Create a table

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

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

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 preceding example, two tables are created. Some constraints, including 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 Overview.

Create a replicated table

Replicated table is a special table schema in OceanBase Database. A replicated table can read the latest data changes from any replica and applies to scenarios with low write frequency requirements but high read latency and read load balancing requirements.

After you create a replicated table, the replicas of this table are distributed on all OBServer nodes of the tenant. One of these replicas is elected as the leader to receive read and write requests, and other replicas serve as followers to receive only read requests.

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, OceanBase Database V4.x builds the partition-based readable version verification and log stream-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 and read-only replicas in the current tenant.

If you do not specify DUPLICATE_SCOPE when you create a table, the default value none takes effect.

CREATE TABLE dup_t1 (c1 NUMBER,c2 NUMBER) 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 SYS.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 | 1684982852976428261 |     NULL | 1684983282912048623 | 1684983282912048623 | DUPLICATE |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
1 row in set

In this example, the log stream with the ID (LS_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 Overview.

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), read requests of a replicated table are routed to any OBServer node whereas read requests of a normal table can be routed only to the leader node. When you directly connect to OceanBase Database, if the local replica is readable, read requests will be executed on the OBServer node that you directly connect to. For more information about the routing rules of ODP, see Partitioned table-based routing for strong-consistency reads.

Create a table by copying the data in an existing table

You can execute the CREATE TABLE AS SELECT statement to copy data types and data from a table. This statement does not copy table attributes. For example, indexes and constraints such as NOT NULL are lost during the copy operation.

Here is an example:

CREATE TABLE t2_copy AS SELECT * FROM t2;

You cannot use the CREATE TABLE LIKE statement to copy table schemas.