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 —
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
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" (
, 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
Interested in working with Alex? Schedule a tech call.