Is anyone successfully using 1:M views in ArcSDE 9.3.1 and Oracle 10g (10.2.0.2)?
I have been providing several 1:M views to users for years. They stopped working correctly at SDE 9.2 and now work inconsistently at SDE 9.3.1. By that I mean that the 1:M relationships show quite plainly as stacked polygons in ArcCatalog, but not in ArcMap. And also that the 1:M views export incorrectly, i.e a user has 1500 features selected on the view and exports his selection to a shapefile only 500 features export.
As an example, I have a 1:M view where the '1' part of the view is a county feature class, and the 'M' is a table which has one or more rows per county. In the view, I'm using an OBJECTID field from the M table in order to have a unique OBJECTID for the view. In fact, all fields are coming from the 'M' table except for SHAPE which is coming from the '1' feature class.
I've eliminated the following as contributors to the problem:
Oracle client version
BLOB vs. LONGRAW geometry storage
outer join (+) added to 'M' side of business table view post sde command line creation
OBJECTID for view coming from '1' feature class (results in duplicate OBJECTID)
Running traces in the database for views that mysteriously work versus views that do not tells me that an additional select on SHAPE.FID from the F view (see below) seems to make the difference. But, I have no idea how ArcGIS generates this SQL from the sde view I created. Does ArcGIS Desktop generate the SQL or does ArcSDE?
You can probably tell from the bind variables at the end of the SQL, but the below was captured from a DB trace while I was in ArcMap and used the identify tool on a county that has 2 records in the many table.
SELECT /*+ LEADING INDEX(S_ S3060_IX1) INDEX(SHAPE) INDEX(COUNTIES_MAP_STATUS_VW A3060_IX1) */
objectid,
st_name,
cnty_name,
stcnty,
agmt_type,
incomplete_legal,
arcmap_requested,
update_requested,
needs_review,
polygen_error_general,
polygen_error_no_grid,
polygen_error_no_legal,
polygen_error_no_lot,
shape,
shape.area,
shape.len,
shape.fid,
s_.eminx,
s_.eminy,
s_.emaxx,
s_.emaxy,
shape.fid,
shape.numofpts,
shape.entity,
shape.points,
shape.ROWID
FROM (SELECT /*+ INDEX(SP_ S3060_IX1) */
DISTINCT
sp_fid, eminx, eminy, emaxx, emaxy
FROM lease.s3166 sp_
WHERE sp_.gx >= :1
AND sp_.gx <= :2
AND sp_.gy >= :3
AND sp_.gy <= :4
AND sp_.eminx <= :5
AND sp_.eminy <= :6
AND sp_.emaxx >= :7
AND sp_.emaxy >= :8) s_,
lease.counties_map_status_vw,
lease.f3166 shape
WHERE s_.sp_fid = shape.fid
AND s_.sp_fid = lease.counties_map_status_vw.shape
...
I'm looking for help diagnosing why our 1:M views no longer work correctly, or help understanding how the spatial view is used by ArcGIS, or to hear from anyone using 1:M views successfully.
