Create a table

Use the CREATE TABLE statement to create a table.

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

Create a non-partitioned table

A non-partitioned table is a table with only one partition.

Here is a sample statement for creating 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)
);

Query OK, 0 rows affected

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

This example creates two tables and defines some constraints on the columns of the tables, including primary keys and foreign keys created on different columns. For information about constraints, see Define column constraints.

When creating columns for a table, choose the correct data types. For information about data types, see Overview.

Note

• To ensure system performance and facilitate maintenance, it is recommended that you specify a primary key or a unique key when creating a table. If no field can be used as the primary key, you can add a numeric column as the primary key and use the Sequence object of the Oracle tenant to generate sequential values for this column. For information about sequences, see Manage sequences.

• Since adding a table's primary key is not supported through ALTER TABLE, you must set the primary key when creating the table.

Create a table by copying data from an existing table

Use 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 a sample statement:

obclient>CREATE TABLE t2_copy AS SELECT * FROM t2;
Query OK, 0 rows affected (0.10 sec)

The CREATE TABLE LIKE statement cannot be used to copy table schemas.

Create a duplicate table

Creating a duplicate table is an advanced tuning technique in OceanBase Database.

In general, OceanBase uses a three-replica architecture. By default, each partition of a table has three replicas in the OceanBase database, one of which works as the leader and the other two work as followers. By default, the leader provides write and read services.

You can create a duplicate table on every server in the tenant. The tenant can have one leader and two or more followers. Full data synchronization is implemented to maintain strong consistency between the leader and followers. This allows your application to execute specific SQL JOIN queries on the same server for better performance.

To create a duplicate table, add a DUPLICATE_SCOPE clause to the CREATE TABLE statement.

Here is a sample statement:

obclient>CREATE TABLE table_name (i_id int,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id int,
primary key(i_id)) COMPRESS FOR QUERY pctfree=0 BLOCK_SIZE=16384
duplicate_scope='cluster' locality='F,R{all_server}@doc_1, F,R{all_server}@doc_2,F,R{all_server}@doc_3' primary_zone='doc_1';