Query Using Sdo_sam.Aggregates_for_geometry Runs Slower Than Expected (Doc ID 1448722.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

Query using sdo_sam.aggregates_for_geometry runs slower than expected

If 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