I am having a performance issue updating a table in Oracle Spatial 11G while converting 63,000,000 points to WKT format. I also made a comparison with Postgres. Both Postgres and Oracle run in two virtual machines hosted by the same server.
Postgres: processors 12, ram 64GB
create table alldata(geom geometry(Point, 4326));
alter table alldata add column wkt4326 varchar(100);
update alldata set wkt4326 = St_AsText(geom) -- 25 minutes
Oracle 11G: processors 12, ram 40GB, pga + sga = 8GB maximum
create table alldata(geom sdo_geometry); -- e.g.: SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(aLon, aLat, null), null, null)
alter table alldata add (wkt4326 varchar(100));
-- **Did not complete in a hour...**
update alldatafiltered set wkt4326 = SDO_UTIL.TO_WKTGEOMETRY(geom);
Why is Postgres outperforming Oracle while updating tables? Which causes may affect Oracle spatial performances?
At the moment I am not leveraging any spatial index.
UPDATE
is a very expensive operation for a full table scan; you should be using aCREATE TABLE ... AS
construct. – Vince Jul 31 '17 at 18:37