Skip to Main Content

Option to set a unique index unusable prior to mass data inserts

Martin PreissMar 18 2015 — edited Jan 11 2016

In data warehouses it is not unusual to set indexes unusable before a load operation and rebuild them after the load - this approach may reduce the maintenance overhead for the indexes significantly. But for a unique b*tree index this is not an option:

drop table t;

create table t( id number);

create unique index t_idx on t(id);

alter index t_idx unusable;

insert into t

select rownum id

  from dual

connect by level < 10;    


FEHLER in Zeile 1:

ORA-01502: Index 'C##TEST.T_IDX' oder Partition dieses Index in nicht brauchbarem Zustand

--> index ... or partition of such index is in usable state.

With a nonunique index the script works without a problem. The result of this behaviour is frequently that such indexes are dropped before and recreated after the data integration - and that's not a satisfying workaround.

Post Details
Added on Mar 18 2015