Using Partitioned Spatial Indexes

You can create a partitioned spatial index on a partitioned table. This section describes usage considerations specific to Oracle Spatial. For a detailed explanation of

partitioned tables and partitioned indexes, see Oracle Database Administrator's 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 Section 5.1.1) 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. 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. See the description of the SDO_NN operator in Chapter 19 for more information.

5.1.3.1 Creating a Local Partitioned Spatial Index

If you want to create a local partitioned spatial index, Oracle recommends that you use the procedure in this section instead of using the PARALLEL keyword, to avoid having to start over if the creation of any partition’s index fails for any reason (for example, because the tablespace is full). Follow these steps:

1. Create a local spatial index and specify the UNUSABLE keyword. For example:

CREATE INDEX sp_idx ON my_table (location) INDEXTYPE IS mdsys.spatial_index

PARAMETERS (‘tablespace=tb_name work_tablespace=work_tb_name’) LOCAL UNUSABLE;

This statement executes quickly and creates metadata associated with the index.

2. Create scripts with ALTER INDEX REBUILD statements, but without the PARALLEL keyword. For example, if you have 100 partitions and 10 processors, create 10 scripts with 10 ALTER INDEX statements such as the following:

ALTER INDEX sp_idx REBUILD PARTITION ip1;

ALTER INDEX sp_idx REBUILD PARTITION ip2;

. . .

ALTER INDEX sp_idx REBUILD PARTITION ip10;

3. Run all the scripts at the same time, so that each processor works on the index for a single partition, but all the processors are busy working on their own set of ALTER INDEX statements.

If any of the ALTER INDEX statements fails, you do not need to rebuild any partitions for which the operation has successfully completed.