Forum Stats

  • 3,840,134 Users
  • 2,262,571 Discussions


18.3 Max_string_size: ORA-01450 for function based index

Scott Wesley
Scott Wesley Member Posts: 6,240 Gold Crown
edited Apr 23, 2019 7:35PM in General Database Discussions

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

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-------8192EXTENDED

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 isbegin 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 isbegin 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?



Best Answer

  • Scott Wesley
    Scott Wesley Member Posts: 6,240 Gold Crown
    edited Mar 26, 2019 4:50AM Answer ✓

    Yes, using a virtual column also resolved the issue. I think I like this solution.

    create table max_key2(str varchar2(30),str_fn varchar2(30) GENERATED ALWAYS AS (cast(fn_mk(str) as varchar2(30))) VIRTUAL);create index mk_i on max_key2(str_fn);/insert into max_key2(str) values ('Scott');-- explain of this shows index useselect * from max_key2 where str_fn = :b;