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 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, 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 (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.

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 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 About 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

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.

The syntax is as follows:

CREATE TABLE t2_copy AS SELECT * FROM t2;

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

Create a rowstore table

OceanBase Database allows you to create rowstore tables and convert rowstore tables into columnstore tables.

When default_table_store_format is set to row, which is the default value, a rowstore table is created by default. When default_table_store_format is not set to row, you can specify the WITH COLUMN GROUP(all columns) option to create a rowstore table.

For information about how to convert a rowstore table into a columnstore table, see Modify a table. For information about how to create a columnstore index, see Create an index.

You can specify the WITH COLUMN GROUP(all columns) option to create a rowstore table.

Here is an example:

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

Create a columnstore table

OceanBase Database allows you to create a columnstore table, convert a rowstore table into a columnstore table, and create a columnstore index. You can use the WITH COLUMN GROUP option to explicitly specify to create a columnstore table or a rowstore-columnstore redundant table. You can also set the default_table_store_format parameter to specify columnstore or rowstore-columnstore redundancy as the default storage format.

For information about how to convert a rowstore table into a columnstore table, see Modify a table. For information about how to create a columnstore index, see Create an index.

You can specify the WITH COLUMN GROUP(all columns, each column) option to create a rowstore-columnstore redundant table.

Here is an example:

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

You can specify the WITH COLUMN GROUP(each column) option to create a columnstore table.

Here is an example:

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

If you import a large amount of data to a columnstore table, you need to execute a major compaction to optimize the read performance and perform statistics collection to adjust the execution strategy.

• Major compaction: After a batch data import, we recommend that you perform a major compaction to improve the read performance. The major compaction will consolidate segmented data for continuous physical storage, thereby reducing the disk I/Os for reading data. After a data import, initiate a major compaction in the tenant to ensure that all data is compacted to the baseline layer. For more information, see MAJOR and MINOR.

• Statistics collection: After the major compaction, we recommend that you start statistics collection to help the optimizer generate an efficient query plan and execution strategy. You can execute the GATHER_SCHEMA_STATS procedure to collect statistics for all tables and query the GV$OB_OPT_STAT_GATHER_MONITOR view for the collection progress.

Note that when the data amount of a columnstore table increases, the major compaction takes more time.

References

• CREATE TABLE

• ALTER TABLE