将函数索引从Oracle迁移到PostgreSQL

IT那活儿发布于2023-01-11 13:20 / 1144人阅读

我们使用AWSSchema ConversionTool（SCT）来转换数据库的元数据。通过AWS转换后的脚本在PostgreSQL中执行，发现函数索引无法成功执行。

通过Oracle查询发现其使用了substr和to_char等函数。

CREATE INDEX "HB_E2E"."IDX_CUST_ID_I" ON "HB_E2E"."FTP_DIPAN" (SUBSTR(TO_CHAR("CUST_ID"),-1))

而在PostgreSQL中执行则报ERROR: functions in index expression must be marked IMMUTABLE

手动执行上述函数，并不报错。

根据PostgreSQL文档，函数可以是3种类型，每一个函数都有一个易变性分类可能是VOLATILE、STABLE或者IMMUTABLE。如果CREATEFUNCTION命令没有指定一个分类，则默认是VOLATILE。

VOLATILE函数可以做任何事情，包括修改数据库（比如Update）。在使用相同的参数连续调用时，它能返回不同的结果。优化器不会对这类函数的行为做任何假定。在每一行需要volatile 函数值时，一个使用 volatile 函数的查询都会重新计算该函数。

STABLE函数不能修改数据库,并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果。这种分类允许优化器把该函数的多个调用优化成一个调用。特别是，在一个索引扫描条件中使用包含这样一个函数的表达式是安全的（因为一次索引扫描只会计算一次比较值,而不是为每一行都计算一次，在一个索引扫描条件中不能使用VOLATILE函数）。

IMMUTABLE函数不能修改数据库并且被确保用相同的参数永远返回相同的结果。这种分类允许优化器在一个查询用常量参数调用该函数时提前计算该函数。例如，一个 SELECT ... WHERE x = 2 + 2这样的查询可以被简化为SELECT ... WHERE x = 4，因为整数加法操作符底层的函数被标记为IMMUTABLE

通过查询pg_proc，可以确认函数类型，例如sysdate函数。

SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc FROM pg_proc WHERE proname like ‘sysdate%’;

Provolatile为s，则说明是STABLE函数。

上述有点难以理解，我们来用案例实际说明一下。这里使用current_timestamp来说明。

可以看到current_timestamp是的Provolatile状态s，是STABLE函数。

STABLE函数不能修改数据库,并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果。

我们理解如下：

这个函数不能修改数据库，它只能查询时间。
并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果。

这个是什么意思呢？当你查一张大表，而这张表每一行要使用这个函数的时候，时间其实是在流逝的，但是你表上所有数据行都必须使用最开始的那个时间。（可以理解为事务开始的时间）。

我们来找类似的几个时间函数测试一下。

除了clock_timestamp是VOLATILE，now和sysdate都是STABLE

我们创建一张表。

create table test_function

(

id numeric,

now_time timestamp without time zone,

sysdate_time timestamp without time zone,

clock_time timestamp without time zone

);

插入10000行数据

insert into test_function

select generate_series(1,10000),now(),oracle.sysdate(),clock_timestamp();

插入完成后可以看到，当查询这张表的时候，now(),oracle.sysdate()这种为STABLE的，时间不会发生变化，而clock_timestamp为VOLATILE类型则发生了变化。

至此，要创建函数索引，就必须把函数设置成IMMUTABLE。而实现办法可以自己建一个IMMUTABLE函数，该函数接受输入参数作为numeric类型。然后在创建函数索引的地方使用自己创建的。由于我这里的函数索引使用了2种函数，一个是substr,一个是to_char，substr已经是IMMUTABLE的,所以只需要将to_char函数建成IMMUTABLE就行了。

CREATE OR REPLACE FUNCTION immutable_to_char(numeric) RETURNS character varying

select aws_oracle_ext.to_char($1)

LANGUAGE SQL IMMUTABLE;

CREATE INDEX idx_cust_id_i ON hb_e2e.ftp_dipan USING BTREE (aws_oracle_ext.substr(immutable_to_char(cust_id::numeric),lengthb(immutable_to_char(cust_id::numeric)) ) ASC);

再次执行asc排序类的sql，发现已经可以使用这个函数索引了。

参考文档：

函数稳定性讲解- retalk PostgreSQL functions [volatile|stable|immutable ]

https://github.com/digoal/blog/blob/master/201212/20121226_01.md

MigratingFunction based indexes from Oracle to PostgreSQL

https://askdba.org/weblog/2018/01/migrating-function-based-indexes-from-oracle-to-postgresql/

GPU云服务器云服务器从oracle迁移到mysql PPAS 从oracle迁移到mysql PPAS 从db2迁移到oracle 如何将数据迁移到oss

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130105.html

阿里云如何打破Oracle迁移上云的壁垒

摘要：摘要第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破迁移上云的壁垒为题的演讲。于是，阿里云给出了上面的解决方案。摘要： 2018第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指数据库管理系统，面对Oracle迁移上云的壁垒，阿里云如何能够打破它呢？本文提出了Oracle 到云数据库P...

chavesgu 2019-06-25 18:39 评论0 收藏0
新书推荐 |《PostgreSQL实战》出版(提供样章下载)

摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 评论0 收藏0
你应该使用哪个云数据库？

摘要：云计算云计算的基本原则是采用一次性和可更换的多台机器，这对采用云计算技术以及在云中实施的数据库系统的功能有直接影响。云数据库属于相同的类别，而新系统明显倾向于并行优先。与非云系统相比，云计算系统向数据库应用程序公开资源利用控制要常见得多。云计算的基本原则是采用一次性和可更换的多台机器，这对采用云计算技术以及在云中实施的数据库系统的功能有直接影响。传统数据库大致可以分为并行优先(例如Mo...

wuaiqiu 2021-11-15 11:36 评论0 收藏0
面对众多云数据库，应该使用哪个云数据库好？

摘要：云计算的基本准则是采用一次性和可更换的多台机器，这对采用云计算技术及其在云中实施的数据库系统的功能有直接影响。与非云系统相比，云计算系统向数据库应用程序公开资源利用控制要常见得多。云数据库使用哪个云数据库好云数据库哪个好云计算的基本准则是采用一次性和可更换的多台机器，这对采用云计算技术及其在云中实施的数据库系统的功能有直接影响。传统数据库大致可以分为并行优先（...

Lowky 2021-11-22 13:54 评论0 收藏0