Am I missing something? I thought that hint are ignored if they are not relevant or erroneous; ORA-01502 error will be raised during data loads while the index marked as unusable status that supports constraints/keys.
Something like this.
demo@ORA11G> create table t(x int,y varchar2(30),z date,object_id int ,
2 constraint t_pk primary key(x) )
3 partition by hash(object_id)
4 ( partition p1, partition p2 );
Table created.
demo@ORA11G>
demo@ORA11G> alter index t_pk unusable;
Index altered.
demo@ORA11G>
demo@ORA11G> insert into t(x,y,z,object_id)
2 select id,object_name,created,id
3 from big_table;
insert into t(x,y,z,object_id)
*
ERROR at line 1:
ORA-01502: index 'DEMO.T_PK' or partition of such index is in unusable state
rather than setting the index unusable and doing the data loads, you could disable the constraint, do data load and later enable the constraint.
demo@ORA11G> alter index t_pk rebuild;
Index altered.
demo@ORA11G> alter table t disable constraint t_pk;
Table altered.
demo@ORA11G> insert into t(x,y,z,object_id)
2 select id,object_name,created,id
3 from big_table;
1000000 rows created.
demo@ORA11G> alter table t enable constraint t_pk;
Table altered.
demo@ORA11G>
BTW, do we really need to set the index to unusable state during data loads? why not just do a direct path loads? - since direct path loads use the blocks above the high water mark, it builds the index for the newly loaded data and nicely merge them with the existing index as part of the data loads.