Solved

How to resolve "ORA-01450: maximum key length (6398) exceeded" error?

Posted on 2013-11-05
8
5,790 Views
Last Modified: 2013-11-20
I am facing the below error when I try to create primary key constraint on table_a.

"ORA-01450: maximum key length (6398) exceeded"

table_a structure is below,

create table table_a
(
  col_a        VARCHAR2(32 BYTE),
  col_b        VARCHAR2(32 BYTE),
  col_c        VARCHAR2(3 BYTE),
  col_d        DATE,
  col_e        VARCHAR2(3000 BYTE),
  col_f         VARCHAR2(4000 BYTE),
  col_g        DATE
)


Ran below sql statement and got an error,


ALTER TABLE table_a ADD (
  CONSTRAINT PK_table_a
 PRIMARY KEY
 (col_a, col_b, col_c, col_e, col_f)
    USING INDEX
    TABLESPACE WEBRPT_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

I checked max length by running below sql statement,

select max(length(col_a||col_b||col_c||col_e||col_f))
from table_a;

Looks like max length is 323. Not sure why I get above error. The question is how I resolve this issue. Please advise.
0
Comment
Question by:IT_ETL
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 39624480
Since some the columns 'can' contain up to 3000 and 4000 bytes, this is the problem.

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


Do you really not have a natural primary key without those large columns?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 39624500
the total "possible" length of entire key (all columns in the index) must be less than 6398

your key is potentially 7067 bytes long (plus a little for header information)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39624507
what version of the db are you using?

if 12c you can use the new hash and substr options to get a smaller key.

if 11g or lower you can create a unique index on the concatenation (subject to 4k limit) or some other function; and declare the columns not null; but it won't really be the same thing as a primary key
0
 

Author Comment

by:IT_ETL
ID: 39625048
"Do you really not have a natural primary key without those large columns? "

No, Should I add sequence number as part of primary key constraint instead of two large columns.

"what version of the db are you using?"

Oracle 10g


"if 11g or lower you can create a unique index on the concatenation (subject to 4k limit) or some other function; and declare the columns not null; but it won't really be the same thing as a primary key"

As you said, it won't really be useful.
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.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 39625068
>>Should I add sequence number as part of primary key constraint instead of two large columns.

Adding a new column (sequence of GUID) seems like the only solution you have if you cannot change the design to have a natural primary key.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 39626134
Having a column in the primary key with byte sizes of 3000 or 4000 .. looks like a bad design with no doubt. i have not seen such thing in my experience.

I believe you need to have the design set right first by identifying which are those columns which make up the primary key, may be using oracle sequences can be an option but you should think in those lines.

Assuming you were able to setup that primary key without errors - how are you going to use that and how beneficial is that going to be in terms of performance etc are some of the questions which might come up post that right.

May be you need to throw some light on your actual requirement and i hope you get some good feedback from experts.

Thanks,
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 39626205
I agree that it appears to be a 'bad' design.

I would also like to learn more about the actual system/data/???

IT_ETL, if you can provide more about the data without posting any 'trade secrets', I'm sure we can help.

Typically a date, down to the second, is good enough for use in a primary key along with some additional  id.  When it isn't, then look to a timestamp column which supports fractional seconds.

In the database I support, even this isn't enough but I was lucky, I could add a sleep for .01 seconds per transaction to ensure a different timestamp.

If you cannot afford a fractional second delay, then you might go with a sequence or guid.  I do this in some tables as well.

I am interested what in your 3000 and 4000 byte columns actually make a record unique and suitable for a primary key.

You might get by with a lookup table that holds all the distinct values for the large text with a number id.  Then you have a foreign key in the main table or use in the primary key.
0
 

Author Comment

by:IT_ETL
ID: 39664040
Thank you all for posting your comments.

I do agree it was a bad design. But I have modified the design, added a new dimension table, a new key field (from dimension table) has been added to the fact table along with other fields col_a, col_b, and col_c will be part of the primary key constraint in a fact table.

So, the issue has been resolved as I don't need to use below fields to create a primary key constraint anymore.
 
col_e  VARCHAR2(3000 BYTE),
col_f         VARCHAR2(4000 BYTE)
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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now