ST_Geometry long operation error

[Jean-BernardGariépy]

Good afternoon everyone!

I�??ve been trying to run the following query on 3.8 million points for a week now. I ran it 4 times with always the same result: it runs for 8+ hours and then SQLPlus idles for ever and SQL Developer tells me the query ended with an error.

STATUS            Duration    SQL_ID            Session_id  Session_serial    instance_dop      CPU_Time    IO_Time     Start_Time  
DONE (ERROR)      67188       099q07k78mx7f     12          12535             1|0               3474.06     2.75        15-FEB-2013  11:06:15

create table ST_GC_SINGLEHOUSE as
select 
  a.idadr as featureid, 
  a.nocivq as housenumb, 
  trim(o.odogener || ' ' || o.odoparti) as prefixtype, 
  trim(o.odospeci) as streetname,
  trim(o.odoorien) as suffixdir, 
  trim(m.munnom) as city, 
  CP.CODPOS as ZIPCODE,
  sde.st_x(sde.st_transform(a.SHAPE,4269)) as LON, --epsg 4269 : NAD83
  sde.st_Y(sde.st_transform(a.shape,4269)) as lat,
  a.seqodo as streetid,
  a.shape as shape
from
  st_adresses a,
  aq_odonymes o,
  aq_municipalites m,
  aq_cp_adresses cp
where
  a.seqodo = o.seqodo
  and
  a.codemun = m.codemun
  and
  a.idadr = cp.idadr;

If you wonder why I'm using EPSG 4269 here see my previous post:
http://forums.arcgis.com/threads/77998-SDE.ST_TRANSFORM-ERROR?p=274138#post274138

In the above query, ST_Adresses in stored in ESRI ST_GEOMETRY. So I ran the same query on the same 3.8 millions points but this time using SDO_Adresses stored as SDO_GEOMETRY Oracle Spatial format.

All the other tables are the same and indexes are built on each single join field in both cases on top of having spatial indexes.

So the same query using SDO_GEOMETRY completes successfully in 42 min 40 sec.

 
create table SDO_GC_SINGLEHOUSE as
select 
  a.idadr as featureid, 
  a.nocivq as housenumb, 
  trim(o.odogener || ' ' || o.odoparti) as prefixtype, 
  trim(o.odospeci) as streetname,
  trim(o.odoorien) as suffixdir, 
  trim(m.munnom) as city, 
  CP.CODPOS as ZIPCODE,
  SDO_CS.TRANSFORM(A.SHAPE,4326).SDO_POINT.X  as LON,
  SDO_CS.TRANSFORM(A.SHAPE,4326).SDO_POINT.Y as lat,
  a.seqodo as streetid,
  a.shape as shape
from
  sdo_adresses a,
  aq_odonymes o,
  aq_municipalites m,
  aq_cp_adresses cp
where
  a.seqodo = o.seqodo
  and
  a.codemun = m.codemun
  and
  a.idadr = cp.idadr;

What�??s wrong with my ST_GEOMETRY query ?

[Jean-BernardGariépy]

I just removed ST_TRANSFORM and the query runs really fast !

The problem is behind ST_TRANSFORM. Perhaps its related to my previous post on ST_TRANSFORM as well !?
http://forums.arcgis.com/threads/77998-SDE.ST_TRANSFORM-ERROR?p=274138#post274138

create table ST_GC_SINGLEHOUSE as
select 
  a.idadr as featureid, 
  a.nocivq as housenumb, 
  trim(o.odogener || ' ' || o.odoparti) as prefixtype, 
  trim(o.odospeci) as streetname,
  trim(o.odoorien) as suffixdir, 
  trim(m.munnom) as city, 
  CP.CODPOS as ZIPCODE,
  sde.st_x(a.SHAPE) as LON,
  sde.st_Y(a.shape) as lat,
  a.seqodo as streetid,
  a.shape as shape
from
  st_adresses a,
  aq_odonymes o,
  aq_municipalites m,
  aq_cp_adresses cp
where
  a.seqodo = o.seqodo
  and
  a.codemun = m.codemun
  and
  a.idadr = cp.idadr;