Interesting issue happened today, while creating index in database(running 11.2.0.4 DB on Linux), I got ORA-01450: maximum key length (6398) exceeded.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE INDEX "TEST_USER"."DEMO_INDEX_1" ON "TEST_USER"."DEMO_TABLE" ("SEQUENCEGROUP", "SEQUENCENUMBER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ; 2 3 4 5 6
CREATE INDEX "TEST_USER"."DEMO_INDEX_1" ON "TEST_USER"."DEMO_TABLE" ("SEQUENCEGROUP", "SEQUENCENUMBER")
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
So I started searching on metalink to get details about ORA-01450 & found: ORA-01450 When Creating an Index (Doc ID 293599.1)
According to MOS note, There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size,it is 6398. Also the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter. That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage.
So I checked table definition to get details about datatype for columns used in table.
SQL> desc TEST_USER.DEMO_TABLE
Name Null? Type
----------------------------------------- -------- ----------------------------
CID VARCHAR2(4000)
RT VARCHAR2(4000)
SEQUENCEGROUP VARCHAR2(4000)
SEQUENCENUMBER VARCHAR2(4000)
CLIENTID VARCHAR2(4000)
COUNTRY VARCHAR2(4000)
KEYTYPE VARCHAR2(4000)
As per table definition creating index on two columns SEQUENCEGROUP & SEQUENCENUMBER would make index key length to 8000 which is more than 6398, which is causing failure in index creation.
So how to resolve this: So we have 2 ways
1. Limit column key length if possible:
So in my case, I altered both column datatypes to VARCHAR2(3000), so that index key length was restricted to 6000.Post table alteration, I was able to created index successfully.
2. Create index in tablespace with bigger block size:
So you can create tablespace with block size 16K/32K, and then create index in this new tablespace.
Now we need to understand why we have this restriction:
As per Oracle 8i documentation, index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block. Therefore, the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. The Oracle 8i Administrator’s Guide states that the maximum size of a single index entry is approximately one-half the data block size. However, when considering that we must also leave space in the block according to PCTFREE, INITRANS, and space for block overhead (Block Header, ROW Directory, Table Directory, etc) the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Oracle 9.2 onward, restriction on index data length has been increased from 3218 to 6398. So from 9.2 – 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.
Hope so u will find this post very useful:-)
Cheers
Regards,
Adityanath
