You can execute the CREATE TABLE statement to create a table. CREATE TABLE is a DDL statement.
Use the CREATE TABLE statement to create a table
- The following example shows how to create a table in an Oracle tenant.
CREATE TABLE t_data_types(
id number NOT NULL PRIMARY KEY
,c_char char(100)
,c_varchar varchar2(100)
,c_nchar nchar(100)
,c_nvarchar nvarchar2(100)
,c_numeric numeric(10,2)
,c_int int
,c_float float(2)
,c_binary_float binary_float
,c_binary_double binary_double
,c_date date
,c_timestamp timestamp
,c_timestamp2 timestamp WITH time ZONE
,c_timestamp3 timestamp WITH LOCAL time zone
,c_interval_year INTERVAL YEAR TO MONTH
,c_interval_day INTERVAL DAY TO SECOND
,c_raw raw(2000)
,c_blob blob
,c_clob clob
);
Query OK, 0 rows affected (0.14 sec)
You can use the DESC command to view the column attributes.
+-----------------+-----------------------------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-----------------+-----------------------------------+------+-----+---------+-------+
| ID | NUMBER | NO | PRI | NULL | NULL |
| C_CHAR | CHAR(100) | YES | NULL | NULL | NULL |
| C_VARCHAR | VARCHAR2(100) | YES | NULL | NULL | NULL |
| C_NCHAR | NCHAR(100) | YES | NULL | NULL | NULL |
| C_NVARCHAR | NVARCHAR2(100) | YES | NULL | NULL | NULL |
| C_NUMERIC | NUMBER(10,2) | YES | NULL | NULL | NULL |
| C_INT | NUMBER(38) | YES | NULL | NULL | NULL |
| C_FLOAT | FLOAT(2) | YES | NULL | NULL | NULL |
| C_BINARY_FLOAT | BINARY_FLOAT | YES | NULL | NULL | NULL |
| C_BINARY_DOUBLE | BINARY_DOUBLE | YES | NULL | NULL | NULL |
| C_DATE | DATE | YES | NULL | NULL | NULL |
| C_TIMESTAMP | TIMESTAMP(6) | YES | NULL | NULL | NULL |
| C_TIMESTAMP2 | TIMESTAMP(6) WITH TIME ZONE | YES | NULL | NULL | NULL |
| C_TIMESTAMP3 | TIMESTAMP(6) WITH LOCAL TIME ZONE | YES | NULL | NULL | NULL |
| C_INTERVAL_YEAR | INTERVAL YEAR (2) TO MONTH | YES | NULL | NULL | NULL |
| C_INTERVAL_DAY | INTERVAL DAY (2) TO SECOND (6) | YES | NULL | NULL | NULL |
| C_RAW | RAW(2000) | YES | NULL | NULL | NULL |
| C_BLOB | BLOB | YES | NULL | NULL | NULL |
| C_CLOB | CLOB | YES | NULL | NULL | NULL |
+-----------------+-----------------------------------+------+-----+---------+-------+
19 rows in set (0.01 sec)
- The following example shows how to use the CREATE TABLE statement to create two tables: ware and cust.
obclient> create table ware(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 cust (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 ware(w_id)
, primary key (c_w_id, c_d_id, c_id)
);
Query OK, 0 rows affected (0.10 sec)
Note
We recommend that you specify the primary key or UNIQUE key when you create a table to improve the performance and facilitate subsequent maintenance. If no existing field can be used as the primary key, you can add a numeric column to the table to use it as the primary key. Then, you can use the Sequence object of the Oracle tenant to generate sequential values for this column. For more information about sequences, see Create and manage sequences.
You cannot add the primary key by executing the ALTER TABLE statement. Therefore, you must specify the primary key when you create a table.
Use the CREATE TABLE statement to copy data from a table
In an Oracle tenant, you cannot use the CREATE TABLE LIKE statement to copy the table structure. In this case, you need to know the DDL for the source table and change its name to create a table.
In an Oracle tenant, you can use the CREATE TABLE AS SELECT statement to copy the data types and data from a table. However, this statement does not copy the table attributes, such as indexes and constraints (for example, NOT NULL).
obclient> show create table t3\G
*************************** 1. row ***************************
TABLE: T3
CREATE TABLE: CREATE TABLE "T3" (
"ID" NUMBER NOT NULL,
"NAME" VARCHAR2(50),
"GMT_CREATE" DATE DEFAULT sysdate NOT NULL,
CONSTRAINT "T3_OBPK_1582718853302518" PRIMARY KEY ("ID"),
CONSTRAINT "T3_UK" UNIQUE ("NAME", "ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
partition by hash(id) partitions 8
1 row in set (0.01 sec)
obclient> create table t3_like like t3;
ERROR-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'like t3' at line 1
obclient> create table t3_copy as select * from t3;
Query OK, 0 rows affected (0.10 sec)
obclient> show create table t3_copy\G
*************************** 1. row ***************************
TABLE: T3_COPY
CREATE TABLE: CREATE TABLE "T3_COPY" (
"ID" NUMBER,
"NAME" VARCHAR2(50),
"GMT_CREATE" DATE
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
1 row in set (0.00 sec)
About tables and partitions
In OceanBase, data is stored in tables and partitions are the most fine-grained representation of data. A non-partitioned table has only one partition and a partitioned table usually has two or more partitions. By default, the partition name is prefixed with a lower-case p, followed by a number starting from 0. A partition is a subset of a table.
Generally, partitions of a table are transparent to your application. You can read and write the table in your application by executing SQL statements without specifying the partitions. In some cases, to improve the query performance of a partitioned table, you may execute SQL statements to directly access a specified partition. SQL syntax:
SELECT ... FROM parted_table PARTITION (pN) WHERE query_condition ;
- Example: Using an SQL statement to directly access a specified partition of a partitioned table
obclient> select o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d from ordr partition (p5) where o_w_id=1 and o_d_id=2 and o_id=2100;
+------+--------+--------------+----------+-------------+---------------------+
| O_ID | O_C_ID | O_CARRIER_ID | O_OL_CNT | O_ALL_LOCAL | O_ENTRY_D |
+------+--------+--------------+----------+-------------+---------------------+
| 2100 | 8 | 8 | 11 | 1 | 2020-02-15 18:57:10 |
+------+--------+--------------+----------+-------------+---------------------+
1 row in set (0.00 sec)
obclient> select ol_o_id, ol_number,ol_delivery_d,ol_amount,ol_i_id,ol_supply_w_id,ol_quantity from ordl partition (p5) where ol_w_id=1 and ol_d_id=2 and ol_o_id=2100;
+---------+-----------+---------------------+-----------+---------+----------------+-------------+
| OL_O_ID | OL_NUMBER | OL_DELIVERY_D | OL_AMOUNT | OL_I_ID | OL_SUPPLY_W_ID | OL_QUANTITY |
+---------+-----------+---------------------+-----------+---------+----------------+-------------+
| 2100 | 1 | 2020-02-15 18:57:10 | 0 | 87133 | 1 | 5 |
| 2100 | 2 | 2020-02-15 18:57:10 | 0 | 47413 | 1 | 5 |
| 2100 | 3 | 2020-02-15 18:57:10 | 0 | 9115 | 1 | 5 |
| 2100 | 4 | 2020-02-15 18:57:10 | 0 | 42985 | 1 | 5 |
| 2100 | 5 | 2020-02-15 18:57:10 | 0 | 43621 | 1 | 5 |
| 2100 | 6 | 2020-02-15 18:57:10 | 0 | 5787 | 1 | 5 |
| 2100 | 7 | 2020-02-15 18:57:10 | 0 | 62576 | 1 | 5 |
| 2100 | 8 | 2020-02-15 18:57:10 | 0 | 91592 | 1 | 5 |
| 2100 | 9 | 2020-02-15 18:57:10 | 0 | 34452 | 1 | 5 |
| 2100 | 10 | 2020-02-15 18:57:10 | 0 | 13792 | 1 | 5 |
| 2100 | 11 | 2020-02-15 18:57:10 | 0 | 94326 | 1 | 5 |
+---------+-----------+---------------------+-----------+---------+----------------+-------------+
11 rows in set (0.01 sec)
Note
For a composite partition, you can access more fine-grained partitions. For more information, see Partition routing.
In OceanBase Database, partitions are the most fine-grained data migration unit between nodes. Each partition has three replicas in the cluster. These replicas are synchronized to ensure data consistency and are divided into two roles: leader and follower. The three replicas consist of one leader and two followers. By default, only the leader provides write and read services. When you commit a transaction on the leader, the transaction logs are synchronized to the two followers. The three replicas use the Paxos protocol to vote on whether the transaction is committed. Sometimes, to avoid overloading the leader, the followers can share some read requests. This is the commonly used read/write splitting solution.
The process of reading follower replicas is known as weak consistency read . This solution has the risk of stale read caused by the latency between the follower that you read and the leader. You can set the max_stale_time_for_weak_consistency parameter to control the maximum allowed latency.
Example: Using SQL hints for read/write splitting
Syntax for SQL hints with weak consistency read: /*+ read_consistency(weak) */. Generally, the default read is a strong consistency read and no hint is needed.
obclient> select /*+ read_consistency(weak) */ o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d from ordr partition (p5) where o_w_id=1 and o_d_id=2 and o_id=2100;
+------+--------+--------------+----------+-------------+---------------------+
| O_ID | O_C_ID | O_CARRIER_ID | O_OL_CNT | O_ALL_LOCAL | O_ENTRY_D |
+------+--------+--------------+----------+-------------+---------------------+
| 2100 | 8 | 8 | 11 | 1 | 2020-02-15 18:57:10 |
+------+--------+--------------+----------+-------------+---------------------+
1 row in set (0.00 sec)
Replication
Replication is an advanced optimization method in OceanBase.
Generally, OceanBase uses a three-replica cluster architecture. By default, each partition of a table has three replicas in OceanBase Database, including one leader and two followers. By default, the leader provides read and write services.
You can create a follower on each server in a tenant and maintain strong synchronization between the leader and all replicas by using the full synchronization policy. The enables your application to execute some SQL JOIN queries within the same node for better performance.
The syntax for replication is to add DUPLICATE_SCOPE to the CREATE TABLE statement.
- Example: Creating a replica
obclient> create table item (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@zone1, F@zone2,R{all_server}@zone3' primary_zone='zone1';
Query OK, 0 rows affected (0.10 sec)