ORA-01450 During Online Index Rebuild

Posted in: Technical Track

We hit an ORA-01450 error today trying to do online rebuild for an index in an unusable state. This was a non-unique index on a fairly large column — VARCHAR2(800 CHAR).

SQL> alter index i1 rebuild online;
alter index i1 rebuild online
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

It rang a bell. I remembered that I encountered this issue a while ago, but I couldn’t recall the details. I know that it has nothing to do with the actual data size — it’s an error that can occur during index creation. A single index block must be able to fit at least two index entries, so the maximum index key size defined by the block size and overhead.

The key length is calculated as:

key length = sum of all column lengths
           + number of columns
           + 2 (key length)
           + 6 (restricted ROWID)
           + 1 (ROWID field length)

If I didn’t miss anything, the key length for my index should be 800 + 1 + 2 + 6 + 1 = 810. Hold on . . . we have character length semantic here — let’s check the database character set:

SQL> select value
  2  from nls_database_parameters
  3  where parameter = 'NLS_CHARACTERSET';


AL32UTF8 is a multi-byte character set with up to 4 bytes per character, so VARCHAR2(800 CHAR) actually means VARCHAR2(3200 BYTE). Still, the length of the index key (3210) is within 3215 limit.

Okay. Now, the rebuild operation doesn’t actually recreate an index, so it shouldn’t be failing. But, I tried an online rebuild which builds an Index-Organized Table (IOT) in the background to keep track of the changes while an index is being created. That’s how Oracle implements online version of the rebuild. These changes are then merged into the rebuilt index to make it consistent.

At this moment I already knew the answer but for the sake of curiosity I enabled SQL_TRACE and reproduced the error. Here is the relevant part of the trace:

PARSING IN CURSOR #13 len=40 dep=0 uid=57 oct=9 lid=57 tim=1186710032936342 hv=51312995 ad='7fd28768'
alter index oracloid_idx2 rebuild online
PARSE #13:c=0,e=1131,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=4,tim=1186710032936340
PARSING IN CURSOR #4 len=168 dep=1 uid=57 oct=1 lid=57 tim=1186710032936899 hv=2056963451 ad='7d9fa040'
create table "ORACLOID"."SYS_JOURNAL_749466" (

C0 VARCHAR2(3200)

,  opcode char(1), partno number,  rid rowid,

primary key( C0 , rid )

) organization index TABLESPACE "USERS"
PARSE #4:c=0,e=413,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=4,tim=1186710032936897
EXEC #4:c=4000,e=3219,p=0,cr=30,cu=22,mis=0,r=0,dep=1,og=4,tim=1186710032940133
ERROR #4:err=1450 tim=2586752363

Fair enough. The IOT index key is actually bigger than 3215, and that’s causing the online index rebuilt to fail. A quick search on the Metalink uncovered note 236329.1. I suppose I could have started from there in the first place, but, since I walked the investigation path anyway, I thought it would be nice to cover it here.

Additional reading on index and IOT internals:

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

8 Comments. Leave new

Hi Alex,

Probably, you ended up rebiulding index without ONLINE.


Alex Gorbachev
July 6, 2008 8:25 am

Yep – that’s workaround. Another one would be to use a larger block size tablespace but there is a lot to think about before changing block size for an index.


Julian Dyke`s “IOT Internals” whitepaper, page 5, say that for Oracle version 9.X, in fact, it is NOT required that any index
block must contain at least TWO index entries per block. For example, with block size 16K – maximum key lengths = 12958. Can you comment this?

Alex Gorbachev
July 30, 2008 9:25 am

Not sure where you take it from… Page 11 of the IOT Internals presentation that I referenced says that Maximum primary key lengths in Oracle 9.2 for 16K block size is 3800 bytes, which actually contradicts to the Metalink Note 136158.1 (6498 bytes) but it might be the difference between and index and IOT table (thought, I don’t see how it would be *significantly* different).

Now, I can’t imagine why the requirements of having at least two keys per block would be lifted – it’s impossible with the structure of a b-tree index and more specifically, it’s about the *branch* blocks that should fit at least two index keys and not leaf blocks.


Oops! I see “Index Internals”, sorry.

Alex Gorbachev
July 30, 2008 12:03 pm

Yeah… Actually, I must be wrong regarding minimum two index key entries per branch block — it doesn’t need to be two entries. The beginning of a range for an n level branch block is inherited from an n-1 level parent branch block.

Che fine ha fatto ORA-01450? « Oracle and other
September 2, 2008 10:45 am

[…] fase successiva è la ricerca su Google, questi mi porta a un post di Alex Gorbachev su Pythian. Riporta un problema diverso, ma con lo stesso […]

Kellyn Pedersen
June 10, 2010 12:57 pm

Thank you, Alex! Here’s the answer to my problem today- quick and simple! :)


Leave a Reply

Your email address will not be published. Required fields are marked *