You can create a partitioned spatial index on a partitioned table. This section describes usage considerations specific to Oracle Spatial and Graph. For a detailed explanation of partitioned tables and partitioned indexes, see Oracle Database VLDB and Partitioning Guide.
A partitioned spatial index can provide the following benefits:
Reduced response times for long-running queries, because partitioning reduces disk I/O operations
Reduced response times for concurrent queries, because I/O operations run concurrently on each partition
Easier index maintenance, because of partition-level create and rebuild operations
Indexes on partitions can be rebuilt without affecting the queries on other partitions, and storage parameters for each local index can be changed independent of other partitions.
Parallel query on multiple partition searching
The degree of parallelism is the value from the DEGREE column in the row for the index in the USER_INDEXES view (that is, the value specified or defaulted for the PARALLEL keyword with the CREATE INDEX, ALTER INDEX, or ALTER INDEX REBUILD statement).
Improved query processing in multiprocessor system environments
In a multiprocessor system environment, if a spatial operator is invoked on a table with partitioned spatial index and if multiple partitions are involved in the query, multiple processors can be used to evaluate the query. The number of processors used is determined by the degree of parallelism and the number of partitions used in evaluating the query.
The following restrictions apply to spatial index partitioning:
The partition key for spatial tables must be a scalar value, and must not be a spatial column.
Only range partitioning is supported on the underlying table. All other kinds of partitioning are not currently supported for partitioned spatial indexes.
To create a partitioned spatial index, you must specify the LOCAL keyword. (If you do not specify the LOCAL keyword, a nonpartitioned spatial index is created on the data in all table partitions.) The following example creates a partitioned spatial index:
CREATE INDEX counties_idx ON counties(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX LOCAL;
In this example, the default values are used for the number and placement of index partitions, namely:
Index partitioning is based on the underlying table partitioning. For each table partition, a corresponding index partition is created.
Each index partition is placed in the default tablespace.
If you do specify parameters for individual partitions, the following considerations apply:
The storage characteristics for each partition can be the same or different for each partition. If they are different, it may enable parallel I/O (if the tablespaces are on different disks) and may improve performance.
The sdo_indx_dims value must be the same for all partitions.
The layer_gtype parameter value (see Constraining Data to a Geometry Type) used for each partition may be different.
To override the default partitioning values, use a CREATE INDEX statement with the following general format:
CREATE INDEX <indexname> ON <table>(<column>)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
[PARAMETERS ('<spatial-params>, <storage-params>')] LOCAL
[( PARTITION <index_partition>
PARAMETERS ('<spatial-params>, <storage-params>')
[, PARTITION <index_partition>
PARAMETERS ('<spatial-params>, <storage-params>')]
)]
Queries can operate on partitioned tables to perform the query on only one partition. For example:
SELECT * FROM counties PARTITION(p1)
WHERE ...<some-spatial-predicate>;
Querying on a selected partition may speed up the query and also improve overall throughput when multiple queries operate on different partitions concurrently.
When queries use a partitioned spatial index, the semantics (meaning or behavior) of spatial operators and functions is the same with partitioned and nonpartitioned indexes, except in the case of SDO_NN (nearest neighbor). With SDO_NN, the requested number of geometries is returned for each partition that is affected by the query. (See the description of the SDO_NN operator in Spatial Operators for more information.)
For example, if you request the 5 closest restaurants to a point and the spatial index has 4 partitions, SDO_NN returns up to 20 (5*4) geometries. In this case, you must use the ROWNUM pseudocolumn (here, WHERE ROWNUM <=5) to return the 5 closest restaurants, and the ORDER BY clause to sort the results by distance in miles. Example 5-1 returns the 5 nearest restaurants from a partitioned spatial index.
Note:
SDO_NN Examplesfor more examples of using the SDO_NN operator.
For a cross-schema query when a table has a partitioned spatial index, the user must be granted SELECT or READ privilege on both the spatial table and the index table (MDRT_xxx) for the spatial index that was created on the spatial table. For more information and an example, see "Cross-Schema Invocation of SDO_JOIN" in the Usage Notes for the SDO_JOIN operator in Spatial Operators .
Example 5-1 SDO_NN Query with Partitioned Spatial Index
SELECT * FROM
(
SELECT r.name, r.___location, SDO_NN_DISTANCE(1) distance_in_miles
FROM restaurants_part_table r
WHERE SDO_NN(r.___location,
MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(-110,35,Null),Null,Null),
'SDO_NUM_RES=5 distance=2 unit=MILE', 1) = 'TRUE'
ORDER BY distance_in_miles
)
WHERE ROWNUM<=5;