oracle迁移过来的函数表达式索引出现下列问题,可以尝试用下列办法解决
迁移时需要保证创建的函数本身的属性,输入固定的值返回值不能变.
demo:
create table TABLE1(id int);
CREATE INDEX X_IDX ON TABLE1 ((TO_NUMBER(ID)));
ERROR: functions in index expression must be marked IMMUTABLE
解决办法:
CREATE OR REPLACE FUNCTION my_to_number(p_value text)
RETURNS number
AS
$$
SELECT to_number(p_value);
$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE INDEX INDEX ON table1 ((my_TO_NUMBER(ID)));
![[Oracle]索引](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1634956/20241107/b30f131fc2184af8a7b29e924db9a549.jpeg?x-oss-process=image/resize,h_160,m_lfit)