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
Solved

ORA-01450: maximum key length (6398) exceeded

Posted on 2014-04-29
12
813 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
12 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 334 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 166 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 13

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 76

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 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 76

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 22

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

860 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