oracle迁移函数,将函数索引从Oracle迁移到PostgreSQL

最新推荐文章于 2024-07-17 13:21:52 发布

笔记侠

最新推荐文章于 2024-07-17 13:21:52 发布

阅读量599

点赞数

文章标签： oracle迁移函数

我们使用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 FROMpg_proc WHERE proname like ‘sysdate%’;

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

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

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

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

我们理解如下：这个函数不能修改数据库，它只能查询时间。

并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果。

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

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

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

我们创建一张表。createtable test_function

(

id numeric,

now_time timestamp without time zone,

sysdate_timetimestamp without time zone,

clock_time timestamp without time zone

);

插入10000行数据insertinto test_function

selectgenerate_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 ORREPLACE FUNCTION immutable_to_char(numeric) RETURNS charactervarying

'selectaws_oracle_ext.to_char($1)'

LANGUAGESQL IMMUTABLE;

CREATE INDEXidx_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，发现已经可以使用这个函数索引了。