作者介绍

洪烨，openGauss Contributor，多年银行业系统架构设计及DBA实战经验，《DB2数据库内部解析与性能调优》作者。

openGauss对Oracle表的兼容性验证

接着上一篇，本文继续基于openGauss 1.0.0版本对于Oracle中数据库表的兼容性进行验证。

数据库的逻辑对象总共包含表、索引、约束、视图、序列、别名、函数、存储过程等。

表是数据库最基本的逻辑对象，也是作为承载数据的逻辑对象。在Oracle数据库中，数据库表分为堆表、临时表、表压缩、索引组织表、簇表、分区表以及嵌套表等7种类型。

一、堆表(heap table)

堆表是Oracle和openGauss默认表类型，堆表在数据写入时无需考虑行存放的顺序(按照写入的时间先后顺序存放)，因此写入速度较高，但由于是无序存放，读取效率较低。在Oracle中，表的创建通常采用以下两种方式，openGauss都可以兼容：

• 在语句中定义表的字段结构：例如create table test(id int)，这种方式也是最常见的方式；在openGauss中，执行结果如下：

postgres=# CREATE TABLE T_HEAP_TABLE

(

ID NUMBER,

NAME VARCHAR2(32)

);

CREATE TABLE

• 通过select语句的查询结果创建表：例如create table test as select * from tmp_table，openGauss中执行结果如下：

postgres=# CREATE TABLE T_HEAP_TABLE

AS

SELECT * FROM TMP_TEST;

INSERT 0 0

二、临时表

临时表可以理解为是一种特殊类型的表，用来保存临时数据的一个数据库对象。它只能存储在临时表空间，而非用户的表空间，对临时表的DML操作通常不记录事务日志。

Oracle临时表分为事务级临时表和会话级临时表。事务级临时表的数据只保存在事务的生命周期中，会话级临时表能支持会话的完整生命周期。对于这两种级别的临时表，openGauss都可以支持，实测结果如下：

1、事务级临时表

此类型的临时表中的数据仅在事务过程中有效，当事务提交后，临时表中的数据将被自动清除，但是临时表的结构以及元数据还存储在用户的数据字典中。在事务结束后，最好显式删除临时表，否则数据库会残留临时表的表结构和元数据。

• 在语句中定义表的字段结构方式创建

postgres=# CREATE GLOBAL TEMPORARY TABLE T_TRANS_TMP

(

ID NUMBER,

NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

CREATE TABLE

• 通过select语句的查询结果创建表

postgres=# CREATE GLOBAL TEMPORARY TABLE T_TRANS_TMP ON COMMIT DELETE ROWS

AS

SELECT * FROM TMP_TEST;

INSERT 0 0

2、会话级临时表

会话级临时表中的数据可以跨事务而存在，不过当该会话结束时，临时表中的数据将随着会话的结束而被丢弃。与事务级临时表相同，在会话结束后，会话级临时表的结构以及元数据还存储在用户的数据字典中，需显式手动清除。

• 在语句中定义表的字段结构方式创建

postgres=# CREATE TEMPORARY TABLE T_CONN_TMP

(

ID NUMBER,

NAME VARCHAR2(32)

) ON COMMIT PRESERVE ROWS;

CREATE TABLE

• 通过select语句的查询结果创建表

postgres=# CREATE TEMPORARY TABLE T_CONN_TMP ON COMMIT PRESERVE ROWS

AS

SELECT * FROM TMP_TEST;

INSERT 0 0

三、表压缩

表压缩是对表数据进行压缩，达到节省空间的目的，压缩对于数据装载和DML操作有一定的CPU消耗。然而，这些消耗可以为I/O的减少而抵消。Oracle常用的压缩方式有两种：基础压缩以及OLTP压缩。

1、基础压缩

基础压缩只在direct path load的时候才会生效，对于普通的dml语句insert、update不会发生压缩，openGauss可以支持基础压缩。

postgres=# create table t_compress (id number) compress;

CREATE TABLE

2、OLTP压缩

OLTP压缩会对所有的DML生效，所以适用于OLTP系统。只有当新的block中的数据存放到达了阈值的时候才会引发块内的压缩操作，然后更多的数据加入到块中，再一次达到阈值，整个block会重新压缩，以达到最大程度的压缩级别。

这个过程会一直重复，直到Oracle数据库确定无法再从压缩上获得更高的效益。所以多数OLTP事务作用在压缩的块上面，会和未压缩的表上拥有相同的性能。只有部分操作会引发块内的压缩动作。OLTP压缩功能openGauss目前无法支持。

postgres=# create table t_oltp_compress (id number) compress for oltp;

ERROR:  syntax error at or near "for"

LINE 1: create table t_oltp_compress (id number) compress for oltp;

四、索引组织表

索引组织表是以索引的方式保存表的数据，数据根据主键的顺序进行排列的，这样就提高了访问的速度。缺点是由于索引块保存所有的字段的信息，就需要更多的叶子页面来保存数据，数据量较大的时候会造成访问效率降低。

此外，如果主键频繁修改，对应的行也就需要磁盘位置频繁修改，行需要在不同的块之间相互移动。通常在以下情况，会考虑使用索引组织表：

• 表的宽度(即一行的数据长度)有限；

• 表的主键不会或极少更改；

• 表主要用于查询，DML操作较少；

• 大部分的业务需求是根据主键查询行中其它列上的信息。

openGauss目前版本不支持索引组织表，对于应用程序来说，索引组织表的使用方式与堆表并无差异。

postgres=# CREATE TABLE T_ORG_INDEX

(   

ID NUMBER,

NAME VARCHAR2(32),

PRIMARY KEY(ID) 

)organization index; 

ERROR:  syntax error at or near "organization index"

LINE 6: )organization index;

五、簇表

簇表也称为Cluster，在没有数据表和索引的时候，Cluster段是可以单独存在的。依据一定的规则，如连接键(Join Key)，可以将多个数据表数据保存在同一个段中。并且依据一定场景实现快速检索连接。在openGauss中，无法兼容此功能。

postgres=# create cluster t_cluster (id number) size 600;

ERROR:  syntax error at or near "cluster"

LINE 1: create cluster t_cluster (id number) size 600;

在某些为了提高连接性能的情况下，可以考虑用列存与partial cluster key结合的方式替代，表定义中可以选取某一列或几列设置为partial cluster key。

在导入数据时，按设置的列进行局部排序(默认每70个CU即420万行排序一次)，生成的CU会聚集在一起，即CU的min,max会在一个较小的区间内。当查询时，where条件含有这些列时，可产生良好的过滤效果。

postgres=# CREATE TABLE WAREHOUSE

(

    W_WAREHOUSE_SK            INTEGER               NOT NULL,

    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,

    W_WAREHOUSE_NAME        VARCHAR(20)                   ,

    PARTIAL CLUSTER KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)

) WITH (ORIENTATION = COLUMN);

六、分区表

分区表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表。对于应用来说，逻辑上只有一个表，但在物理上这个表由多个物理分区组成。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。分区表通常分为范围分区、列表分区、哈希分区以及复合分区。

1、范围分区

范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等(联通每个月的账单记录就用的分区表存储)。在openGauss中，可以支持范围分区。

postgres=# CREATE TABLE t_range_partition

  ( prod_id       NUMBER(6)  

  , cust_id       NUMBER  

  , time_id       DATE  

  , channel_id    CHAR(1)  

  , promo_id      NUMBER(6)  

  , quantity_sold NUMBER(3)  

  , amount_sold   NUMBER(10,2)  

)  PARTITION BY RANGE (time_id)  

(PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) TABLESPACE pg_default, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE pg_default, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) TABLESPACE pg_default, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))  TABLESPACE pg_default );

CREATE TABLE  

2、列表分区

列表分区是根据所有可能的值，指定应该插入相应的分区，openGauss当前版本无法支持列表分区。

postgres=# CREATE TABLE t_list_partition_table  

(id number,   

name varchar2(20),  

sales number(10, 2),  

state varchar2(2))  

PARTITION BY LIST (state)  

(PARTITION q1_northwest VALUES ('OR', 'WA'),  

PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),  

PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),  

PARTITION q1_southeast VALUES ('FL', 'GA'),  

PARTITION q1_northcentral VALUES ('SD', 'WI'),  

PARTITION q1_southcentral VALUES ('OK', 'TX'));  

ERROR:  syntax error at or near "LIST"

LINE 6:    PARTITION BY LIST (state)  

3、散列分区

散列(HASH)分区通过在分区键值上执行一个散列函数来说决定数据的物理位置。散列分区把记录分布在比范围分区更多的分区上，这减少了I/O争用的可能性。openGauss当前版本无法支持散列(HASH)分区。

postgres=# CREATE TABLE t_hash_partition

(deptno NUMBER, deptname VARCHAR(32))  

PARTITION BY HASH(deptno)  

(PARTITION p1 TABLESPACE pg_default, PARTITION p2 TABLESPACE pg_default,  

PARTITION p3 TABLESPACE pg_default, PARTITION p4 TABLESPACE pg_default);   

ERROR:  syntax error at or near "HASH"

LINE 2:      PARTITION BY HASH(deptno) 

4、复合分区

对于分区表来说，数据倾斜的问题通常是最头疼的。为了解决这个问题，Oracle提供了复合分区的功能。复合分区是先使用范围分区，然后在每个分区内再使用散列分区/列表分区的一种分区方法。不过目前版本openGauss无法支持复合分区。

postgres=# CREATE TABLE t_sub_partition

( dept_no number, country varchar2(20), sale_date date)   

PARTITION BY RANGE(sale_date)   

SUBPARTITION BY LIST(country)  

( PARTITION q1_2012 VALUES LESS THAN('2012-Apr-01')   

( SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),   

SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),   

SUBPARTITION q1_americas VALUES ('US', 'CANADA') ),   

PARTITION q2_2012 VALUES LESS THAN('2012-Jul-01')   

( SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),   

SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),   

SUBPARTITION q2_americas VALUES ('US', 'CANADA') ),   

PARTITION q3_2012 VALUES LESS THAN('2012-Oct-01')   

( SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),   

SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),   

SUBPARTITION q3_americas VALUES ('US', 'CANADA') ),   

PARTITION q4_2012 VALUES LESS THAN('2013-Jan-01')   

( SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),   

SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),   

SUBPARTITION q4_americas VALUES ('US', 'CANADA') ) ); 

ERROR:  syntax error at or near "SUBPARTITION"

LINE 3: SUBPARTITION BY LIST(country)

七、嵌套表

嵌套表类似C语言中的结构体，可以把一个表结构定义为一个类型，在创建其他表的时候，可以将字段类型指向这个自定义类型。openGauss中可以通过create type进行嵌套表定义。

postgres=# CREATE TYPE t_type AS (f1 int, f2 text);

CREATE TYPE

postgres=# CREATE TABLE t_compfoo(a int, b t_type);

CREATE TABLE

总结

总体而言，openGauss兼容Oracle常用表类型，索引组织表需要用集群索引方式进行改造。对于少数非常用数据类型，需要进行少量代码改造，可采取下列替代方案进行替换。

表类型	是否兼容	备注
堆表	全部兼容
临时表	全部兼容
表压缩	部分兼容	支持普通压缩模式，有DELTA、PREFIX、DICTIONARY、NUMSTR四种压缩算法供选择
索引组织表	不兼容	不支持
簇表	不兼容	不支持，部分情况可以考虑用partial cluster key替代
分区表	部分兼容	只支持范围分区
嵌套表	功能兼容，但语法有部分改变

相关阅读：

• openGauss可替代Oracle吗？从字段类型说起……

---

openGauss开源社区官方网站：

https://opengauss.org

openGauss组织仓库：

https://gitee.com/opengauss

openGauss镜像仓库：

https://github.com/opengauss-mirror

一键三连“分享、在看与点赞”，给我充点儿电吧~