• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 946
  • Last Modified:

ORA-01450: maximum key length (6398) exceeded

How can i resolve the following issue in creating an index?
ORA-01450: maximum key length (6398) exceeded..

I am trying to create a unique index with 7 columns, where 2 columns are varchar2(4000 Byte). Even though this column is large, the value in the column is always very small, not ever more than 10-15 characters.
  • 4
  • 2
  • 2
  • +3
3 Solutions
slightwv (䄆 Netminder) Commented:
Have you looked at the action for that error?


Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system. See also your operating system-specific Oracle documentation.
If your data is small, then resize the columns.  There is no advantage to allowing bigger data unless if you actually need to support it.

otherwise, you'll either have to either not index those values, or create an index on the concatenation of the values.  Preferably with a delimiter and substr to enforce size limit.

It won't be used for most queries but could still enforce uniqueness.
Rao_SAuthor Commented:
What is a function index? Can I create a function which trims the column?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Rao_SAuthor Commented:
I did look at the action for that error -->
The ORA-01450 error is related to your db_block_size and the maximum key length for your database is approximately 40% of the database block size minus some overhead.
Alexander Eßer [Alex140181]Software DeveloperCommented:
FBI, aka Function Based Index:
official docs here: http://docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_indexes.htm#ADFNS00505

other nice docs here: http://www.dba-oracle.com/t_garmany_easysql_function_based_indexes_fbi.htm
or here: http://www.oracle-base.com/articles/8i/function-based-indexes.php

And yes, you may create a FBI that "trims" the columns or whatever function(s) you'd like to use upon your columns.
But beware!!! Doing so could possibly lead to poor performance or at least some perf. impacts ;-)
slightwv (䄆 Netminder) Commented:
Another note on function-based indexes:
Developers need to know about them so they can write their SQL to exactly match them.

Oracle's optimizer can be pretty picky when it comes to their use.
I'd recommend shrinking the columns if you're able.

If your data is supposed to be in the 10-15 character range, then allowing 4000 is simply providing an extra window for bad data.

Even though I suggested the FBIs, as noted above they have some serious caveats.  Not the least of which is making sure you don't create a function that breaks your data.
slightwv (䄆 Netminder) Commented:
I agree with sdstuber.

To add:
A lot of DBA's/Developers think they are being clever when designing columns and just MAX out all data type lengths for maximum flexibility.

As you can see, at times, this isn't actually clever and can cause problems.

Since sdstuber mentioned breaking data, I'll add that by maxing out data lengths in the database, it can also break poorly coded applications.

Here's an example:
Say the requirement for an app states the max length for column XYZ is 20 characters.
The DBA being 'clever' defines the column as varchar2(4000).
The input app development team messes up and allows up to 500 characters to be entered.
The database gladly accepts it.
Say the XYZ column is used in payroll checks and there is a max of 20 characters allowed or the check is void.
The check printing development team just prints what the database provides.

See the possible issue?

I see the database design as an extension of requirements.  Have it catch flaws in an applications data before they become problems later.
Mark GeerlingsDatabase AdministratorCommented:
I agree with the recommendation to shrink the size of the varchar2(4000) columns to reasonable sizes.  

This will involve some work, since Oracle does not allow us to shrink the size of a column that contains any data.  So, you have to choose one of these options:
1. create a new table with all of the same columns as the current table; copy all of the data from this table to the new table; truncate the current table; alter the current table setting the size of these columns down to reasonable values; copy the data back to the current table; drop the new table; rebuild any indexes on this table; recompile any PL\SQL objects or views that use this table.
2. add a new column to the table that has the length you want; update each row copying the value of the varchar2(4000) column to the new column, and setting the value of the varchar2(4000) column to null; alter the current table setting the size of the varchar2(4000) column down to a reasonable value; update each row copying the value of the former varchar2(4000) column from the new column back to the original column; alter the table and drop the new column.
3. use the DBMS_REDEFINITION package to change this table's column definitions for you.

Note that option #2 may leave you with some "chained rows" and these can cause a significant performance penalty.
slightwv (䄆 Netminder) Commented:
>>since Oracle does not allow us to shrink the size of a column that contains any data.

Sure it does (in later releases) as long as none of the data makes it impossible to perform the alter.

SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(col1 varchar2(4000));

Table created.

SQL> insert into tab1 values('Hello');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tab1 modify col1 varchar2(10);

Table altered.

SQL> alter table tab1 modify col1 varchar2(1);
alter table tab1 modify col1 varchar2(1)
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big

SQL> spool off

Open in new window

Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now