Online Rebuild

Here’s a little oddity that may be waiting to catch you out – but only if you like to create indexes with very long keys.

rem
rem     Script:         index_rebuild.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2009
rem

create table t1(
        v1      varchar2(4000),
        v2      varchar2(2387),
        v3      varchar2(100)
);

create index t1_i1 on t1(v1, v2);

alter index t1_i1 rebuild;
alter index t1_i1 rebuild online;

My key value is at the limit for an 8KB block size in Oracle 9i and later – which is roughly 80% of (block size – 190 bytes). In earlier versions of Oracle (prior to 9i) the limit was roughly half that (i.e. 40% rather than 80%).

If you try to create a longer key you’ll see Oracle error ORA-01450: “maximum key length (6398) exceeded”.  (If you’ve built your indexes using a different blocksize the number in brackets will be different – and you will have to adjust the demo code for 16KB and 32KB block sizes). The difference between my declared column lengths and the error limit relates to the overheads in an index entry – but seems to “forget” the one byte extra for non-unique indexes.

But this is the output you’ll (probably) get from running the script if you’re using an 8KB block size:

Table created.

Index created.

Index altered.

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

So I can create an index with a very large key, rebuild that index safely – and then fail when I try to rebuild the index with the online option!

What’s going on ?

When you use an online index rebuild Oracle has to create a log of all changes you make to the table while the rebuild is running. Once the log is set up Oracle creates a new index using a read-consistent copy of the data in the table and copies the logged changes into this index. then finishes off by tidying up the data dictionary, dropping temporary segments and dropping the log.

This process locks the table twice, by the way,  once while getting started and creating the log and then again when finishing off and dropping the log. If you read Richard Foote’s blog regularly you will know that these locks are only held for a short time but can be hard to acquire and may lead to a randomly long delay in the rebuild.

For my purposes though, the critical feature is the object that holds the logging information. This is created as an index organized table (IOT) with a name like sys_journal_NNNNN where NNNNN is the object_id of the index you are trying to rebuild. (You can check this by enabling sql_trace just before doing a rebuild.)

It’s the IOT that has the problem with the excess key length – its limit is still set to match the 40% limit used for earlier versions of Oracle, and the key to the IOT is the key defined for the index, plus the rowid for the row with that key.

It’s possible that this outdated limit is simply an oversight in the code; it’s possible that it’s a deliberate design decision that has to stay in place to allow for the different strategies for leaf-block splits adopted by standard B-tree indexes and the index structures supporting IOTs. Either way it’s a limitation that might finally catch you out months after you’ve created an unusually lengthy index key.

Just as a quick demonstration that the problem is in the IOT, we need only run a simple piece of DDL (the sample below was cut and pasted from a live session):

SQL> create table iot1(
  2     v1      varchar2(4000),
  3     v2      varchar2(2380),
  4     v3      varchar2(100),
  5     constraint iot1_pk primary key(v1,v2)
  6  )
  7  organization index
  8  overflow
  9  ;
create table iot1(
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded

Of course, when you see little oddities like this you might think: “I wouldn’t create an index like that – so I’m not worried”. But it’s worth spending a little time looking at the symptoms from different directions before dismissing the problem.

Try this experiment (starting with the same t1 heap table above) but see if you can guess what’s going to happen and why … before you read the explanation:

create or replace function f (i_in varchar2)
return varchar2
deterministic
as
begin
        return i_in;
end;
/

create index t1_f1 on t1(f(v3));

alter index t1_f1 rebuild;
alter index t1_f1 rebuild online;

You can rebuild the index but you can’t rebuild it online because you get the same Oracle error ORA-01450.

You didn’t build an index with a large key deliberately, but a function returning a varchar2() implicitly returns a 4,000 byte string – so your index has been built on a virtual column of 4,000 bytes.

select
        column_name, column_length
from
        user_ind_columns
where
        index_name = 'T1_F1'
/

COLUMN_NAME          COLUMN_LENGTH
-------------------- -------------
SYS_NC00004$                  4000

1 row selected.

If you want to build this type of index, and be able to rebuild it online, your index definition will have to be something like:

drop index t1_f1;
create index t1_f1 on t1(substr(f(v3),1,100));
alter index t1_f1 rebuild online;

With this definition, the function-based index can be rebuilt online. (Of course, it may no longer be the index you want, and some of your SQL has to be modified so that the predicates match the new index definition.)

Footnote 1: The 40% limit on IOT keys is further constrained if you are using a 16KB block size by a “hard” limit of 3,800 bytes – so rebuilding the index with a different blocksize will only work around the problem in a limited range of cases.

Footnote 2: Although I implied at the start of the article that it would be unusual to have very long index keys there are quite a lot of applications that define tables with fairly long (e.g. 254, 1000) varchar2() columns: “just in case”.  Watch out when you start creating multi-column indexes on tables like this.

Update June 2017

For reference – the online rebuild works in 12.1.0.2 and 12.2.0.1 – the fix had appeared by 11.2.0.4 – Oracle seems to have changed the mechanism to make the journal table redundant.  However, IOTs in an 8KB block will still report an ORA-01450 error if their primary key definition exceeds 3,215 bytes in total – even in 19c – though the same restriction doesn’t apply to any secondary indexes, which will not break until you pass the “ordinary” limit of 6,404 bytes (subject to the few extra bytes used in each entry).

Update Dec 2021

Nothing has changed in 21c (21.3): IOT primary keys are limited to 3,125 bytes in an 8KB block size; and the ‘hard limit’ of 3,800 bytes still applies for the 16KB block size.

[Further reading on rebuilding indexes]