ORA-01450 During Online Index Rebuild

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';

VALUE
--------------
AL32UTF8

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
END OF STMT
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"
END OF STMT
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:

• Metalink Note 136158.1 – ORA-01450, and Maximum Key Length – How it is Calculated
• Julian Dyke – IOT Internals whitepaper