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