[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORA-01450: maximum key length (6398) exceeded

Posted on 2014-04-29
12
Medium Priority
?
902 Views
Last Modified: 2014-06-10
Hello,
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.
0
Comment
Question by:Rao_S
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1336 total points
ID: 40029762
Have you looked at the action for that error?

http://ora-01450.ora-code.com/

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.
0
 
LVL 74

Assisted Solution

by:sdstuber
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.
0
 

Author Comment

by:Rao_S
ID: 40029795
What is a function index? Can I create a function which trims the column?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Rao_S
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.
0
 
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 ;-)
0
 
LVL 78

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.
0
 
LVL 74

Expert Comment

by:sdstuber
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.
0
 
LVL 78

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.
0
 
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.
0
 
LVL 78

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

0
 
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

872 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