OpenGauss vs Oracle Tables: Compatibility Test and Practical Insights

Background and History

Huawei's database lineage began with GMDB, later rebuilt on PostgreSQL‑XC with a custom storage engine and released as FusionInsight LibrA (also known as MPPDB). In 2015 Huawei launched Zenith, an Oracle‑like database, and TaurusDB, a cloud‑native MySQL‑based product. Around 2018 the products were consolidated under the GaussDB brand: GaussDB 100 (OLTP, single‑node, Zenith engine), GaussDB 200 (OLAP, MPPDB architecture, Libra engine), and GaussDB 300 (HTAP, PostgreSQL‑XL based). In 2019 GaussDB 100 and GaussDB 300 merged into GaussDB T (OLTP/HTAP) and GaussDB A (OLAP). The open‑source project openGauss emerged from the GaussDB T line, retaining PostgreSQL‑compatible command‑line tools while introducing a new storage engine (originally named Libra, later renamed XuanYuan).

Compatibility Test Overview

Using openGauss 1.0.0, the article verifies compatibility with the logical objects defined in Oracle tables, including heap tables, temporary tables, compression, index‑organized tables, cluster tables, partitioned tables, and nested tables. For each type, the Oracle creation syntax is reproduced, the equivalent openGauss command is shown, and the execution result (success or error) is recorded.

Oracle Table Types and openGauss Support

Heap Table – The default table type in both Oracle and openGauss. Creation via explicit column definition or CREATE TABLE … AS SELECT … works identically in openGauss.

postgres=# CREATE TABLE T_HEAP_TABLE (
  ID NUMBER,
  NAME VARCHAR2(32)
);

postgres=# CREATE TABLE T_HEAP_TABLE AS SELECT * FROM TMP_TEST;
INSERT 0 0

Temporary Table – Supports both transaction‑level and session‑level temporary tables. openGauss accepts CREATE GLOBAL TEMPORARY TABLE … ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS syntaxes.

postgres=# CREATE GLOBAL TEMPORARY TABLE T_TRANS_TMP (
  ID NUMBER,
  NAME VARCHAR2(32)
) ON COMMIT DELETE ROWS;

postgres=# CREATE TEMPORARY TABLE T_CONN_TMP (
  ID NUMBER,
  NAME VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;

Table Compression – Basic compression (direct‑path load only) is supported. Attempting Oracle’s OLTP compression syntax results in a syntax error in openGauss.

postgres=# CREATE TABLE t_compress (id number) COMPRESS;

ERROR: syntax error at or near "for"

Index‑Organized Table – Not supported. The Oracle syntax ORGANIZATION INDEX triggers a syntax error.

postgres=# CREATE TABLE T_ORG_INDEX (
  ID NUMBER,
  NAME VARCHAR2(32),
  PRIMARY KEY(ID)
) ORGANIZATION INDEX;

ERROR: syntax error at or near "organization index"

Cluster Table – Not supported. The CLUSTER clause fails in openGauss; a partial cluster key can be used as a workaround in column‑store tables.

postgres=# CREATE CLUSTER t_cluster (id number) SIZE 600;

ERROR: syntax error at or near "cluster"

Partitioned Table – openGauss currently supports only range partitioning. Attempts to create list, hash, or composite partitions produce syntax errors.

postgres=# CREATE TABLE t_range_partition (
  prod_id NUMBER(6),
  cust_id NUMBER,
  time_id DATE,
  ...
) PARTITION BY RANGE (time_id) (
  PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')),
  ...
);

-- List partition example (fails)
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'),
  ...
);

ERROR: syntax error at or near "LIST"

Nested Table – Supported via CREATE TYPE and column definition referencing the type.

postgres=# CREATE TYPE t_type AS (f1 int, f2 text);
postgres=# CREATE TABLE t_compfoo (a int, b t_type);

Summary of Compatibility

Heap tables – fully compatible.

Temporary tables – fully compatible (both transaction‑level and session‑level).

Table compression – partially compatible (basic compression works; OLTP compression not supported).

Index‑organized tables – not compatible.

Cluster tables – not compatible (partial cluster key may be used as an alternative).

Partitioned tables – partially compatible (only range partitioning is supported).

Nested tables – functionally compatible, though the DDL syntax differs.