Query Using Sdo_sam.Aggregates_for_geometry Runs Slower Than Expected
(Doc ID 1448722.1)
Last updated on APRIL 01, 2020
Applies to:
Oracle Spatial and Graph  Version 11.2.0.2 and laterInformation in this document applies to any platform.
Symptoms
Query using sdo_sam.aggregates_for_geometry runs slower than expectedIf we use only SUM the query runs fast:
select
sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'SUM', 'POP_B', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_B
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'SUM', 'POP_C', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_C
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'SUM', 'POP_F', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_F
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'SUM', 'HOUSEHLDSC', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as HOUSEHLDSC
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'SUM', 'POP_0_4_C', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_0_4_C
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'SUM', 'POP_5_9_C', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_5_9_C
from DUAL;
1.41sec
If we use AVG the query runs slow
select
sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'AVG', 'POP_B', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_B
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'AVG', 'POP_C', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_C
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'AVG', 'POP_F', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_F
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'AVG', 'HOUSEHLDSC', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as HOUSEHLDSC
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'AVG', 'POP_0_4_C', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_0_4_C
,sdo_sam.aggregates_for_geometry('COUNTIES', 'GEOMETRY', 'AVG', 'POP_5_9_C', sdo_geom.sdo_buffer (SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(74.0768,40.9189,NULL),NULL, NULL),10,0.005,'unit=mile, arc_tolerance=0.005')) as POP_5_9_C
from DUAL;
17.22sec
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 