Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

18.3 Max_string_size: ORA-01450 for function based index

Scott WesleyMar 26 2019 — edited Apr 23 2019

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.
This post has been answered by Scott Wesley on Mar 26 2019
Jump to Answer

Comments

Gaz in Oz

How about setting the return value from the function as fixed length, for example:

...
  return substr(str, 1, 30);
...
Scott Wesley

I had tried that (still failed), though forgot to mention it on my journey towards CAST.

JohnWatson2

If you expect to use the index with equality predicates, then you can create the index on standard_hash(str)

1 person found this helpful
AndrewSayer

Does it work if you declare a virtual column with the appropriate size?

Substr is supposed to work but it looks like the projected length is no longer the 3rd argument, it might be worth raising a bug SR to fix that as an index on substr can still be used without a rewrite, making it quite appealing https://blog.dbi-services.com/index-on-truncdate-do-you-still-need-old-index-1/

Otherwise, stick to the cast, it’s annoying to have to remember to use it. It’s a shame there’s no way for a UDF to have a defined length output.

-edit

Just tested and substr does work fine. I think I am misremembering a test I did recently where it wasn’t quite appropriate.

1 person found this helpful
AndrewSayer

Not sure you saw my edit as it was around the time you marked it as helpful. Here’s a demo of appeasing this error message with substr. Note that the max key length is much shorter when doing an online build (Due to the internals of how Oracle does it)

https://livesql.oracle.com/apex/livesql/s/h5phsfxk21uxn1p9wnkby6g30

Scott Wesley

Ahh, so you're referring to substringing in the FBI expression, not within the called function. Which is pretty much the same as CAST? As in, it will still need to form part of the where clause expression.

Scott Wesley
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 use
select * from max_key2 where str_fn = :b;
Marked as Answer by Scott Wesley · Sep 27 2020
1 - 7

Post Details

Added on Mar 26 2019
#general-database-discussions, #sql
7 comments
1,243 views