Why oracle does not allow to disable indexes in oracle? I tried to search google but did not find quite satisfactory information. Please re-direct me if you know any link that give good information on this.
Also noticed that we can disable only function based indexes. But if we disable function based indexes then we are unable to carry any DML operation on that table. Why is that restriction?
create table id14(id number);
create index id14_idx on id14(ABS(id))
alter index id14_idx disable;
insert into id14 values(1);
Exception:[Error] Execution (8: 1): ORA-30554: function-based index SCOTT.ID14_IDX is disabled
My requirement is during bulk load operations from java application, I want to disable indexes before load and rebuild them after load. I don't want to drop and re-create as I want to retain definition intact without reading index definition from dictionary and creating it again.
I can mark index unusable but again it has it's own drawbacks and depends on parameter SKIP_UNUSABLE_INDEXES. It can fail in multiple scenarios.
Ex:
create table id14(id number);
create unique index id14_idx on id14(id)
alter index id14_idx unusable;
insert into id14 values(2);
[Error] Execution (8: 1): ORA-01502: index 'SCOTT.ID14_IDX' or partition of such index is in unusable state
In case of invisible indexes, It will still be validating index during insert operations so not of any help.
Another option that I'm thinking is to convert all unique indexes to unique constraints and disable them separately. Then mark rest other indexes as unusable and proceed with bulk load.
Please advice.