可以通过执行 DDL 语句 CREATE TABLE 来创建一个表。

使用 CREATE TABLE 语句建表

  • 下面示例对 Oracle 模式内建类型的支持。
  1. CREATE TABLE t_data_types(
  2. id number NOT NULL PRIMARY KEY
  3. ,c_char char(100)
  4. ,c_varchar varchar2(100)
  5. ,c_nchar nchar(100)
  6. ,c_nvarchar nvarchar2(100)
  7. ,c_numeric numeric(10,2)
  8. ,c_int int
  9. ,c_float float(2)
  10. ,c_binary_float binary_float
  11. ,c_binary_double binary_double
  12. ,c_date date
  13. ,c_timestamp timestamp
  14. ,c_timestamp2 timestamp WITH time ZONE
  15. ,c_timestamp3 timestamp WITH LOCAL time zone
  16. ,c_interval_year INTERVAL YEAR TO MONTH
  17. ,c_interval_day INTERVAL DAY TO SECOND
  18. ,c_raw raw(2000)
  19. ,c_blob blob
  20. ,c_clob clob
  21. );
  22. Query OK, 0 rows affected (0.14 sec)

创建后可以通过DESC命令查看列属性。

  1. +-----------------+-----------------------------------+------+-----+---------+-------+
  2. | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
  3. +-----------------+-----------------------------------+------+-----+---------+-------+
  4. | ID | NUMBER | NO | PRI | NULL | NULL |
  5. | C_CHAR | CHAR(100) | YES | NULL | NULL | NULL |
  6. | C_VARCHAR | VARCHAR2(100) | YES | NULL | NULL | NULL |
  7. | C_NCHAR | NCHAR(100) | YES | NULL | NULL | NULL |
  8. | C_NVARCHAR | NVARCHAR2(100) | YES | NULL | NULL | NULL |
  9. | C_NUMERIC | NUMBER(10,2) | YES | NULL | NULL | NULL |
  10. | C_INT | NUMBER(38) | YES | NULL | NULL | NULL |
  11. | C_FLOAT | FLOAT(2) | YES | NULL | NULL | NULL |
  12. | C_BINARY_FLOAT | BINARY_FLOAT | YES | NULL | NULL | NULL |
  13. | C_BINARY_DOUBLE | BINARY_DOUBLE | YES | NULL | NULL | NULL |
  14. | C_DATE | DATE | YES | NULL | NULL | NULL |
  15. | C_TIMESTAMP | TIMESTAMP(6) | YES | NULL | NULL | NULL |
  16. | C_TIMESTAMP2 | TIMESTAMP(6) WITH TIME ZONE | YES | NULL | NULL | NULL |
  17. | C_TIMESTAMP3 | TIMESTAMP(6) WITH LOCAL TIME ZONE | YES | NULL | NULL | NULL |
  18. | C_INTERVAL_YEAR | INTERVAL YEAR (2) TO MONTH | YES | NULL | NULL | NULL |
  19. | C_INTERVAL_DAY | INTERVAL DAY (2) TO SECOND (6) | YES | NULL | NULL | NULL |
  20. | C_RAW | RAW(2000) | YES | NULL | NULL | NULL |
  21. | C_BLOB | BLOB | YES | NULL | NULL | NULL |
  22. | C_CLOB | CLOB | YES | NULL | NULL | NULL |
  23. +-----------------+-----------------------------------+------+-----+---------+-------+
  24. 19 rows in set (0.01 sec)
  • 下面示例使用 CREATE TABLE 语句创建订单表 ware 和 cust 表。
  1. obclient> create table ware(w_id int
  2. , w_ytd decimal(12,2)
  3. , w_tax decimal(4,4)
  4. , w_name varchar(10)
  5. , w_street_1 varchar(20)
  6. , w_street_2 varchar(20)
  7. , w_city varchar(20)
  8. , w_state char(2)
  9. , w_zip char(9)
  10. , unique(w_name, w_city)
  11. , primary key(w_id)
  12. );
  13. Query OK, 0 rows affected (0.09 sec)
  14. obclient> create table cust (c_w_id int NOT NULL
  15. , c_d_id int NOT null
  16. , c_id int NOT null
  17. , c_discount decimal(4, 4)
  18. , c_credit char(2)
  19. , c_last varchar(16)
  20. , c_first varchar(16)
  21. , c_middle char(2)
  22. , c_balance decimal(12, 2)
  23. , c_ytd_payment decimal(12, 2)
  24. , c_payment_cnt int
  25. , c_credit_lim decimal(12, 2)
  26. , c_street_1 varchar(20)
  27. , c_street_2 varchar(20)
  28. , c_city varchar(20)
  29. , c_state char(2)
  30. , c_zip char(9)
  31. , c_phone char(16)
  32. , c_since date
  33. , c_delivery_cnt int
  34. , c_data varchar(500)
  35. , index icust(c_last, c_d_id, c_w_id, c_first, c_id)
  36. , FOREIGN KEY (c_w_id) REFERENCES ware(w_id)
  37. , primary key (c_w_id, c_d_id, c_id)
  38. );
  39. Query OK, 0 rows affected (0.10 sec)

说明

建议建表的时候为表设计主键或者唯一键,这样对性能或者后期维护都很有益。如果没有合适的字段作为主键,可以为表增加一个数值列作为主键,使用 OceanBase Oracle 租户的序列为这一列填充值。有关序列介绍请参见 创建和管理序列

由于 ALTER TABLE 语法不支持后期增加主键,所以在建表的时候设置主键就更加有必要了。

使用 CREATE TABLE 复制表数据

Oracle 租户不支持 CREATE TABLE LIKE 复制表结构的方法,所以完全的复制表结构必须拿到源表的 DDL,更改名字重新创建新表。

Oracle 租户支持 CREATE TABLE AS SELECT 复制表的基本数据类型和数据,但不包含约束、索引和非空等属性。

  1. obclient> show create table t3\G
  2. *************************** 1. row ***************************
  3. TABLE: T3
  4. CREATE TABLE: CREATE TABLE "T3" (
  5. "ID" NUMBER NOT NULL,
  6. "NAME" VARCHAR2(50),
  7. "GMT_CREATE" DATE DEFAULT sysdate NOT NULL,
  8. CONSTRAINT "T3_OBPK_1582718853302518" PRIMARY KEY ("ID"),
  9. CONSTRAINT "T3_UK" UNIQUE ("NAME", "ID")
  10. ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
  11. partition by hash(id) partitions 8
  12. 1 row in set (0.01 sec)
  13. obclient> create table t3_like like t3;
  14. 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
  15. obclient> create table t3_copy as select * from t3;
  16. Query OK, 0 rows affected (0.10 sec)
  17. obclient> show create table t3_copy\G
  18. *************************** 1. row ***************************
  19. TABLE: T3_COPY
  20. CREATE TABLE: CREATE TABLE "T3_COPY" (
  21. "ID" NUMBER,
  22. "NAME" VARCHAR2(50),
  23. "GMT_CREATE" DATE
  24. ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
  25. 1 row in set (0.00 sec)

关于表和分区

在 OceanBase 数据库中,数据存储在表中,而数据表示的最小粒度是分区。普通的非分区表,就只有一个分区;而分区表,通常有多个分区,分区名默认以 p 开头,按数字顺序从 0 开始编号。所以分区是表的子集。

通常分区对用户的应用是透明的,应用只需要使用 SQL 读写表即可。只有某些场景下,为了提升分区表的查询性能,应用也可以使用 SQL 直接访问某个具体的分区,SQL 语法格式是:

  1. SELECT ... FROM parted_table PARTITION (pN) WHERE query_condition ;
  • 示例:通过 SQL 直接访问分区表的分区
  1. 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;
  2. +------+--------+--------------+----------+-------------+---------------------+
  3. | O_ID | O_C_ID | O_CARRIER_ID | O_OL_CNT | O_ALL_LOCAL | O_ENTRY_D |
  4. +------+--------+--------------+----------+-------------+---------------------+
  5. | 2100 | 8 | 8 | 11 | 1 | 2020-02-15 18:57:10 |
  6. +------+--------+--------------+----------+-------------+---------------------+
  7. 1 row in set (0.00 sec)
  8. 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;
  9. +---------+-----------+---------------------+-----------+---------+----------------+-------------+
  10. | OL_O_ID | OL_NUMBER | OL_DELIVERY_D | OL_AMOUNT | OL_I_ID | OL_SUPPLY_W_ID | OL_QUANTITY |
  11. +---------+-----------+---------------------+-----------+---------+----------------+-------------+
  12. | 2100 | 1 | 2020-02-15 18:57:10 | 0 | 87133 | 1 | 5 |
  13. | 2100 | 2 | 2020-02-15 18:57:10 | 0 | 47413 | 1 | 5 |
  14. | 2100 | 3 | 2020-02-15 18:57:10 | 0 | 9115 | 1 | 5 |
  15. | 2100 | 4 | 2020-02-15 18:57:10 | 0 | 42985 | 1 | 5 |
  16. | 2100 | 5 | 2020-02-15 18:57:10 | 0 | 43621 | 1 | 5 |
  17. | 2100 | 6 | 2020-02-15 18:57:10 | 0 | 5787 | 1 | 5 |
  18. | 2100 | 7 | 2020-02-15 18:57:10 | 0 | 62576 | 1 | 5 |
  19. | 2100 | 8 | 2020-02-15 18:57:10 | 0 | 91592 | 1 | 5 |
  20. | 2100 | 9 | 2020-02-15 18:57:10 | 0 | 34452 | 1 | 5 |
  21. | 2100 | 10 | 2020-02-15 18:57:10 | 0 | 13792 | 1 | 5 |
  22. | 2100 | 11 | 2020-02-15 18:57:10 | 0 | 94326 | 1 | 5 |
  23. +---------+-----------+---------------------+-----------+---------+----------------+-------------+
  24. 11 rows in set (0.01 sec)

说明

如果是组合分区,可以访问更细粒度的分区,详细描述请参考 分区路由

在 OceanBase 数据库里,节点间的数据迁移的最小粒度是分区,每个分区在集群里有三个副本,内容保持同步,角色上有区分。三副本会有一个主副本(Leader 副本)和两个备副本(Follower 副本),只有主副本可以提供写服务,默认也只有主副本可以提供读服务。主副本上的事务提交时会将事务日志同步到两个备副本,三副本使用 Paxos 协议表决事务是否提交成功。有时候为了不影响主副本,可以让备副本承担部分读请求,这就是应用常用的读写分离的解决方案,

这种读备称为弱一致性读。使用这种方案,应用读需要承担读延时的风险,这个延时最大允许值会通过参数(max_stale_time_for_weak_consistency)控制。

  • 示例:使用 SQL Hint 实现读写分离。

    弱一致读的 Hint 语法是 /*+ read_consistency(weak) */ 。通常的读默认是强一致性读,就不用 Hint 了。

  1. 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;
  2. +------+--------+--------------+----------+-------------+---------------------+
  3. | O_ID | O_C_ID | O_CARRIER_ID | O_OL_CNT | O_ALL_LOCAL | O_ENTRY_D |
  4. +------+--------+--------------+----------+-------------+---------------------+
  5. | 2100 | 8 | 8 | 11 | 1 | 2020-02-15 18:57:10 |
  6. +------+--------+--------------+----------+-------------+---------------------+
  7. 1 row in set (0.00 sec)

复制表

复制表是分布式数据库 OceanBase 的高级优化手段。

通常 OceanBase 集群是三副本架构,默认每个表的每个分区在 OceanBase 集群中会有三个副本数据,角色上分为一个主副本(Leader 副本)和两个备副本(Follower副本),默认提供读写服务的是主副本。

复制表可以指定在租户的每台机器上都有一个备副本,并且主副本跟所有备份的数据使用全同步策略保持强同步。这样做的目的是为了让业务有些 SQL 关联查询时能在同一节点内部执行,以获取更好的性能。

复制表的语法是在 CREATE TABLE 语句后增加 DUPLICATE_SCOPE 选项。

  • 示例:创建复制表。
  1. obclient> create table item (i_id int
  2. , i_name varchar(24)
  3. , i_price decimal(5,2)
  4. , i_data varchar(50)
  5. , i_im_id int
  6. , primary key(i_id)) COMPRESS FOR QUERY pctfree=0 BLOCK_SIZE=16384
  7. duplicate_scope='cluster' locality='F@zone1, F@zone2,R{all_server}@zone3' primary_zone='zone1';
  8. Query OK, 0 rows affected (0.10 sec)