-2

I am trying something like below and getting exception.

CREATE TABLE test1( col1 varchar(4000) NOT NULL, col2 varchar(4000) NOT NULL, col3 varchar(4000), col4 varchar(4000), col5 varchar(4000) NOT NULL, col6 varchar(4000), col7 varchar(4000) , PRIMARY KEY(col1, col2, col3))

Oracle version => 19.16.0.0.0

exception: Error report - ORA-01450: maximum key length (6398) exceeded 01450. 00000 - "maximum key length (%s) exceeded" *Cause:
*Action:

anurag1007
  • 107
  • 1
  • 10
  • [**ORA-01450**](https://docs.oracle.com/cd/B10501_01/server.920/a96525/e900.htm#1004903) maximum key length (string) exceeded **Cause**: *The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. * **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.* – astentx Jun 07 '23 at 07:23
  • **maximum key length (6398)** – Scary Wombat Jun 07 '23 at 07:27
  • You may use different tablespace of larger block size for the index, but 12000 symbols as a primary key seems to be quite strange modelling: use some hashing on text or identify different entities of this text by some attributes at the application side and make it a PK. – astentx Jun 07 '23 at 07:29

1 Answers1

1

This blog post was written to clarify this specific problem. This is the gist of it:

The smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes).

Each index entry must fit within one block. So the maximum size of an indexed value must be less than the block size. Minus some storage overheads. In a default installation this means the largest indexable value is around 6,400 bytes.

Since your primary key is enforcing a UNIQUE key (by the creation of a UNIQUE index), it also needs to fit into a single block.

You cannot change the block size of an already created database, see: https://docs.oracle.com/cd/E24693_01/server.11203/e24448/initparams049.htm and https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:226813934564

So, if you really, really want (I do not recommend this) to increase your block size, then you can export your database, create a new database with the desired block size and import the exported database.

However, I strongly recommend the reconsideration of this. Having three textual fields as the primary key spells disaster for performance because each time you search by your primary key fields values will be compared to very long texts. Hence, it would make much more sense to create a numeric field as the primary key and maybe a unique index on your three fields if you change the block size or, if possible, implement a cron job which would periodically check for new records whether they fulfill the uniqueness you expect to ease the burden on your RDBMS.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175