Creating a local partitioned index in parallel in Oracle

If you need to create a non-unique local partitioned index in partitioned table, Oracle doesn't let you use as simple script as this.

create index IX_LOCAL_PART_IND on BIG_PART_TABLE online nologging
local parallel 32

Up until Oracle 12c, they have a limit where the degree of parallelism would be limiedt to the number of partitions in the table. If you have 12 partitions, above statement will do dop of 12 instead of 32. But rest assured, there's a way to circumvent this limitation.

You can get around this limitation by creating UNUSABLE INVISIBLE local partitioned index and rebuild index partitions in parallel

create index IX_LOCAL_PART_IND on BIG_PART_TABLE nologging parallel 32 
unusable invisible local

This way you are creating just index metadata, not actually building the index at all. It's not usable or visible for now so it should NOT impact anything in production database. At this point let's check index partition status.

SELECT status, visibility 
FROM dba_indexes 
WHERE index_name = 'IX_LOCAL_PART_IND ';

SELECT index_name, partition_name, status 
FROM dba_ind_partitions 
WHERE index_name = IX_LOCAL_PART_IND ';

Rest of the process is simple and easy.

alter index IX_LOCAL_PART_IND rebuild partition PART1 nologging 
online parallel 32;
alter index IX_LOCAL_PART_IND rebuild partition PART2 nologging 
online parallel 32;
alter index IX_LOCAL_PART_IND rebuild partition PART3 nologging 
online parallel 32;
alter index IX_LOCAL_PART_IND rebuild partition MAXVALUE nologging 
online parallel 32;

begin
  dbms_stats.gather_index_stats(ownname => 'SCOTT',  
                                indname  => 'IX_LOCAL_PART_IND ' ,  
                                degree => 32);
end;
/

alter index IX_LOCAL_PART_IND logging noparallel;
alter index IX_LOCAL_PART_IND visible;

Check the index partitions again. It would go USUABLE as rebuild succeeds. If you don't mind building it without online option, you could make use of DBMS_PCLXUTIL. It internally uses DBMS_JOB to submit partition rebuilding jobs in parallel.