github-fill

描述

该语句用来在数据库中创建一张新表。

语法

CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [table_column_group_option] [AS] select;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
     | [,
        | [CONSTRAINT [constraint_name]] { PRIMARY KEY|UNIQUE } (column_name) //在创建所有列后添加约束
        | [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constraint_state
        | [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state
       ]

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [VISIBLE|INVISIBLE] [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    {
    [DEFAULT expression]
    [NULL | NOT NULL]
    [CONSTRAINT [constraint_name]] [ PRIMARY KEY|UNIQUE ]       //在创建列时添加约束
    [CONSTRAINT [constraint_name] CHECK(expression) constraint_state]
    [CONSTRAINT [constraint_name] references_clause]
    |
    [GENERATED ALWAYS] AS (expression) [VIRTUAL]
    [NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [SKIP_INDEX(skip_index_option_list)]
    }

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {SET NULL | CASCADE}]

constraint_state:
    [RELY | NORELY] [USING INDEX index_option_list] [ENABLE | DISABLE] [VALIDATE | NOVALIDATE]

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | compression
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'

physical_attribute_list:
    physical_attribute [physical_attribute]

physical_attribute:
    PCTFREE [=] num
    | PCTUSED num
    | INITRANS num
    | MAXTRANS num
    | STORAGE(storage_option [storage_option] ...)
    | TABLESPACE tablespace

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

compression:
    NOCOMPRESS
    | COMPRESS { BASIC | FOR OLTP | FOR QUERY [LOW | HIGH]  | FOR ARCHIVE [LOW | HIGH]}

storage_option:
    INITIAL num [K|M|G|T|P|E]
    | NEXT num [K|M|G|T|P|E]
    | MINEXTENTS num [K|M|G|T|P|E]
    | MAXEXTENTS num [K|M|G|T|P|E]

partition_option:
      PARTITION BY HASH(column_name_list)
      [subpartition_option] hash_partition_define
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)

/*模板化二级分区*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
      (list_subpartition_list)

/*非模板化二级分区*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list)
    | SUBPARTITION BY RANGE (column_name_list)
    | SUBPARTITION BY LIST (column_name_list)

subpartition_list:
      (hash_subpartition_list)
    | (range_subpartition_list)
    | (list_subpartition_list)

hash_partition_define:
      PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | (hash_partition_list)

hash_partition_list:
    hash_partition [, hash_partition ...]

hash_partition:
    partition [partition_name] [subpartition_list/*仅非模板化二级分区可定义*/]

hash_subpartition_define:
      SUBPARTITIONS subpartition_count
    | SUBPARTITION TEMPLATE (hash_subpartition_list)

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

hash_subpartition:
    subpartition [subpartition_name]

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | (MAXVALUE)}
    [subpartition_list/*仅非模板化二级分区可定义*/]
    [ID = num] [physical_attribute_list] [compression]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]

list_partition_list:
    list_partition [, list_partition] ...

list_partition:
    PARTITION [partition_name]
    VALUES (DEFAULT | expression_list)
    [subpartition_list /*仅非模板化二级分区可定义*/]
    [ID num] [physical_attribute_list] [compression]

list_subpartition_list:
    list_subpartition [, list_subpartition] ...

list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT | expression_list) [physical_attribute_list]

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

on_commit_option:
    ON COMMIT DELETE ROWS
  | ON COMMIT PRESERVE ROWS

table_column_group_option：
    WITH COLUMN GROUP(all columns)
  | WITH COLUMN GROUP(each column)
  | WITH COLUMN GROUP(all columns, each column)

参数说明

参数	描述
GLOBAL TEMPORARY	将该表创建为临时表。
DEFAULT expression	指定列的默认值。`expression` 支持包含 Sequence 的函数表达式。注意自增列不能设置默认值。
BLOCK_SIZE	指定表的微块大小。
COMPRESSION	指定存储格式 Flat/Encoding 以及压缩方法，对应如下： `nocompress`：使用 Flat 格式，不进行压缩。 `compress [basic]`：使用 Flat 格式和 lz4_1.0 压缩方法。 `compress for oltp`：使用 Flat 格式和 zstd_1.3.8 压缩方法。 `query low` 或者 `query high`：使用 Encoding 格式和 lz4_1.0 压缩方法。 `archive low` 或者 `archive high`：使用 Encoding 格式和 zstd_1.3.8 压缩方法。
tablegroup_name	指定表所属的 Table Group。
FOREIGN KEY	为创建的表指定外键。如果不指定外键名，则会使用表名 + `OBFK` + 创建时间命名。（例如，在 2021 年 8 月 1 日 00:00:00 为 `t1` 表创建的外键名称为 `t1_OBFK_1627747200000000`）。外键允许跨表交叉引用相关数据，当 `DELETE` 操作影响与子表相匹配行的父表中键值时，其结果取决于 `ON DELETE` 子句的引用操作： `CASCADE`：表示从父表中删除或更新行，并自动删除或更新子表中匹配的行。 `SET NULL`：表示从父表中删除或更新行，并将子表中的外键列设置为 `NULL`。
VISIBLE	表示列可见，是默认的列状态。
INVISIBLE	表示列不可见，当将一个列设置为 `INVISIBLE` 后，在查询时该列将不会被默认显示。
GENERATED BY DEFAULT AS IDENTITY \| GENERATED ALWAYS AS IDENTITY	可选项，指定一个列为自增列。具体如下： `GENERATED BY DEFAULT AS IDENTITY`：表示在插入数据时，如果没有显式指定自增列的值，系统将使用默认的自增值。 `GENERATED ALWAYS AS IDENTITY`：表示在插入数据时，必须使用自动生成的自增值，而不能显式指定自增列的值。注意该列的数据类型必须是数值类型。
physical_attribute	`PCTFREE`: 指定宏块保留空间百分比。其它属性 `STORAGE`、`TABLESPACE` 等仅为了语法兼容方便迁移，不生效。
ENABLE/DISABLE ROW MOVEMENT	是否允许在不同分区间移动以进行分区键更新。
ON COMMIT DELETE ROWS	事务级临时表，提交时删除数据。
ON COMMIT PRESERVE ROWS	会话级临时表，会话结束时删除数据。
parallel_clause	指定表级别的并行度： `NOPARALLEL`：并行度为 1，为默认配置。 `PARALLEL integer`：指定并行度，`integer` 取值大于等于 `1`。注意当指定并行度时，优先级关系如下：通过 Hint 指定的并行度 > 通过 `ALTER SESSION` 指定的并行度 > 表级别的并行度。
DUPLICATE_SCOPE	指定复制表的属性，取值如下： `none`：表示该表是一个普通表，为默认值。 `cluster`：表示该表是一个复制表，Leader 需要将事务复制到当前租户的所有 F（全能）副本及 R（只读）副本。 OceanBase 数据库目前仅支持 `cluster` 级别的复制表。
table_column_group_option	指定表列存选项。具体解释如下： `WITH COLUMN GROUP(all columns, each column)`：指定创建行存列存冗余的表。 `WITH COLUMN GROUP(all columns)`：指定添加行存表。 `WITH COLUMN GROUP(each column)`：指定创建列存表。
SKIP_INDEX	标识列的 Skip Index 属性。取值如下： `MIN_MAX`：是 Skip Index 中最通用的聚合数据类型，它会在索引节点粒度下存储被索引列的最大值、最小值和 Null Count。这种类型的数据可以加速下推的 Filter 和 `MIN/MAX` 聚合的下推。 `SUM`：用于加速数值类型的 `SUM` 聚合的下推。注意 JSON 和空间数据类型的列禁止创建 Skip Index。生成列禁止创建 Skip Index。

示例

创建表 tbl1。

obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50)) ;
Query OK, 0 rows affected

创建 Hash 分区，分区数为 8 的表。

obclient> CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT) PARTITION BY HASH(col1) PARTITIONS 8;
Query OK, 0 rows affected

创建一级分区为 Range 分区，二级分区为 Hash 分区的表。

obclient> CREATE TABLE tbl5 (col1 INT, col2 INT, col3 INT)
                 PARTITION BY RANGE(col1)
                 SUBPARTITION BY HASH(col2)  SUBPARTITIONS  5
                 (PARTITION p0 VALUES LESS THAN(0),
                  PARTITION p1 VALUES LESS THAN(100));
Query OK, 0 rows affected

开启 Encoding 并使用 zstd 压缩，宏块保留空间为 5%。

obclient> CREATE TABLE tbl6 (col1 INT, col2 INT, col3 VARCHAR(64))
                 COMPRESS FOR ARCHIVE
                 PCTFREE 5;
Query OK, 0 rows affected

创建一个事务级临时表。

obclient> CREATE GLOBAL TEMPORARY TABLE tbl7(col1 INT) ON COMMIT DELETE ROWS;
Query OK, 0 rows affected

创建一个带约束的表。

obclient> CREATE TABLE tbl8 (col1 INT, col2 INT, col3 INT,CONSTRAINT equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
Query OK, 0 rows affected

为表 ref_t2 指定外键，当 DELETE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY,C2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY，C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON DELETE SET NULL);
Query OK, 0 rows affected

创建非模板化的 Range + Range 二级分区表。

obclient> CREATE TABLE tbl9 (col1 INT, col2 INT, col3 INT)
                 PARTITION BY RANGE(col1)
                 SUBPARTITION BY RANGE(col2)
(
  PARTITION p0 VALUES LESS THAN(100)
  (
    SUBPARTITION p0_r1 VALUES LESS THAN(2019),
    SUBPARTITION p0_r2 VALUES LESS THAN(2020),
    SUBPARTITION p0_r3 VALUES LESS THAN(2021)
  ),
  PARTITION p1 VALUES LESS THAN(200)
  (
    SUBPARTITION p1_r1 VALUES LESS THAN(2019),
    SUBPARTITION p1_r2 VALUES LESS THAN(2020),
    SUBPARTITION p1_r3 VALUES LESS THAN(2021)
  ),
  PARTITION p2 VALUES LESS THAN(300)
  (
    SUBPARTITION p2_r1 VALUES LESS THAN(2019),
    SUBPARTITION p2_r2 VALUES LESS THAN(2020),
    SUBPARTITION p2_r3 VALUES LESS THAN(2021)
  )
);
Query OK, 0 rows affected

创建表 tbl10 的并行度为 3。

obclient> CREATE TABLE tbl10(col1 INT PRIMARY KEY, col2 INT) PARALLEL 3;
Query OK, 0 rows affected

使用函数定义列的默认值。

obclient> CREATE SEQUENCE SEQ_PERSONIPTVSEQ START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
Query OK, 0 rows affected

obclient> SELECT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
+----------------------------------------------------------------------------+
| LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')) |
+----------------------------------------------------------------------------+
| 2022-07-05 11:34:1                                                         |
+----------------------------------------------------------------------------+
1 row in set

obclient> CREATE TABLE FUNC_DEFAULT_TEST ( OID NUMBER(20,0) DEFAULT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) NOT NULL);
Query OK, 0 rows affected

创建 cluster 级别的复制表 dup_t1。像普通表一样插入和读写复制表的数据。对于一个读请求，如果使用了 Proxy，读请求可能会路由到任意一个 OBServer 节点；如果直连 OBServer 节点，只要本地副本可读，就会在直连的 OBServer 节点上执行读请求。
```
obclient> CREATE TABLE dup_t1(c1 int) DUPLICATE_SCOPE = 'cluster';
Query OK, 0 rows affected

obclient> INSERT INTO dup_t1 VALUES(1);
Query OK, 1 row affected

obclient> SELECT * FROM dup_t1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set
```

创建列存表 tbl1_cg。

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

创建表时标识列 Skip Index 属性。

CREATE TABLE test_skidx(
  col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
  col4 CHAR(10)
);

创建一个名为 test_tbl1 的表，列 col1 设置为自增列，并且指定为主键。

CREATE TABLE test_tbl1 (
  col1 INT GENERATED BY DEFAULT AS IDENTITY,
  col2 VARCHAR2(50),
  PRIMARY KEY (col1)
);

Oracle 兼容模式 Global 临时表的使用限制

Oracle 兼容模式下的临时表在多个业务场景下都有实际应用，具备基本的正确性/功能性保证。
一般使用临时表的目的大多是做兼容，减少业务改造。当业务在有限场景，对临时表性能要求不高时可以使用。如果业务场景能够改成普通表，则改普通表更好。

性能和稳定性

临时表的 SQL 执行效率与普通表基本一样，无特别优势。
临时表在事务结束/Session 断开时需要做额外的工作，即清理数据，会产生额外开销。
临时表在登录时进行的检查和清理动作可能会对登录线程造成压力，导致登录时间变长，严重时导致无法登录。

创建临时表

当创建临时表时，默认会对建表语句进行改写：

新增 SYS_SESSION_ID 列作为主键。
新增 SYS_SESS_CREATE_TIME 作为普通列。
创建以 SYS_SESSION_ID 作为分区键的 Hash 分区表，分区个数为固定的 16 分区。

例如：

CREATE GLOBAL TEMPORARY TABLE t1(
  c1 INT,
  c2 INT,
  PRIMARY KEY(c1)
);

会被改写为如下形式。

CREATE GLOBAL TEMPORARY TABLE t1(
  SYS_SESSION_ID INT,
  SYS_SESS_CREATE_TIME INT,
  c1 INT,
  c2 INT,
  PRIMARY KEY(SYS_SESSION_ID, c1)
)
PARTITION BY HASH(SYS_SESSION_ID) PARTITIONS 16;

临时表 DML/查询语句

执行 INSERT 时，会默认将当前会话的 Session ID 和 Session 创建时间插入 SYS_SESSION_ID 列和 SYS_SESS_CREATE_TIME。

执行 UPDATE/DELETE/SELECT 时，通过 SQL 改写，默认会为包含临时表的语句添加过滤条件 “SYS_SESSION_ID = 当前会话 session_id”。通过这个条件，SQL 优化器可以进行分区裁剪并抽取查询范围。

临时表数据清理

对 ON COMMIT DELETE ROWS 选项的临时表（事务临时表，也是默认选项），在事务结束时，开启一个新事务，通过执行 DELETE 语句删除临时表数据。
对 ON COMMIT PRESERVE ROWS 选项的临时表（会话临时表），在会话断开时，通过执行 DELETE 语句删除临时表数据。
由于 Session ID 可能存在复用的情况，在 OceanBase 数据库 V3.2.4 BP4 和之前版本会在登录时对当前 Session ID 的数据进行检查，如果存在需要额外进行一次清理。
由于 Session ID 不唯一进行的登录检查和清理，可能会导致故障（集群无法登录）。

临时表的路由

事务临时表（ON COMMIT DELETE ROWS）事务内对临时表的访问只能路由给事务开启节点。
会话临时表（ON COMMIT PRESERVE ROWS）当 Session 访问过临时表后，OBServer 节点会通知 Proxy，让 Proxy 的后续请求只能发给当前 Session。

删除临时表

与普通表相同可以在 DML 执行的同时成功执行 DROP，临时表全部数据会被删除。这与 Oracle 的行为不同，Oracle 是等待所有 Session 不持有临时表资源之后，才能 DROP。

交叉功能的支持情况

功能	V3.2.4 BP4 和之前版本是否支持	V3.2.4 BP5 和 V3.2.x 后续版本是否支持	V4.2.0 版本是否支持
不同 Session 共享计划	No	No	Yes
登录无需触发检查和清理	No	Yes	Yes
`MERGE INTO` 语句	No	No	Yes

严重问题的规避手段

无法登录

停止临时表相关业务，将临时表删除或做一次合并，一般可以自行恢复，如果仍然无法恢复，则进行第 2 步。
重启无法登录的机器。

无法重用计划导致 PL Cache 膨胀

例如 PL 定义中包含临时表的语句：

CREATE PROCEDUCE p1 () (
  INSERT INTO temp_table VALUES (xxx);
  SELECT * FROM temp_table WHERE ROWNUM = 1 INTO var1;
);

由于不同 Session 对临时表访问无法共享计划，每个 Session 都需要编译一次这个 Procedure p1 生成对应的 Cache，可能导致稳定性问题。将其中的 SQL 改成：

EXCUTE IMMEDIATE 'SELECT * FROM temp_table WHERE ROWNUM = 1 INTO var1;'

将临时表 SQL 改为动态 SQL 可以绕过这个问题。

故障情况数据未清理

故障情况可能导致数据残留，目前无自动清理办法，一般不影响使用。如果残留数据过多，可以通过 DROP 临时表，再重建解决。

CREATE TABLE

描述

语法