Creating an Index on a Static Table Referenced by an Active Table

We have a parent-child pair of tables with a foreign key constraint between them, and we need to add an index to the parent table, while the application is active.

The parent table is static during the creation of the index (no DML on it), but lots of DML statements are done on the child table. The parent table is relatively big, so the index creation takes a relatively significant time.

For example, a CATALOG_ITEMS table contains many records that do not change, and an ORDERS table referencing the CATALOG_ITEMS table (each order is for one specific catalog item). Orders are inserted, updated and deleted all the time.

create table catalog_items (
       catalog_item_id number        not null,
       name            varchar2(100) not null,
       ... 
       constraint catalog_items_pk primary key (catalog_item_id)
);

create table orders (
       order_id        number not null,
       catalog_item_id number not null,
       ... 
       constraint orders_pk primary key (order_id),
       constraint orders_fk_catalog_items foreign key (catalog_item_id)
                  references catalog_items (catalog_item_id)
);

create index orders_catalog_item_id_idx on orders (catalog_item_id);

We need to create a new index on CATALOG_ITEMS. The CREATE INDEX operation locks the CATALOG_ITEMS table in SHARE mode. It means that DML statements on the CATALOG_ITEMS table will be blocked now (which is fine in our use case, as there are no DML statements on this table anyway). But it also means that DML statements (INSERT, DELETE, and UPDATE of the referencing column) on the ORDERS table will be blocked for the whole duration of the index creation (which is very bad in our use case). This is due to the foreign key from ORDERS to CATALOG_ITEMS – a ROW EXCLUSIVE lock on CATALOG_ITEMS is required in order to force the foreign key constraint.

In the Enterprise Edition we could use ONLINE index creation, but not in the Standard Edition.

A simple solution is to temporarily disable the foreign key constraint for the duration of the index creation:

-- wait up to 30 seconds to allow the subsequent "ALTER TABLE" statements
-- getting the (very short, SHARE) locks they need, without getting the
-- ORA-00054 "resource busy..." error
-- (this feature was introduced in 11g)
alter session set ddl_lock_timeout=30;

-- disable the foreign key
alter table orders disable constraint orders_fk_catalog_items;

-- create the new index
create index catalog_items_idx1 on catalog_items(...);

Note that by doing so we allow entering orders into the database that reference non-existing catalog items. Think carefully if you are willing to take this risk.

As soon as the index creation is done, we’d like to re-enable the constraint. Once again, we’d like to minimize locking and blocking, so first we’ll enable the constraint with the “novalidate” option (which enables the foreign key validation for statements from now on, but does not validate it for existing records, and therefore it’s a very fast operation):

alter table orders enable novalidate constraint orders_fk_catalog_items;

And finally, we’ll enable the constraint with the “validate” option, to make sure no violation of the constraint has occurred since we disabled it. Validating an “enabled novalidated” constraint is an online operation – DML statements on ORDERS will not be blocked:

alter table orders enable validate constraint orders_fk_catalog_items;

Use case taken from a site running: Oracle 11g Release 2 Standard Edition