Spatial Table Partition

Partitioning is an Oracle Database feature in which a single table is decomposed into multiple smaller tables called partitions.Range partitioning is the supported partitioning scheme for tables with spatial indexes. 

Range Partition Table 

CREATE TABLE tab_part
(geom MDSYS.SDO_GEOMETRY,
 id   NUMBER(10),
 mod  NUMBER(2)

)
PARTITION BY RANGE (mod)
(PARTITION p1_tab VALUES LESS THAN (3),
 PARTITION p2_tab VALUES LESS THAN (5),
 PARTITION p3_tab VALUES LESS THAN (7)
);

We should have entry in user_sdo_geom_metadata before  creating spatial Index.

INSERT INTO user_sdo_geom_metadata
SELECT 'TAB_PART' TABLE_NAME, COLUMN_NAME, DIMINFO, SRID 

FROM   user_sdo_geom_metadata 

WHERE TABLE_NAME= 'TAB1'      -- TAB1 have same entry in user_sdo_geom_metadata

AND COLUMN_NAME ='geom';

Create the partitioned spatial index specifying the keyword UNUSABLE

CREATE INDEX tab_part_sidx ON TAP_PART(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
LOCAL (
PARTITION P1 ,
PARTITION P2 ,
PARTITION P3 )
UNUSABLE;

If the index is created UNUSABLE, and each
partition is rebuilt separately, then if one or more partition’s indexes fail to build correctly only those
partition’s indexes will need to be rebuilt.

After creating the index UNUSABLE, each partition’s spatial index can then be created using the
ALTER INDEX … REBUILD PARTITION command.

alter index TAB_PART_SIDX rebuild partition p1;
alter index TAB_PART_SIDX rebuild partition p2;
alter index TAB_PART_SIDX rebuild partition p3;