We're reviewing differences between from 12.1 and 18.3, and found the following error in the new DB relating to an function based index definition.
I found the index in status UNUSABLE, and received this error upon recreation:
ORA-01450: maximum key length (6398) exceeded
There is plenty of decent information on this, but none really resonated with my case
https://www.oratable.com/ora-01450-maximum-key-length-exceeded/
https://richardfoote.wordpress.com/2013/09/12/12c-indexing-extended-data-types-part-i-a-big-hurt/
Until I saw this highlight an issue with function based indexes
ORA-01450: maximum key length (6398) exceeded
select value from v$parameter where name in ('max_string_size','db_block_size');
VALUE
-------
8192
EXTENDED
If I defined a simple table with a small string
create table max_key(str varchar2(30));
Then create a function that regurgitates that string
create or replace function fn_mk(str varchar2) return varchar2 deterministic is
begin
return str;
end;
/
The following fails
create index mk_i on max_key(fn_mk(str));
/
ORA-01450: maximum key length (6398) exceeded
If I try to be specific with the returned datatype in the function definition, it still fails
create or replace package mk_pkg as
subtype ret_string is varchar2(30);
function fn_mk(str varchar2) return ret_string deterministic;
end mk_pkg;
/
create or replace package body mk_pkg as
function fn_mk(str varchar2) return ret_string deterministic is
begin
return str;
end;
end mk_pkg;
/
create index mk_cast_i on max_key(mk_pkg.fn_mk(str));
/
ORA-01450: maximum key length (6398) exceeded
However, if I CAST the result within the fb index, then it succeeds, as long as replicate the entire expression in the where clause, of course.
create index mk_cast_i on max_key(cast(fn_mk(str) as varchar2(30)));
/
explain plan for select * from max_key where cast(fn_mk(str) as varchar2(30)) = :B;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MAX_KEY | 1 | 34 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MK_CAST_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Is there a better way to address this particular issue?
Scott.