SDO_GEOMETRY AND INNER JOINS

[IanMcCrorie]

Hi,

I am using ArcGIS 9.1 and equivalent ArcMap / ArcIMS clients.

Recently decided to change underlying storage of a table from SDEBinary to SDO_Geometry. Having done so it appears inner joins cause network i/o errors or, if using direct connect, memory reference errors.

Has anyone else had this issue and is there a solution?

So in ArcMap add SDO layer (correctly registered with SDE) and add join. If the default (outer join) is chosen the layer works, however if the 'Advanced' tab is used to select 'keep only matching records' the layer crashes with errors already mentioned.

We have been working with ArcSDE at v9.1 for many years using SDEBinary and SDO_Geometry layers and joins but this is the first instance where we have specifically chosen the inner join.

Any ideas appreciated.

Ian

[VinceAngelo]

ArcGIS 9.1 and ArcSDE 9.1 were retired from support a long time back (June 2010).
Since 9.1 was only supported with versions of Oracle which are likewise unsupported,
on operating systems which are also now unsupported, I would urge you to consider
the security risks associated with exposing old software to current virus threats.

How did you go about changing the storage?  Inner joins only occur on views -- Did you
recreate the views when you recreated the layers?  I never had problems with inner joins,
though their were plenty of problems with SDO_GEOMETRY performance through views
(optimizer issues at 9i and 10g).

- V

[IanMcCrorie]

Vince,

Upgrade not an option, client decision.

Is 'keep only matching records' from the join data form in ArcMap not setting an inner join? There are no views involved here.

The data was 'converted' from SDEBinary to SDO_Geometry by sdeexport / sdeimport with relevant steps of dropping layers and tables and use of keyword to change storage on import. That all works fine, as I mentioned in original post the layer performs perfectly with the default join type of 'keep all records', only the advanced 'keep only matching records' causes the errors.

Thanks for the reply

Ian

[VinceAngelo]

It's only an "inner join" if it's done through SQL (and an INNER JOIN would be better
described as "keep non-matching records").  ArcGIS does not do inner joins (the ArcSDE
API at that time didn't either -- it's only been added at 10.1), but it's entirely possible
there was a bug in ArcMap (or in the older Oracle DLLs) that was exposed by the
Advanced tab option.

Since it's long retired, your best hope is to try 9.1 SP2 and the 3 post-SP2 patches that
followed, or to go back to SDEBINARY (or SDELOB if you don't want to risk the LONG
RAW bugs in Oracle 10gR2, but LOBs are significantly slower  than LONG RAW [and
SDO_GEOMETRY uses LOB storage]).

- V

[IanMcCrorie]

Vince,

Lets not allow the symantics of 'LEFT' or 'INNER' join cloud the real issue.

When my storage was SDEBinary both join types available through the ArcMap interface worked, when storage is SDO_Geometry the 'keep only matching records' option blows up ArcMap.

The real questions are why this occurs, have others experienced this, is there a fix or workaround and is there a definite version of the software where this is fixed?

Regards

Ian

[VinceAngelo]

The ArcGIS join mechanism doesn't use native SQL (it can't, since it joins shapefiles
with Access and other non-SQL objects, as well as across RDBMSes without regard
to bridging software).  I'm willing to believe ArcGIS 9.1 was buggy, or that the Oracle
release was buggy (10.1.* and 10.2.0.1 were very buggy), but there isn't anything 
that can be done about it now (unless it was done by a patch released 5+ years ago,
when patches for 9.1 were last produced).

I doubt many here have access to 9.1 (I certainly don't, but I at least remember
using it).  Potential solutions are limited -- You could make sure you're using the
terminal service pack and patches for ArcGIS and ArcSDE 9.1 (and the last RDBMS
release that was documented as working with it, which would be 10.2.0.2 or 10.2.0.3),
or you can try to use a view to do a SQL join to avoid the bug, or work with your
local Esri marketing office to see if a modern ArcGIS release can reproduce the problem,
or you can go back to the SDEBINARY storage solution that was working previously
(to the detriment of  whatever made the change necessary).  Only the first two options
are really solutions (and the Oracle upgrade holds the most promise).

- V