创建空间数据分区表及分区索引步骤（oracle 10g 且关键字非字符型）

 1.
create table yellow_pages
(id number,
name varchar2(50),
category number,
location SDO_GEOMETRY)
partition by range (category)
(partition p1 values less than (2),
partition p2 values less than (3),
partition p3 values less than (4),
partition p4 values less than (maxvalue));

 

2.
insert into USER_SDO_GEOM_METADATA values('yellow_pages', 'location' ,MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X', -2147483648, 2147483647, 0.000005), MDSYS.SDO_DIM_ELEMENT('Y',  -2147483648, 2147483647, 0.000005)), NULL );

3.
create index yp_part_sidx on yellow_pages (location)
indextype is mdsys.spatial_index
parameters ('LAYER_GTYPE=POINT')
local
(partition ip1 parameters('tablespace=users1'),
partition ip2 parameters ('tablespace=users2'),
partition ip3 parameters ('tablespace=users3'),
partition ip4 parameters ('tablespace=users4'));

 

Oracle在空间数据方面主要是采用了分区表的形式来平衡大表的I/O的，哪怕是上百万的居民地边线，一旦使用了分区表，即可有较好的响应时间。