How OpenGauss Handles Oracle Objects: Indexes, Constraints, Views, and More

1. Indexes

Indexes are special data structures that speed up data retrieval. In Oracle, the two main index types are B* tree indexes and bitmap indexes. openGauss fully supports B* tree indexes and provides work‑arounds for features that differ from Oracle.

1) B* tree indexes

All leaf nodes form an ordered list. Oracle’s default indexes (regular, composite, unique, reverse, function‑based, global) map directly to openGauss.

Regular index : created on a single column without extra keywords. postgres=# create index single_index on product(name); Composite index : created on multiple columns. postgres=# create index mutli_index on product(id, name); Unique index : enforces uniqueness via the UNIQUE keyword.

postgres=# create unique index unique_index on product(id, name);

Reverse index : Oracle uses REVERSE; openGauss replaces it with DESC. postgres=# create index reverse_index on product(name DESC); Function‑based index : indexes the result of a function. postgres=# CREATE INDEX func_index ON product(trunc(name)); Partition index : required for partitioned tables; created with the LOCAL keyword.

postgres=# CREATE INDEX IDX_PARTI_RANGE_ID on t_range_partition(prod_id) LOCAL;

2) Bitmap indexes

Bitmap indexes store a bitmap for each distinct value, suitable for low‑cardinality columns (e.g., gender). openGauss does not currently support bitmap indexes, and attempts to create them result in syntax errors.

postgres=# create bitmap index bit_index on product(name);
ERROR:  syntax error at or near "bitmap"

2. Constraints

Constraints enforce data integrity. openGauss is compatible with all Oracle constraint types.

1) NOT NULL

postgres=# create table t_notnull(
   id number not null,
   name varchar2(20)
);
CREATE TABLE

2) Primary key

postgres=# create table t_primary_key(
   id number not null primary key,
   name varchar2(20)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_primary_key_pkey" for table "t_primary_key"
CREATE TABLE

3) Unique constraint

postgres=# create table t_unique(
   product_id   number not null,
   product_name number not null,
   product_type varchar2(50),
   supplier_id  number,
   CONSTRAINT t_unique_u1 UNIQUE (product_id, product_name)
);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "t_unique_u1" for table "t_unique"
CREATE TABLE

4) Foreign key

Defines a relationship between a parent and child table. openGauss enforces the same referential integrity rules, though the example shows an error because the referenced table lacks a suitable unique constraint.

postgres=# create table t1(id number);
CREATE TABLE
postgres=# create table t2(id number, cc number, constraint fk_t2_id foreign key(id) references t1(id));
ERROR:  there is no unique constraint matching given keys for referenced table "t1"

5) Check constraint

postgres=# create table t3(id number, sal number, constraint ck_t3_sal check(sal between 5000 and 50000));
CREATE TABLE
postgres=# insert into t3 values (1,6000);
INSERT 0 1
postgres=# insert into t3 values (1,600);
ERROR:  new row for relation "t3" violates check constraint "ck_t3_sal"
DETAIL:  Failing row contains (1, 600).

3. Views

Views simplify query reuse. openGauss supports Oracle’s view syntax but lacks some optional clauses.

1) Relational view

postgres=# CREATE OR REPLACE VIEW prod_view AS select p.id, p.name from product p;
CREATE VIEW

Options such as WITH READ ONLY and WITH CHECK OPTION are not implemented.

2) Materialized view

postgres=# create materialized view mater_view as select p.id, p.name from product p;
SELECT 0

Materialized views store data physically; openGauss requires manual refresh.

4. Sequences

Sequences generate unique numbers, similar to MySQL’s AUTO_INCREMENT. openGauss fully supports them.

postgres=# create sequence autoincre
   minvalue 1
   maxvalue 9999999999999
   start with 1
   increment by 1;
CREATE SEQUENCE

5. Synonyms

Synonyms act as aliases for tables or columns, simplifying object references.

postgres=# create synonym product_syn for product;
CREATE SYNONYM

6. Compatibility Summary

Indexes : B* tree – compatible (use DESC instead of REVERSE); Bitmap – not compatible.

Constraints : NOT NULL, primary key, unique, foreign key, and check – all compatible.

Views : Relational and materialized – compatible, but WITH READ ONLY and WITH CHECK OPTION are not supported.

Sequences : Compatible.

Synonyms : Compatible.