My Oracle Support Banner

Spatial Partition Pruning Does Not Work (Doc ID 789683.1)

Last updated on MARCH 16, 2009

Applies to:

Oracle Spatial - Version: 10.2.0.4 to 11.1.0.7
This problem can occur on any platform.

Symptoms

Facts:

- Partitioned table having the partition key on a few numeric columns.
- Local partitioned spatial index created on SDO_GEOMETRY column.
- Spatial partition pruning does not take place for a query using a Spatial operator in the where-clause.

SELECT UNIQID, OWNSHP, STRNDE, ENDNDE, GRADEL, SHAPE
FROM S_EDGE_304
WHERE SDO_FILTER(SHAPE,
  SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
  SDO_ORDINATE_ARRAY (-92.5,43.5,-92.6,43.6)), 'QUERYTYPE = WINDOW') = 'TRUE';

- Generated execution plan:

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3926 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 3926 | 1 (0)| 00:00:01 | 1 | 27 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| S_EDGE_304 | 1 | 3926 | 1 (0)| 00:00:01 | 1 | 27 |
|* 3 | DOMAIN INDEX | SEDGE_304_SPD_IDX | | | | | | |
------------------------------------------------------------------------------------------------------------

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
  Symptoms
  Cause
  Solution
  References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.