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
Probably, you ended up rebiulding index without ONLINE.
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?
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.
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.
[…] 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 […]
Thank you, Alex! Here’s the answer to my problem today- quick and simple! :)