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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms