Last updated on FEBRUARY 08, 2017
Applies to:Oracle Spatial and Graph - Version 184.108.40.206 to 220.127.116.11 [Release 11.2]
Information in this document applies to any platform.
Query SDO_RELATE Spatial Function Is Slow at 18.104.22.168
Upgrade to Oracle 11gR2 results in queries spatial running much slower in compared to Oracle 10g.
Queries with sod_relate function are very slow in production environment taking more than 15 minutes.
There are around 1.5 million records.
Index quadtree takes 2 seconds at 10g and 15 minutees at 11gr2.
CREATE INDEX T1E12_01_SI ON T1E12_CONTR_POLGN
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('tablespace=ARP_EDI_S07_U16M initial=16m next=16m pctincrease=0 minextents=1 maxextents=UNLIMITED sdo_commit_interval=10000 sdo_level=6 sdo_fanout=60 sdo_max_memory=25000000 sdo_rtr_pctfree=0 ')
FROM t1e12_contr_polgn t1e12
WHERE t1e12.co_echel = 20
AND sdo_relate (t1e12.dg_geomt,
mdsys.sdo_elem_info_array (1, 1003, 3),
'mask=anyinteract querytype=window') = 'TRUE';
The platform was also changed from Linux to SPARC
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms