8

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.

|improve this question
  • 1
    What's the purpose of this exercise? Performance is only really a concern for a process which will be repeated, and I can't see a reason to repeat this. UPDATE is a very expensive operation for a full table scan; you should be using a CREATE TABLE ... AS construct. – Vince Jul 31 '17 at 18:37
  • I do not see anything GIS specific here. Maybe TO_WKTGEOMETRY is slow, perhaps your Oracle creates and commits too small transactions, or there is something else that could be optimized. – user30184 Aug 1 '17 at 3:47
  • Using Oracle you have to differ between Oracle Locator, which is in standard editions and the Spatial Cardridge of Oracle which is significanly faster, but very expensive. Many methods like SDO_UTIL.TO_WKTGEOMETRY are implemented in Java, Thats why they are not available in Oracles Express edition (without Java). Without the Spatial Cardridge these methods seem to be interpreted (slow). – wumpz Aug 1 '17 at 6:00
7

Oracle Spatial and Postgis both have their own pros and cons. While dealing with the spatial data Postgis always outperform Oracle Spatial. I have been working for last 2 year on Oracle Spatial and i recently switched to PostGIS, and i saw a huge performance difference in both of them.

The reason why it perform much faster is because spatial data parsing. In many case studies it has been derived that PostGIS is 300%-400% faster than Oracle Spatial.

PostGIS uses GEOS(Geometry Engine - Open Source) for geometry parsing while Oracle spatial has its own geometry engine which has very low parsing than GEOS.

I have found this case study to be very useful for performance comparison between PostGIS and Oracle Spatial.

In my personal opinion and experience Open source always outcomes Proprietary in Performance.

|improve this answer

Some of the information contained in this post requires additional references. Please edit to add citations to reliable sources that support the assertions made here. Unsourced material may be disputed or deleted.

  • 1
    PostGIS does not use GEOS for parsing geometries. That is all native PostGIS. GEOS is used for some of the geomtry operations in PostGIS – Nicklas Avén Oct 29 '17 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.