db2查看表结构_技术分享|openGaussOracle兼容性评估（二）数据库表

作者介绍

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

上一篇看到很多朋友留言对openGauss的历史疑问较多&＃xff0c;本文开头就先把笔者道听途说来的openGauss历史作为杂谈在这里聊聊。

华为数据库最早诞生于运营商的需求&＃xff0c;最初版本名称为GMDB&＃xff0c;后来基于PostgreSQL-XC进行整体改造&＃xff0c;再配合自研的存储引擎&＃xff0c;发布了FusionInsight  LibrA(天枰座)&＃xff0c;也就是大家听过较多的MPPDB&＃xff0c;目前官网上还能找到一些FusionInsight LibrA的资料。

同时在2015年&＃xff0c;华为成立了另一项目组&＃xff0c;纯自研了一款与Oracle非常相似的数据库&＃xff0c;引擎名称为Zenith。在2015-2016年左右&＃xff0c;华为基于MySQL研发了一款云原生数据库TaurusDB(这个时间段貌似有三款并行的数据库产品)。

2018年左右&＃xff0c;华为开始进行数据库整合&＃xff0c;对数据库产品名定义为GaussDB。针对不同的场景&＃xff0c;分为GaussDB 100(简单OLTP场景&＃xff0c;单节点架构&＃xff0c;基于Zenith引擎)、GaussDB 200(OLAP及数仓场景&＃xff0c;MPPDB架构&＃xff0c;基于Libra引擎)、GaussDB 300(HTAP场景&＃xff0c;分布式架构&＃xff0c;貌似是基于PostgreSQL-XL改造)三个对外的产品&＃xff0c;在2019年又进行了再次整合&＃xff0c;将GaussDB 100、GaussDB 300合并&＃xff0c;产品名称变为GaussDB T(OLTP、HTAP场景)、GaussDB A(OLAP场景&＃xff0c;原Gauss 200)。

之后又基于华为云整体策略&＃xff0c;Zenith内核貌似是弃用了&＃xff0c;启用原Libra内核(内核名称改成了轩辕)&＃xff0c;GaussDB A变成了目前的华为云上DWS服务&＃xff0c;GaussDB T变成了GaussDB for openGauss服务&＃xff0c;同时也将openGauss开源。由于openGauss是基于GMDB发展而来(也就是基于PostgreSQL的产品路线)&＃xff0c;所以命令行和元数据库的信息看起来还是Postgres&＃xff0c;不过底层的存储引擎与PostgreSQL有不少改动

书接正传&＃xff0c;接着上一篇&＃xff0c;本文继续基于openGauss 1.0.0版本对于Oracle中数据库表的兼容性进行验证。

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

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

一、堆表(heap table)

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

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

postgres&＃61;# CREATE TABLE T_HEAP_TABLE

(

ID NUMBER,

NAME VARCHAR2(32)

);

CREATE TABLE

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

postgres&＃61;# CREATE TABLE T_HEAP_TABLE

AS

SELECT * FROM TMP_TEST;

INSERT 0 0

二、临时表

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

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

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

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

postgres&＃61;# CREATE GLOBAL TEMPORARY TABLE T_TRANS_TMP

(

ID NUMBER,

NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

CREATE TABLE

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

postgres&＃61;# CREATE GLOBAL TEMPORARY TABLE T_TRANS_TMP ON COMMIT DELETE ROWS

AS

SELECT * FROM TMP_TEST;

INSERT 0 0

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

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

postgres&＃61;# CREATE TEMPORARY TABLE T_CONN_TMP

(

ID NUMBER,

NAME VARCHAR2(32)

) ON COMMIT PRESERVE ROWS;

CREATE TABLE

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

postgres&＃61;# CREATE TEMPORARY TABLE T_CONN_TMP ON COMMIT PRESERVE ROWS

AS

SELECT * FROM TMP_TEST;

INSERT 0 0

三、表压缩

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

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

postgres&＃61;# create table t_compress (id number) compress;

CREATE TABLE

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

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

postgres&＃61;# 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;

四、索引组织表

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

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

• 表的宽度(即一行的数据长度)有限&＃xff1b;

• 表的主键不会或极少更改&＃xff1b;

• 表主要用于查询&＃xff0c;DML操作较少&＃xff1b;

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

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

postgres&＃61;# 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&＃xff0c;在没有数据表和索引的时候&＃xff0c;Cluster段是可以单独存在的。依据一定的规则&＃xff0c;如连接键(Join Key)&＃xff0c;可以将多个数据表数据保存在同一个段中。并且依据一定场景实现快速检索连接。在openGauss中&＃xff0c;无法兼容此功能。

postgres&＃61;# 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;

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

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

postgres&＃61;# 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 &＃61; COLUMN);

六、分区表

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

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

postgres&＃61;# 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(&＃39;01-APR-2006&＃39;,&＃39;dd-MON-yyyy&＃39;)) TABLESPACE pg_default, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE(&＃39;01-JUL-2006&＃39;,&＃39;dd-MON-yyyy&＃39;)) TABLESPACE pg_default, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE(&＃39;01-OCT-2006&＃39;,&＃39;dd-MON-yyyy&＃39;)) TABLESPACE pg_default, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE(&＃39;01-JAN-2007&＃39;,&＃39;dd-MON-yyyy&＃39;))  TABLESPACE pg_default );

CREATE TABLE  

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

postgres&＃61;# 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 (&＃39;OR&＃39;, &＃39;WA&＃39;),  

PARTITION q1_southwest VALUES (&＃39;AZ&＃39;, &＃39;UT&＃39;, &＃39;NM&＃39;),  

PARTITION q1_northeast VALUES  (&＃39;NY&＃39;, &＃39;VM&＃39;, &＃39;NJ&＃39;),  

PARTITION q1_southeast VALUES (&＃39;FL&＃39;, &＃39;GA&＃39;),  

PARTITION q1_northcentral VALUES (&＃39;SD&＃39;, &＃39;WI&＃39;),  

PARTITION q1_southcentral VALUES (&＃39;OK&＃39;, &＃39;TX&＃39;));  

ERROR:  syntax error at or near "LIST"

LINE 6:    PARTITION BY LIST (state)  

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

postgres&＃61;# 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) 

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

postgres&＃61;# 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(&＃39;2012-Apr-01&＃39;)   

( SUBPARTITION q1_europe VALUES (&＃39;FRANCE&＃39;, &＃39;ITALY&＃39;),   

SUBPARTITION q1_asia VALUES (&＃39;INDIA&＃39;, &＃39;PAKISTAN&＃39;),   

SUBPARTITION q1_americas VALUES (&＃39;US&＃39;, &＃39;CANADA&＃39;) ),   

PARTITION q2_2012 VALUES LESS THAN(&＃39;2012-Jul-01&＃39;)   

( SUBPARTITION q2_europe VALUES (&＃39;FRANCE&＃39;, &＃39;ITALY&＃39;),   

SUBPARTITION q2_asia VALUES (&＃39;INDIA&＃39;, &＃39;PAKISTAN&＃39;),   

SUBPARTITION q2_americas VALUES (&＃39;US&＃39;, &＃39;CANADA&＃39;) ),   

PARTITION q3_2012 VALUES LESS THAN(&＃39;2012-Oct-01&＃39;)   

( SUBPARTITION q3_europe VALUES (&＃39;FRANCE&＃39;, &＃39;ITALY&＃39;),   

SUBPARTITION q3_asia VALUES (&＃39;INDIA&＃39;, &＃39;PAKISTAN&＃39;),   

SUBPARTITION q3_americas VALUES (&＃39;US&＃39;, &＃39;CANADA&＃39;) ),   

PARTITION q4_2012 VALUES LESS THAN(&＃39;2013-Jan-01&＃39;)   

( SUBPARTITION q4_europe VALUES (&＃39;FRANCE&＃39;, &＃39;ITALY&＃39;),   

SUBPARTITION q4_asia VALUES (&＃39;INDIA&＃39;, &＃39;PAKISTAN&＃39;),   

SUBPARTITION q4_americas VALUES (&＃39;US&＃39;, &＃39;CANADA&＃39;) ) ); 

ERROR:  syntax error at or near "SUBPARTITION"

LINE 3: SUBPARTITION BY LIST(country)

七、嵌套表

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

postgres&＃61;# CREATE TYPE t_type AS (f1 int, f2 text);

CREATE TYPE

postgres&＃61;# CREATE TABLE t_compfoo(a int, b t_type);

CREATE TABLE

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

相关阅读&＃xff1a;

• openGauss可替代Oracle吗&＃xff1f;从字段类型说起……

2020 DAMS中国数据智能管理峰会即将于10月30日在上海举办&＃xff0c;部分精彩议题先睹为快&＃xff1a;

• 腾讯《腾讯游戏大数据资产管理实战&＃xff1a;元数据管理与数据治理》

• 京东《京东EB级全域大数据平台建设和治理之路》

• 阿里《大规模容器云基础设施环境架构、管理与运维》

• 工商银行《DevOps转型的探索与实践》

• 中国银联《从自研演进看分布式数据库》

• 民生银行《开源数据库MySQL在民生银行的应用实践》

• 平安银行《“传统&＃43;互联网”混合CMDB及运营中台实践》

• 中国联通《大数据资产管理平台的设计、研发、运营实践》

• AWS《基于数据湖构建云上的数据分析架构》

• 今日头条《字节跳动数据治理实践》

• 苏宁《苏宁大规模智能告警收敛与告警根因的实践》

• 滴滴《万亿级消息队列Kafka在滴滴的实践》