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/pic/developer-ecology/3mfxe2r26qbaq_c50f50e14c2447959ba0c272c4967106.jpeg?x-oss-process=image/resize,h_160,m_lfit)