Forum Stats

  • 3,874,263 Users
  • 2,266,716 Discussions
  • 7,911,794 Comments

Discussions

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

Martin Preiss
Martin Preiss Member Posts: 2,381 Gold Trophy
edited Jan 11, 2016 6:05PM in Database Ideas - Ideas

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.

Martin Preissctriebvinaykumar2JagadekaramarkmevansManish ChaturvediPravin TakpireLothar FlatzFranck Pachotulohmann
11 votes

Active · Last Updated

Comments

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy

    Martin, as you are probably aware of there is a viable workaround already available: Create a non-unique index and add a unique constraint based on that non-unique index. Then you can disable the constraint and make the index unusable. load the data, rebuild the index and enable the constraint.

    This way no index needs to be dropped/re-created and the index maintenance can still be avoided. Bugs around this approach were fixed in 11.2.0.4 / 12.1, so there's not much that speaks against this.

    I can still understand your point of wishing to have this simpler model supported disabling / enabling the unique index, and of course a non-unique index might behave differently from a unique index, but for most cases this workaround should be fine.

    Randolf

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    Martin, as you are probably aware of there is a viable workaround already available: Create a non-unique index and add a unique constraint based on that non-unique index. Then you can disable the constraint and make the index unusable. load the data, rebuild the index and enable the constraint.

    This way no index needs to be dropped/re-created and the index maintenance can still be avoided. Bugs around this approach were fixed in 11.2.0.4 / 12.1, so there's not much that speaks against this.

    I can still understand your point of wishing to have this simpler model supported disabling / enabling the unique index, and of course a non-unique index might behave differently from a unique index, but for most cases this workaround should be fine.

    Randolf

    Randolf,

    you're right, of course. I remember the discussion on the fixing of one unpleasant bug that resulted in inconsistent data (and that you mentioned in your blog in 2008) in the thread ORA-01502 when skip_unusable_indexes=TRUE. And I agree: using non-unique indexes and unique constraints is a valid workaround. But still I think that it would be nice to use unique indexes if they are appropriate - especially since they reduce the number of necessary LIOs by one in comparison to a non-unique index (though I concede that this advantage is not decisive in most situations).

    Martin

  • Pravin Takpire
    Pravin Takpire Technical Services Manager Member Posts: 1,763 Gold Trophy

    What about duplicate data ? how you are supposed to take care of same.

    regards

    Pravin

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    the same way as with a unique constraint: you get an error on activation if there are duplicates (and the index creation fails of course). The behaviour is only relevant for bulk load operations (in data warehouses) and there your ETL(ELT) process should make sure that there are no duplicates.

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    the same way as with a unique constraint: you get an error on activation if there are duplicates (and the index creation fails of course). The behaviour is only relevant for bulk load operations (in data warehouses) and there your ETL(ELT) process should make sure that there are no duplicates.

    Even if the ETL process does not check for duplicates you can check after the mass insert. That will always be cheaper than checking for each row. I vote in favor.