Frequently asked question
What type of st_spatial_index should be created for a partitioned table in Oracle: global or local?
Answer
Determining what type of st_spatial_index to create on a partitioned table, completely depends on how the table is accessed with spatial SQL statements.If the partitioned attribute key is not included in the predicate with the spatial operator, a global index should be created.
Note:
A global index maintains the indexed attribute values for every partition in one index.
If the partitioned attribute key is included in the predicate with the spatial operator, a local index should be created.
Note:
A local index maintains the indexed attribute values for only the attributes in each partition, resulting in an index for each partition.
Reason:
When using local indexes and the partitioned attribute key is not included, in the predicate list as a filter, the access path for the query must visit each partition's local index to satisfy the spatial operator filter. The cost of searching each partition's local index quickly becomes more resource expensive and slower compared to using a global index.
The following SQL statement demonstrates a query using two predicates: the event_date attribute, which is the range partition key for the event_location table, and the operator st_envintersects. If the spatial index is a local index, then the optimizer is able to leverage partition pruning by only visiting the partitions where the value for event_date equals '12-04-2007', and apply the spatial operator to the partition's local spatial index.
Code:
SQL> SELECT id, cost, st_astext(shape)
1 FROM event_location
2 WHERE event_date = TO_DATE('12-04-2007','MM-DD-YYYY')
3 AND st_envintersects(shape,10,20,15,25) = 1;
Article ID:000009725
Software:
- Legacy Products
Get help from ArcGIS experts
Download the Esri Support App
Related Information
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback