7 Replies Latest reply on Mar 26, 2019 8:50 AM by Scott Wesley

    18.3 Max_string_size: ORA-01450 for function based index

    Scott Wesley

      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.