extent computing while loading SDO_GEOMETRY from oracle DB

Question

It takes very long time while loading SDO_GEOMETRY based feature class from ORACLE to ArcMap. What are the possible reasons?
(I have already updated the spatial metadata (USER_SDO_GEOM_METADATA)and the feature class is properly spatially indexed).
Any suggestions! This is affecting our production environment in terms of performance. Thanks in advance.

Engr__RizwanAlvi · Answer

Thanks for the reply!

I knew it from day one, that ArcMap somehow, due to any reason, not able to take extent information from Oracle Spatial Metadata table but for me only it was strange that it at leasts, reads the table as a pre-requisite to show up the data (because if metadata table is not populated, it will not render the layer).

I hope, these things will be fixed in the coming releases soon.

MarcoBoeringa · Answer

Oracle 11g
ArcMap 10.2
Polygon features (NOT SDE feature class as its not registered with SDE)
No of features (100k)

My issue is when I add this spatial table or as a matter of fact any spatial table from Oracle into ArcMap it calculates the spatial extent on the fly (image attached). Also I have noticed larger the size of the added layer longer it takes ArcMap to calculate the spatial extent. I don't understand why ArcMap does not get this extent from USER_SDO_GEOM_METADATA which is already populated. Moreover the data loads quicker in QGIS.

Any suggestions?

While it may seem logical to you as an exclusive Oracle user, for ArcGIS to get this information from the USER_SDO_GEOM_METADATA table, it is not entirely so.

The functionality you are using to access a database table that is not registered with a geodatabase, is generic and targeted at all databases ArcGIS supports (SQL Server, PostGreSQL, DB2 etc.), some of which may not have a similar USER_SDO_GEOM_METADATA table storing a geometry table's extent as Oracle has.

While I can see ESRI adding functionality for this in a future release, it would require checking for database type, and than going after the proper "Metadata" type table storing the extent information. It would significantly complicate the code, and possibly make it more vulnerable to RDMBS changes and bugs, as changes to the underlying database between releases regarding the stored metadata, would start affecting the process of loading the datasets. With the current implementation, all ArcGIS needs to do is to extract the required data from the table containing geometries.

That said, I don't know what ESRI's plans are in this respect. It seems likely more "intelligent" functionality might be added in future releases. And of course, you can already base the extent on the Spatial Reference Extent as a rough quick shortcut, although this may not be a satisfactory "workaround" for some datasets.

Engr__RizwanAlvi · Answer

Oracle 11g
ArcMap 10.2
Polygon features (NOT SDE feature class as its not registered with SDE)
No of features (100k)

My issue is when I add this spatial table or as a matter of fact any spatial table from Oracle into ArcMap it calculates the spatial extent on the fly (image attached). Also I have noticed larger the size of the added layer longer it takes ArcMap to calculate the spatial extent. I don't understand why ArcMap does not get this extent from USER_SDO_GEOM_METADATA which is already populated. Moreover the data loads quicker in QGIS.

Any suggestions?

dataload.png