[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


ORA-01450: maximum key length (6398) exceeded

Posted on 2014-04-29
Medium Priority
Last Modified: 2014-06-10
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.
Question by:Rao_S
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 1336 total points
ID: 40029762
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.
LVL 74

Assisted Solution

sdstuber earned 664 total points
ID: 40029768
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.

Author Comment

ID: 40029795
What is a function index? Can I create a function which trims the column?
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


Author Comment

ID: 40029805
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.
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40029811
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 ;-)
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40029825
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.
LVL 74

Expert Comment

ID: 40029948
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.
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 40030033
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.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40030512
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.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40030531
>>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

LVL 23

Expert Comment

by:Steve Wales
ID: 40124062
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question