SQL Loader - SKIP_INDEX_MAINTENANCE

SKP_INDEX_MAINTENANCE option stops the index maintenance for DIRECT path loads. For conventional load, this option is not valid.
If the data is loaded into only few index partitions then only those index partitions which are affected is made UNUSABLE. If there are global indexes, then they are made UNUSABLE too.


To identify the global indexes which are UNUSABLE;
> select index_name , table_name from all_indexes
where status = 'UNUSABLE'

To identify the local partitioned indexes which are UNUSABLE;
>select index_name, partition_name, status from all_ind_partitions
where status = 'UNUSABLE';

To rebuild the indexes
--To build the global indexes
>alter index ixpk_emp_hist rebuild nologging parallel 12;

--To build the index partition
>alter index ix2_emp_hist rebuild partition p2009 parallel 12;

-- To build all the unusable local indexes for a partition.
> alter table emp_hist modify partition p2010 rebuild unusable local indexes;
-- u cannot specify PARALLEL in the sql. it keeps building one index at a time.