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

ORA-01450: maximum key length (6398) exceeded

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
Rao_S
Asked:
Rao_S
  • 4
  • 2
  • 2
  • +3
3 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
Rao_SAuthor Commented:
What is a function index? Can I create a function which trims the column?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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.
0
 
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 ;-)
0
 
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.
0
 
sdstuberCommented:
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
 
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.
0
 
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.
0
 
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> 
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
 
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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