Joining a spatial layer with Oracle SDE view only returning some records

Question

I'm running SDE 9.1 on Oracle 10g. I have a spatial table with land parcel polygons and the other with some attribute data. The spatial table is indexed but the attribute data table is a view and is not. I do a join in Arcmap based on a parcel id and everything appears to work fine but it does not when you try to use more than a few hundred records.

In ArcMap 10.0, 10.1 and 10.2 only the first 300 records (out of 2300) are returned when the table is viewed.
In ArcMap 9.31 it works fine.

Does anyone know if there settings in ArcMap 10.x that control the number of records returned in a request or is this just an incompatibility between versions with ArcGIS.

Thanks
Larry

VinceAngelo · Answer

What exact version of Oracle are you using (10.a.b.c.d notation)?
Oracle 10.2.0.1.0 was known to not function reliably, and I think
many of the 10.1.* releases had similar issues.

The relationship between the layer and view is 1:Many, correct?
That appears to be your problem -- ArcGIS only supports 1:Many
relationships with relationship classes.

If you want to implement a different view, that joins the layer
in with the other three tables, then you need to:
 1) Alter the table with many cardinality so that it has a rowid-ready
column (number(38) NOT NULL, with unique values).  NOTE THAT
YOU CANNOT USE ROWNUM() FOR THIS PURPOSE -- EACH
SUBSEQUENT QUERY MUST RETURN THE SAME ROWID VALUE
AS RETURNED BY AN UNBOUNDED QUERY.
2) Create a proxy table that has the same column definitions as
the current view (but with the rowid added)
3) Use 'sdetable -o create_view' to join the layer table to the
proxy table USING THE ROWID COLUMN OF THE PROXY TABLE
INSTEAD OF THE OBJECTID ON THE BUSINESS TABLE, specifying
the "-C" on that rowid column, USER-SET.
4) Edit the resultant view to do a four-table join between the
three view source tables and the business table, using the
columns in the same order as defined in the proxy table.

The resulting view should have unique rowids (you should verify
this with a "SELECT COUNT(DISTINCT rowidcol) FROM viewname"),
or Desktop will not be able to handle the results.

- V

LarryNolan · Answer

Vince, 
  I have some of the information on the attribute table view "LICENSE" and Spatial Table "CLAIMS"
The views are "not materialized" so they are just plain SDE views.
The view contains attribute data about a land parcel. Owner, issue date, cost and few more similar fields.

Here is the code from the creation of the views.

Thanks again for your help.

CREATE OR REPLACE VIEW LICENSE AS
SELECT l.license_nbr, l.file_nbr AS filenum, c.client_name, l.LOCATION,
          l.num_curr_claims AS numclaims, l.status, l.stake_dt AS stakedate,
          l.record_dt AS recdate, l.issue_dt AS issdate,
          l.wr_due_dt AS wrkdue,
             map_num1
          || ' '
          || map_num2
          || ' '
          || map_num3
          || ' '
          || map_num4
          || ' '
          || map_num5 AS mapsheets,
          w.total_exp
     FROM owners.licenses l, owners.clients c, owners.license_exp w
    WHERE l.client_id = c.client_id and L.LICENSE_ID=W.LICENSE_ID(+) AND status IN ('IS', 'RC', 'P');

-- Create table
create table CLAIMS
(
  objectid    NUMBER(38) not null,
  cdate       DATE,
  operator    VARCHAR2(30),
  zone        NUMBER(6),
  license_nbr VARCHAR2(10),
  shape       INTEGER
)
tablespace LANDPARCELS_SMALL
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes 
create unique index A90_IX1 on CLAIMS (SHAPE)
  tablespace LANDPARCELS_INDX
  pctfree 10
  initrans 4
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create unique index R116_SDE_ROWID_UK on CLAIMS (OBJECTID)
  tablespace LANDPARCELS_INDX
  pctfree 10
  initrans 4
  maxtrans 255
  storage
  (
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CLAIMS
  add constraint A90_FK1 foreign key (SHAPE)
  references F90 (FID)
  disable;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on CLAIMS to SDE;
grant select on CLAIMS to VIEWER;

LarryNolan · Answer

Ha, I wish. No it's SDE 9.1, it was in place before the IT beast was created. It's called living in the world where IT controls your environment and it takes forever to change a production system. Every change, no matter how simple is a major project involving teams of people and of course they have no GIS or GIS aware staff so contracts have to be let for SDE work so it never gets done. Sorry for the vent.

I did a little more digging and the joins in ArcMAP 9.31 still have issues and as bad as 10.x but still there. About half the 2313 records are there instead of about one eigth in 10.x

Are you really using ArcSDE 9.1 (which has been retired for years) or 
ArcSDE 9.3.1 (which has been retired for months)?

None of the ArcGIS 10.x releases are compatible with ArcSDE 9.1.

In order to find out what's happening, you'd need to provide more
details on the contents of the tables and the exact syntax of the
view.

- V