ORA-01450 During Online Index Rebuild

Jul 4, 2008 / By Alex Gorbachev

Tags:

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:

8 Responses to “ORA-01450 During Online Index Rebuild”

  • Asif Momen says:

    Hi Alex,

    Probably, you ended up rebiulding index without ONLINE.

    Regards

  • 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.

  • YAP says:

    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.

  • YAP says:

    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! :)
    ~Kellyn

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>