Online Rebuild

I’ve commented in the past about the strange stories you can find on the internet about how Oracle works and how sometimes, no matter how daft those stories seem, there might be something behind them. Here’s one such remark I came across a little while ago – published in two or three places this year and describing Oracle 11g:

“An index that enforces referential integrity cannot be rebuilt online.”

There are a couple of problems with this statement – first, of course, indexes don’t enforce referential integrity, though they may help to enforce uniqueness, and the so-called “foreign key” index may avoid a locking issue related to referential integrity: that’s splitting hairs a little bit, though, and we can probably guess what the author means by “indexes enforcing referential integrity”.  (An example demonstrating the problem would have been useful, though – it would have saved me from writing this note, and it might save other people from jumping to the wrong conclusion and taking unsuitable action as a consequence.)

So here’s a simple test (run under 11.2.0.3):

rem
rem     Script:         ind_rebuild_defer.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2012
rem

create table parent (
        id      number	not null,
        n1      number,
        constraint par_pk primary key(id)
);

create table child (
        id_p    number	not null,
        id_c    number	not null,
        n1      number,
        constraint chi_pk primary key (id_p, id_c),
        constraint chi_par_fk foreign key (id_p) references parent(id)
);

insert into parent values(1,1);
insert into child values (1,1,1);
commit;

alter index par_pk rebuild online;
alter index chi_pk rebuild online;

If you run this test you’ll find that both indexes rebuild online with no problems. The original claim is clearly false. Almost inevitably it wasn’t backed up with an example, or even a detailed explanation, but surely there must have been some reason that made the author make a claim that can so easily be proved false. So what can we do to create a problem ?

How about continuing the test like this:

alter table child drop constraint chi_par_fk;

alter table child drop primary key;

alter table child add constraint chi_pk primary key(id_p, id_c) deferrable;

alter index chi_pk rebuild online;

Note that I’ve got rid of anything to do with referential integrity here – I’m just using the child table as the target for an index and a primary key constraint – but I’m making the constraint deferrable. The attempt to rebuild the index online fails with Oracle error: “ORA-08108: may not build or rebuild this type of index online”. This is a tiny bit interesting, because what “type” of index is it that we’re not allowed to rebuild online ?

The obvious answer to the last question is: maybe it’s something to do with a non-unique index supporting a unique constraint (which is what we have to have if we want a primary key (or unique) constraint to be deferrable. So let’s continue with the experiment:

alter table child drop primary key drop index;

alter table child add constraint chi_pk primary key(id_p, id_c) using index (
        create index chi_pk on child(id_p, id_c)
);

alter index chi_pk rebuild online;

This time the index rebuild is successful – so it doesn’t seem to be the lack of uniqueness (as such) that causes the problem.

Until further notice, I’m working on the hypothesis that the index protecting a deferrable primary key (or unique) constraint (which I’ve also tested – you can modify the code I’ve shown so far if you want to check for yourself) cannot be rebuilt with the online option. It’s possible that there’s some requirement relating to not null constraints in there as well, but I haven’t got around to looking at that yet.

Footnote: this is an investigation that isn’t complete – and that’s okay as far as I’m concerned because:

• I don’t yet NEED to complete it because I don’t have a problem to solve; but when I see an ORA-08108 on a production system I’ll remember this anomaly and check to see if it’s relevant and do any further investigation that’s needed at that point.
• I don’t intend to go around generalising a single observation into a misleading statement – I’ll let other people review the test case, consider my current hypothesis, and leave them with the opportunity to extend the investigation as they see fit.

Footnote 2: One reason why people make silly statements like this is that they read the manuals. But sometimes (a) they read out of date manuals and (b) they assume the manuals are correct, even when they make surprisingly unlikely statements. Here’s a quote from the SQL Reference manual for 9.0.1 under the alter index command:

 Restrictions:

• Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.
• You cannot specify ONLINE for a bitmap index or a cluster index.
• You cannot specify ONLINE when rebuilding an index that enforces a referential integrity constraint

Footnote 3: Notwithstanding any comments about bitmap indexes that you can find in the 9.0.1 manuals, I didn’t seem to hit a problem with the online option when I tried it with a bitmap index on a simple heap table in 10.2.0.3, and the trace file showed the standard journal IOT being created and dropped as the rebuild operation took place.

Update Dec 2022

Even in 19c (19.11.0.0) the attempt to rebuild the deferrable index online still results in Oracle error: ORA-08108: may not build or rebuild this type of index online