Query Using Sdo_sam.Aggregates_for_geometry Runs Slower Than Expected
Last updated on FEBRUARY 08, 2017
Applies to:
Oracle Spatial  Version: 11.2.0.2Information 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
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