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
Categories: Administration, ORA errors
Adityanath's Oracle Blog 
Pretty! This was a really wonderful article. Many thanks for
providing this info.
Wow that was unusual. I just wrote an incredibly long comment but after I clicked submit my comment didn’t show up.
Grrrr… well I’m not writing all that over again. Regardless,
just wanted to say fantastic blog!
I am impressed with this web site, rattling I am a fan.
Simply a smiling visitor here to share the love (:, btw great pattern.
Hiya, I am really glad I have found this information. Today bloggers publish just about gossips and web and this is really annoying. A good web site with interesting content, that’s what I need. Thanks for keeping this site, I’ll be visiting it. Do you do newsletters? Can’t find it.
Hey There. I found your blog using msn. This is a
really well written article. I will make sure to bookmark it
and return to read more of your useful info. Thanks for the post.
I’ll certainly return.
Hello my friend! I wish to say that this article is amazing, great written and come with almost all important infos.
I would like to peer extra posts like this .
Greate article. Keep writing such kind of info on your blog.
Im really impressed by your blog.
Hello there, You have done a great job. I will certainly digg it and personally suggest to my friends.
I am confident they will be benefited from this website.
Can I just say what a relief to discover someone who really understands what they’re talking about over
the internet. You definitely realize how to bring a problem to light and make
it important. A lot more people really need to check this out and understand this side of your story.
I was surprised you’re not more popular because you surely possess
the gift.
Very nice post. I just stumbled upon your weblog and wished to say that I have
really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed
and I hope you write again soon!
Very nice post. I absolutely love this site. Continue the good work!
Hello my friend! I want to say that this article is amazing,
great written and come with approximately all vital infos.
I’d like to look more posts like this .
Hi my friend! I wish to say that this article is awesome,
great written and come with almost all significant infos.
I’d like to see extra posts like this .
Thanks for the post.Much thanks again. Great.
Hello to all, because I am really eager of reading this weblog’s post to be
updated on a regular basis. It consists of pleasant stuff.
Hey there! This is my 1st comment here so I just wanted to give a quick shout out and say I truly enjoy reading your posts. Can you suggest any other blogs/websites/forums that deal with the same topics? Thanks for your time!
Good day very cool blog!! Guy .. Excellent .. Amazing ..
I’ll bookmark your blog and take the feeds also? I’m satisfied to find numerous helpful
info here in the post, we’d like develop more strategies in this regard, thank you for sharing.
. . . . .