Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

12.1 - pruning doesn't work for spatial index

Maciej PłazaFeb 7 2024

Hi!

I have a big problem with partitioned table and spatial index in version 12.1.

I have a table TEST:

create table TEST 
( 
ID NUMBER(38),
SDO_GEOM MDSYS.SDO_GEOMETRY, -- yes, it's field name ;)
x_value NUMBER 
) PARTITION BY RANGE (x_value) 
( 
PARTITION P1 VALUES LESS THAN (340000), 
PARTITION P2 VALUES LESS THAN (450000), 
PARTITION P3 VALUES LESS THAN (520000), 
PARTITION P4 VALUES LESS THAN (570000), 
PARTITION P5 VALUES LESS THAN (610000), 
PARTITION P6 VALUES LESS THAN (650000), 
PARTITION P7 VALUES LESS THAN (690000), 
PARTITION P8 VALUES LESS THAN (730000), 
PARTITION P9 VALUES LESS THAN (770000), 
PARTITION P10 VALUES LESS THAN (MAXVALUE) 
) 
/

INSERT INTO USER_SDO_GEOM_METADATA 
VALUES ('TEST', 'SDO_GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 150000.0, 900000.0, 0.001), 
MDSYS.SDO_DIM_ELEMENT('Y', 130000.0, 800000.0, 0.001)), 
NULL); 
COMMIT; 

CREATE INDEX IDX_TEST ON TEST (SDO_GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX LOCAL;

populated with data from another table:

INSERT INTO TEST 
SELECT t.ID 
t.SDO_GEOM,
(SDO_GEOM.SDO_POINTONSURFACE(t.SDO_GEOM, 0.005)).SDO_POINT.x 
FROM TEST2 t;

In the TEST table there is ~400 rows (I want put there ~40 million rows).

The problem is that, databse checks all partitions during queries:

EXPLAIN PLAN FOR 
SELECT * 
FROM TEST t 
WHERE SDO_ANYINTERACT(t.SDO_GEOM, 
SDO_UTIL.FROM_WKTGEOMETRY('POLYGON ((520978.81 296870.09, 520976.03 296875.81, 520829.76 296662.73, 520833.28 296658.52, 520978.81 296870.09))')) = 'TRUE' ; 

select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

Plan hash value: 2384812818 

-------------------------------------------------------------------- 
| Id | Operation | Name | 
-------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 
| 1 | PARTITION RANGE ALL | | 
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 
| 3 | DOMAIN INDEX (SEL: 0.100000 %) | IDX_TEST | 
--------------------------------------------------------------------

I tried to partition different tables with different rows number and execution plan always shows PARTITION RANGE ALL.

What I'm doing wrong?

Thanks,
Maciej.

Comments
Post Details
Added on Feb 7 2024
0 comments
107 views