Sharded Database Support by Oracle Spatial

Spatial supports the use of sharded database technology.

You create a shaded spatial table in the usual way, but specify CREATE SHARDED TABLE and appropriate partitioning. For example:

CREATE SHARDED TABLE departments
(  department_id  NUMBER(4),
   geojson        VARCHAR2(4000) CHECK (geojson IS JSON),
   geoloc         mdsys.sdo_geometry,
   CONSTRAINT dept_id_pk PRIMARY KEY(department_id)
)
PARTITION BY CONSISTENT HASH (department_id)
PARTITIONS AUTO
TABLESPACE SET ts1;

Create the special index on this table in the usual way. For example:

CREATE INDEX sidx on departments(geoloc) indextype is mdsys.spatial_index_v2 local;

However, the following index-related considerations apply:

• A global index is not supported on a sharded special table. The CREATE INDEX statement must include the keyword LOCAL.

• The spatial index on a sharded spatial table must be system-managed (INDEXTYPE=MDSYS.SPATIAL_INDEX_V2).

Functional spatial indexes are supported. For example:

CREATE INDEX sidx on departments(json_value(geojson, ‘$’, returning sdo_geometry)) indextype is mdsys.spatial_index_v2 local;

In addition, other requirements and guidelines for application development with sharded databases apply, including the following:

• The major advantage of a sharded database is the partitioning of data into semi-autonomous “regions” called shards. (In a spatial application, the regions might be cities or states.) Using shards means that applications running on a particular region get all the performance benefits of a single database without the interference from users in other shards.

  However, a potential downside is that applications where data moves from partition to partition may not work well in the sharded database environment. For example, applications like truck movement tracking are ideal if the truck remains within a single shard (region), but not ideal if the truck moves from region to region.

  You need to know “where” the data to be manipulated (through DML statements and queries) resides. In an application, accessing data in other shards must be done from the “coordinator” instance. In addition, it can be difficult to migrate data from one shard to another without performing a delete/insert operation because an update may not work as expected.

• Partitioned Management Operations (PMO), such as MERGE PARTITION and SPLIT PARTITION, are not supported by Spatial.

When Spatial performs operations on sharded spatial data, the following actions occur automatically as needed:

• A USER_SDO_GEOM_METADATA view is created on each shard separately.

• All DDL operations (such as CREATE INDEX and ALTER INDEX) are performed on the coordinator and they are automatically propagated to the shards.

• All queries that need to “cross-shards” are performed on the coordinator and are automatically aggregated, and shard-specific queries are performed on the individual shards.

For an overview of Oracle sharding, see Using Oracle Sharding.