5.1.3.1 Creating a Local Partitioned Spatial Index

If you want to create a local partitioned spatial index, Oracle recommends that you use the procedure in this section instead of using the PARALLEL keyword, to avoid having to start over if the creation of any partition's index fails for any reason (for example, because the tablespace is full). Follow these steps:

Create a local spatial index and specify the UNUSABLE keyword. For example:

CREATE INDEX sp_idx ON my_table (___location) 
  INDEXTYPE IS mdsys.spatial_index 
  PARAMETERS ('tablespace=tb_name work_tablespace=work_tb_name') 
  LOCAL UNUSABLE;

This statement executes quickly and creates metadata associated with the index.

Create scripts with ALTER INDEX REBUILD statements, but without the PARALLEL keyword. For example, if you have 100 partitions and 10 processors, create 10 scripts with 10 ALTER INDEX statements such as the following:
```
ALTER INDEX sp_idx REBUILD PARTITION ip1;
ALTER INDEX sp_idx REBUILD PARTITION ip2;
. . .
ALTER INDEX sp_idx REBUILD PARTITION ip10;
```
Run all the scripts at the same time, so that each processor works on the index for a single partition, but all the processors are busy working on their own set of ALTER INDEX statements.

If any of the ALTER INDEX statements fails, you do not need to rebuild any partitions for which the operation has successfully completed.