extent computing while loading SDO_GEOMETRY from oracle DB

855
6
02-13-2014 01:48 AM
Engr__RizwanAlvi
New Contributor
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.
0 Kudos
6 Replies
VinceAngelo
Esri Esteemed Contributor
You should never have the indexes built while attempting bulk-loading.  Drop the
indexes, finish loading, *then* rebuild the indexes.

- V
0 Kudos
Engr__RizwanAlvi
New Contributor
Did'nt help 😞

The problem is that ArcMap calculates the layer extent at loading time, no matter it is in metadata or not.

Any thought!?
0 Kudos
MarcoBoeringa
MVP Regular Contributor
It would be helpful if you specified both your hard- and software environment including exact versions of software used, and statistics and data about the layer you are trying to access, like feature type (point, line, polygon), how many features (10k, 100M??) etc, and what you mean with trying to "load" the data in ArcMap: are you trying to load all of the data at full extent (which effectively renders the use of a spatial index useless), or are you properly accessing the data through well defined min/max display scales (properly limiting the number of features needed to be "loaded" to ArcMap at any given extent and point in time)
0 Kudos
Engr__RizwanAlvi
New Contributor
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?
0 Kudos
MarcoBoeringa
MVP Regular Contributor
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.
0 Kudos
Engr__RizwanAlvi
New Contributor
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.
0 Kudos