Oracle DBA – A lifelong learning experience

ORA-01450: maximum key length (3800) exceeded

Posted by John Hallas on December 13, 2017

This is an oddity as essentially I have an index on a table but I cannot rebuild it because it suggests the block size should be bigger than 8K – however both the database and tablespace block size are 16K already.

The maximum allowed index key length depends on your block size. So the minimum allowed size reported in ORA-01450 varies, depending on which block size your index is using:

ORA-01450 maximum key length (758) exceeded -> (2K Block)

ORA-01450 maximum key length (1578) exceeded -> (4K block)

ORA-01450 maximum key length (3218) exceeded -> (8K Block)

ORA-01450 maximum key length (6498) exceeded -> (16K Block)
See MOSC Note 136158.1 and MOSC Note 236329.1 for more details on the ORA-01450 error and key length.

Even more odd was that I could drop the index and recreate it with no issues and yet rebuild failed every time, even after the recreate. Studying the table and data there did not seem to be a value that was much bigger than the others and certainly nothing that would cause one or more rows to be much larger than the others.

The index (PK) was defined as below with the third value being the column size

DWB_X_CMPTR_PRICE_DAY DAY_KEY 1 22 0 ASC
DWB_X_CMPTR_PRICE_DAY POS_ITEM_ID_CD 2 4000 4000 ASC
DWB_X_CMPTR_PRICE_DAY POS_IDNT_KEY 3 22 0 ASC
DWB_X_CMPTR_PRICE_DAY REGION_CD 4 22 0 ASC
DWB_X_CMPTR_PRICE_DAY COMPETITOR_CD 5 22 0 ASC

Looking at the maximum size of data in each column there was nothing close to the maximum value in any column.

max(length(POS_ITEM_ID_CD)) POS_ITEM_ID_CD, 13
max(length(DAY_KEY)) DAY_KEY, 8
max(length(POS_IDNT_KEY)) POS_IDNT_KEY, 9
max(length(REGION_CD)) REGION_CD, 1
max(length(COMPETITOR_CD)) COMPETITOR_CD 1
from BIA_RTL.DWB_X_CMPTR_PRICE_DAY

The command I was running was

alter index bia_rtl.DWB_X_CMPTR_PRICE_DAY_PK rebuild online;
alter index bia_rtl.DWB_X_CMPTR_PRICE_DAY_PK rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3800) exceeded

I did not want to drop and recreate the index in production as I could not get access to the system and I was worried that I might not be able to recreate despite having taken a copy of the data and being able to recreate it there.
So to cut to the chase how was the problem fixed.

Step one – log a call with MoS – normally a last step for me.
Step 2 – they eventually (after a lot of back and forth) told me that there was a bug and if I dropped the keyword ONLINE I would be able to rebuild the index. They were correct. I must admit I had not considered that option but I am glad it was such an easy fix. The bug was Doc ID 236329.1

cause: This is caused by issue Bug:2525767. The online rebuild of the index 
creates a journal table and index. This internal journal IOT table contains 
more columns in its index. Their total length is greater than number reported 
in ORA-01450 error message. This is a feature of online rebuild.
Maximum key length is calculated with respect to the database block size. It 
means that current value of the initialization parameter db_block_size is not 
large enough so that the internal journal IOT can be created without errors.

PS the reason that I was rebuilding the index is that tables in the tablespace are populated by append statements which write directly to the end of the bigfile and as data was deleted we needed to reorganise the tablespace to recover some of the wasted space

Advertisements

3 Responses to “ORA-01450: maximum key length (3800) exceeded”

  1. Manoj Gorai said

    Thanks John for such post and it is new learning. Pls keep posted such articles for new learning.

  2. fouedgray said

    Thanks for sharing this post.

  3. […] Read More (Community […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: