topic Re: 76 million rows...SDE st_geometry vs Oracle sdo_geometry... in ArcGIS Enterprise Questions

76 million rows...SDE st_geometry vs Oracle sdo_geometry...

JohnLewis — Sun, 12 Dec 2021 00:10:49 GMT

Does anyone know of why the sde.st_geometry functions take so long to run? I need convert 76 million lat/lon's to shape objects.

We're running on a Windows 2008 server using Oracle 11gR2 64bit with 64G ram and 24 core processors.

Following is the code I used to accomplish this and the results of both the SDE methods and the Oracle methods.

--Oracle sdo_geomtry�?�

create table sample_points (
RECORDID,
SHAPE) nologging as
  (select  /*+ NO_PARALLEL_INDEX(jl) */
   tab_row_id,
sdo_geometry(2001, null,
sdo_point_type(longitude, latitude, null)
null,null)
from jl_points_all jl);

Table created.

Elapsed: 00:01:24.29

--ESRI st_geometry�?�

create table sample_points (
   RECORDID,
   SHAPE) nologging as
   (select  /*+ NO_PARALLEL_INDEX(jl) */
           tab_row_id,
           sde.st_geometry (longitude, latitude, null, null,0)
   from jl_points_all jl);

--Still running after 11 hours �?? only 4% of the datablocks read from the source table.

Does anyone know of faster methods using the SDE functions?

Thanks in advance

John

Re: 76 million rows...SDE st_geometry vs Oracle sdo_geometry...

NidhinKarthikeyan — Wed, 25 Sep 2013 09:35:24 GMT

John,

You can post the question on Geodatabse and ArcSDE.

Re: 76 million rows...SDE st_geometry vs Oracle sdo_geometry...

VinceAngelo — Fri, 27 Sep 2013 15:22:57 GMT

I regularly load 8 million row GeoNames tables in minutes using the 'asc2sde' utility,
and have no difficulty copying sde.ST_GEOMETRY tables one to another, so the issue
may be something in your environment, or something specifically to do with the
sde. ST_GEOMETRY constructor. Have you tried using sde.ST_POINT, much like
the SDO_POINT you're using?

You may be introducing inefficiencies by not specifying a coordinate reference (best
practice is to *always* define a non-zero SRID), but a call to Tech Support is the
best way to find out exactly what is happening, and what can be done to make
it better.

- V