Oracle的逻辑对象，openGauss都能hold住吗？

本文由 dbaplus 社群授权转载。

除了最常用的数据库表外，数据库的逻辑对象还包括索引、约束、视图、序列、同义词等，接下来我们将逐一评估 Oracle 对象在 openGauss 中的兼容性。

一、索引

索引是一种特殊的数据结构，能够快速定位到所要查找的数据。为了提高查询效率，在创建表之后通常需要在关键字段上创建索引。按照数据结构分类，Oracle 数据库中的索引分为 B*树索引及位图索引。

1、B*树索引

B*树索引是数据库中最常用的索引类型，所有的叶子节点数据构成了一个有序链表。

在 Oracle 数据库中大多数情况下创建的索引都属于 B*树索引，按照使用场景进行细分，可以分为常规索引、复合索引、唯一索引、反向索引、基于函数的索引、全局索引。

1）常规索引：

基于表中单个列建立的索引，也是最简单的索引创建方式，无需额外关键字，索引默认按照升序排序。

postgres=# create index single_index on product(name);CREATE INDEX

复制代码

2）复合索引：

基于表中多个列创建索引。

postgres=# create index mutli_index on product(id,name);CREATE INDEX

复制代码

3）唯一索引：

创建索引时，通过指定 unique 关键字限制索引列中的值必须唯一，与唯一约束功能类似。

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

复制代码

4）反向索引：

索引列按照降序进行排序，在 Oracle 中通过 REVERSE 关键字进行创建，但 openGauss 中不支持关键字 REVERSE，可以通过指定 DESC 关键字进行替代。

postgres=# create index reverse_index on product(name) REVERSE;ERROR:  syntax error at or near "REVERSE"LINE 1: create index reverse_index on product(name) REVERSE;postgres=# create index reverse_index on product(name desc);CREATE INDEX

复制代码

5）基于函数的索引：

基于函数的索引适用于某个字段做查询的时候经常带函数操作，openGauss 中可以支持基于函数的索引。

postgres=# CREATE INDEX func_index ON product(trunc(name));CREATE INDEX

复制代码

6）分区索引：

分区表的数据量通常较大，随着数据增大，会导致分区表上的 B*树的索引的性能下降，维护成本也随之升高。对于分区表而言，基于每个分区进行索引创建则十分必要。创建分区索引需要加 local 关键字，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 TABLEpostgres=# create index IDX_PARTI_RANGE_ID on t_range_partition(prod_id) local;CREATE INDEX

复制代码

2、位图索引

位图索引的存储结构与 B*树差异较大，ROWID 并不直接存储。位图的每个位置映射到一个可能的 ROWID 上，位图上每个位置的内容用于表示该行特定的值是否在位图列中。所以，位图的每个位置存储特殊行和相关 ROWID 的信息。如果该 ROWID 的行的值匹配，则该特殊 rowid 位置存储为"1"，否则储存为"0"。

位图索引适用于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有 0 和 1 的情况。openGauss 当前版本暂不支持位图索引。

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

复制代码

二、约束

数据库约束用来防止无效的数据进入到表中，以保护数据的实体完整性，最常见的数据库约束就是主键，大多数 SQL 规范中都会包含建表需要定义主键。在 Oracle 数据库中，数据库约束分为 5 类：非空约束、主键约束、唯一约束、外键约束、检查约束，openGauss 可以兼容所有约束类型。

1、非空约束（NOT NULL）

如果在列上定义了非空约束，那么当插入数据时，必须保证数据不能为 NULL，如果数据为空则无法插入，openGauss 支持非空约束。

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

复制代码

2、主键约束

当定义主键约束后，该列的值不能重复而且不能为 NULL。一张表最多只能有一个主键，但是可以由多个唯一约束。创建主键后，openGuass 会自动创建表名+pkey 的索引。

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、唯一约束

唯一约束与主键的区别是，当定义了唯一约束后，该列值是不能重复的，但是可以为 NULL，并且对于同一张表，唯一约束可以存在多个。创建唯一约束后，openGuass 也会自动创建唯一索引。

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、外键约束

用于定义主表和从表之间的关系，外键约束要定义在从表上，主要则必须具有主键约束或是唯一约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为 NULL。

外键约束会增加表插入、更新等 SQL 性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中，就推荐禁用外键约束。

postgres=# create table t1(id number);CREATE TABLEpostgres=# 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、检查约束

用于强制行数据必须满足的条件，假定在 sal 列上定义了 check 约束，并要求 sal 列值在 5000～50000 之间，如果插入数据不在此范围之间就会提示出错。

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

复制代码

三、视图

视图主要是为了提升开发效率，作为一个定义查询语句结果的对象存在的；根据是否真实存储数据，分为关系视图及物化视图。

1、关系视图

Oracle 的关系视图本身不包含任何数据，也是大家最常用的视图，通过 create view... as ...简化的语法就可以创建，还可以需求将视图设置为只读形式等。注意：在当前用户下创建视图需要 CREATE VIEW 系统权限。openGauss 可以兼容 Oracle 创建视图语法，但对于 Oracle 视图创建中的 with check option 以及 with read only 选项暂不支持。

postgres=# CREATE OR REPLACE  VIEW  prod_view AS select p.id, p.name from product p;CREATE VIEWpostgres=# CREATE OR REPLACE  VIEW read_view AS select p.id, p.name from product p WITH READ ONLY;ERROR:  syntax error at or near "READ"LINE 1: ..._view AS select p.id, p.name from product p WITH READ ONLY;postgres=# CREATE OR REPLACE  VIEW  check_view AS select p.id, p.name from product p WITH CHECK OPTION;ERROR:  WITH CHECK OPTION is not implemented

复制代码

2、物化视图

物化视图有别于关系视图，可以像数据库表一样可以真实存储数据。openGauss 可以支持物化视图的创建，但对于数据刷新需要手动刷新，不支持自动刷新。

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

复制代码

四、序列

序列可供多个用户用来产生唯一数值的数据库对象。在 MySQL 中可以通过自增长字段 auto_increment，而 Oracle 中没有自增长字段功能，通常通过建立序列来实现自增长的功能，openGauss 中可以支持 sequence 功能。

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

复制代码

五、同义词

同义词就是别名，可以为表、字段名建立别名，主要是为了简化书写，在当前用户下创建同义词需要 create synonym 权限。

postgres=# create synonym product_syn for product;CREATE SYNONYM

复制代码

六、总结

作者介绍：

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

原文链接：

Oracle的逻辑对象，openGauss都能hold住吗？

创作场景

Oracle 的逻辑对象，openGauss 都能 hold 住吗？

一、索引

1、B*树索引

2、位图索引

二、约束

1、非空约束（NOT NULL）

2、主键约束

3、唯一约束

4、外键约束

5、检查约束

三、视图

1、关系视图

2、物化视图

四、序列

五、同义词

六、总结