Extent of oracle view in an MXD and in ArcGIS Server

1266
7
12-18-2017 07:31 AM
AlessandroValra
Occasional Contributor III

Hello,

I'm trying to load an oracle view in an MXD with Python command.

Oracle view has over 500,000 lines and python take log time to load new Layer based on Oracle View, because it calculate layer extent.

There's a way to set a default extent for the new layer to avoid extent recalculation?

This recalculation happens adding Layer, opening MXD and publishing MXD to ArcGIS Server.

I'm using Desktop and server 10.4.1.

Thanks in advance..

Tags (4)
0 Kudos
7 Replies
Asrujit_SenGupta
MVP Regular Contributor

In case you have a Desktop 10.5.x available, Registering the View with the Geodatabase, should take care of this.

0 Kudos
AlessandroValra
Occasional Contributor III

Done,

I've registered "my_view" with "owner user" and i connect to GDB with "reader user".

If I load in arcmap a registered view, layer is created without "Input Extent" Message, but my python script take 40 minutes to change data source of a Layer in MXD from GDB to SDE... (using lyr.replaceDataSource function).

I'm checking if Oracle log query extent...

Desktop is 10.5.1 and on the same machine there an ArcGIS Server 10.4.1

Any other suggestion?

Thanks

0 Kudos
AlessandroValra
Occasional Contributor III

An other comment.

I'm designing a workflow that will allow us to publish Oracle View, registered with SDE Database, to ArcGIS Server 10.5.1, using an Oracle viewer user, not data owner.

it works quite well, but:

  • if a view structure change (Field Names, format, ecc..), Arcgis says the scheme is out of date: is not possible to re-register th view, because appear already registered.
  • without a tool of "Unregister with Geodatabase", we need to clean SDE metadata by commends, this with SDE credentials:
    • DELETE FROM LAYERS WHERE TABLE_NAME = '<MYVIEW>';
      DELETE FROM TABLE_REGISTRY WHERE TABLE_NAME = '<MYVIEW>';
      DELETE FROM COLUMN_REGISTRY WHERE TABLE_NAME = '<MYVIEW>';
      DELETE FROM GDB_ITEMS WHERE name = '<OWNER>.<MYVIEW>';
      DELETE FROM GEOMETRY_COLUMNS WHERE F_TABLE_NAME = '<MYVIEW>';
  • At this time we need to run tool "register with Geodatabase" on changed view (or python automation), this with Oracle OWNER credentials.

This is complicated for 40 views... in a system production, I have not found a faster way...

Every idea or suggestion is appreciated.

Thanks in advance..

0 Kudos
Bart-JanSchoenmakers
New Contributor III

I can share with you our experience working with "spatial" database views in Oracle.

Initially we wanted to work with a view of about 3.5 million records, but realized this isn’t feasible. Instead of working with a view we created the table structure and refresh the data every day (TRUNCATE and INSERT). This table is registered in our database. We use it in Python scripts and several mapservices.

We never delete the table and only refresh the data. That's why there are no problems with the spatial extent of the data which we also had.  

0 Kudos
MichaelVolz
Esteemed Contributor

Bart-Jan:

You tried to use "spatial" database views in Oracle but they were not feasible.  Can you explain exactly what made these "spatial" views not feasible?

I am working with an Oracle "spatial" view in ArcMap 10.5.1 with about 220,000 records and I am finding performance issues when viewing the records in the table view and when performing queries resulting in large selection sets from this "spatial" view.

Any feedback you can provide is greatly appreciated.

0 Kudos
Bart-JanSchoenmakers
New Contributor III

Michael,

In the case described it was not feasible, there were too many records. Also on Oracle level the view was too big to use.

Right now we are using a few “spatial” database views, but they don’t have that many records (not more then 2000). For big views we now always work with dynamic tables which are created using the geographical dataset and the related table.

Since the data isn’t updated that frequently we don’t mind that each day the new changes are reflected. Hope this helps you.

Bart

0 Kudos
AlessandroValra
Occasional Contributor III

Hi Bart-Jan,

thanks for your response.

in my case I have occasional changes of structure (fields added, removed or renamed).
So Truncate and insert are not good, but the idea is interesting.

Even an "update registered view" could be an ArcGIS IDEA to be proposed to ESRI...

0 Kudos