oracle trunc函数_Oracle的逻辑对象，openGauss都能hold住吗？

作者介绍

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

除了最常用的数据库表外，数据库的逻辑对象还包括索引、约束、视图、序列、同义词等，接下来我们将逐一评估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 TABLE

postgres=# 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 )

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

postgres=# create table t_not(

id number not ,

name varchar2(20));

CREATE TABLE

2、主键约束

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

postgres=# create table t_primary_key(

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

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

postgres=# create table t_unique

(

product_id number not ,

product_name number not ,

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

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

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

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、检查约束

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

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).

三、视图

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

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 VIEW

postgres=# 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 autoincre

minvalue 1

maxvalue 9999999999999

start with 1

increment by 1;

CREATE SEQUENCE

五、同义词

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

postgres=# create synonym product_syn for product;

CREATE SYNONYM

六、总结

对象类型	分类	是否兼容	备注
索引	B*索引	兼容	REVERSE关键字需用DESC替代
	位图索引	不兼容
约束	非空约束	兼容
	主键约束	兼容
	唯一约束	兼容
	外键约束	兼容
	检查约束	兼容
视图	关系视图	兼容	部分选项不兼容
	物化视图	兼容	部分选项不兼容
序列	兼容
同义词	兼容