Sdo_Anyinteract Query On Partitioned Table With Local Spatial Index Returns Rows Only From The First Partition (Doc ID 1336160.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version 11.2.0.2 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 8-Apr-2013***

Symptoms

Query using SDO_ANYINTERACT on a Partitioned table with Partitioned (LOCAL) spatial index returns wrong results, it returns rows only from the first partition. This also reproduces using SDO_RELATE with mask ANYINTERACT.

** DOES NOT ** reproduce if the spatial index is global
** DOES NOT ** reproduce using SDO_FILTER
** DOES NOT ** reproduce with a non-partitioned table
** DOES NOT ** reproduce in versions 10.2.0.4.0, 10.2.0.5.0, 11.1.0.7.0, 11.2.0.1.0

Test case that shows the problem:

-- 1. Create partitioned table and insert some data
conn scott/tiger

drop table sdo_part_test;
CREATE TABLE sdo_part_test
(id NUMBER(5) primary key,
date_column date,
geometry mdsys.sdo_geometry
)
PARTITION BY RANGE(date_column)
(
PARTITION p1 VALUES LESS THAN('01-FEB-2011'),
PARTITION p2 VALUES LESS THAN('01-MAR-2011'),
PARTITION p3 VALUES LESS THAN('01-APR-2011'),
PARTITION p4 VALUES LESS THAN('01-MAY-2011'));

-- Row for Partition 1
insert into sdo_part_test
VALUES(1,'28-JAN-2011',MDSYS.SDO_GEOMETRY(2003,8307,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-25,20,-20,25)));

-- Row for Partition 2
insert into sdo_part_test
VALUES(2,'28-FEB-2011',MDSYS.SDO_GEOMETRY(2003,8307,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-15,20,-10,25)));

-- Row for Partition 3
insert into sdo_part_test
VALUES(3,'28-MAR-2011',MDSYS.SDO_GEOMETRY(2003,8307,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-25,10,-20,15)));

-- Row for Partition 4
insert into sdo_part_test
VALUES(4,'28-APR-2011',MDSYS.SDO_GEOMETRY(2003,8307,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-15,10,-10,15)));
commit;

-- 2. Insert metadata, validate geometries and create spatial index
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'SDO_PART_TEST',
'GEOMETRY',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05)
),
8307
);
commit;

DROP TABLE val_results;
CREATE TABLE val_results (sdo_rowid ROWID, result varchar2(1000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('SDO_PART_TEST','GEOMETRY','VAL_RESULTS');
col result format a40
SELECT * from val_results;

/*
SDO_ROWID RESULT
------------------ ----------------------------------------
Rows Processed <4>

1 row selected.
*/

exec dbms_stats.gather_table_stats('SCOTT','SDO_PART_TEST');
select partition_name, num_rows from user_tab_partitions;

/*
PPARTITION_NAME                  NUM_ROWS
------------------------------ ----------
P3                                      1
P4                                      1
P1                                      1
P2                                      1

4 rows selected.
*/

drop index sdo_part_sidx force;
CREATE INDEX sdo_part_sidx ON sdo_part_test(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
LOCAL
(PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4);

-- 3. Execute the select statement

SELECT sp.id FROM sdo_part_test sp
WHERE SDO_ANYINTERACT(sp.geometry,
SDO_GEOMETRY(2003, 8307, NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-30,5,-5,30))) = 'TRUE';

/*
        ID
----------
         1

1 row selected.
*/



The result should be:

        ID
----------
         1
         2
         3
         4

4 rows selected.


The geometries are shown here:

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