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;
