Oracle join view and spatial index

IanFirkin1 — Wed, 21 Dec 2011 12:57:34 GMT

Hello all,

I'm running SDE 10 on Oracle 11.1.0.7.0; what I have are two tables - one with just objectid and ST_GEOMETRY fields, and the other with some attribute data. Now I can join these tables just fine (on objectid, which is a PK for both tables), register the layer via sdelayer, and they show up and work fine in arcmap.

But:

Arcmap shows that the view-based feature class has no spatial index, and sdelayer - o describe_long shows the layer as having SPIDX_NONE (-1,0,0). The spatial table that the view is built off of does have an index, which both Arc and sdelayer recognize.

I've seen a handful of posts on the forum describing similar situations, but as far as I can tell, none of them have been resolved; has anyone out there gotten this to work, or is it just unsupported?

Thanks in advance,

IMF

Re: Oracle join view and spatial index

BirajaNayak — Fri, 30 Dec 2011 20:36:24 GMT

Please note that spatial view does not have spatial index. Reason of separate spatial index would not exist for the spatial view is as it is simply querying the spatial column of the feature class.

Please check the below explanation for more information:

For a ST_Geometry feature class:

1. Create a ST_Geometry feature class in ArcCatalog.

2. Create a spatial view from this feature class.

3. Query the SDE.ST_GEOMETRY_INDEX table:

select index_id, table_name from sde.st_geometry_index where table_name = 'VIEW' or table_name = 'FEATURE CLASS';

Note the only record returned is for the feature class spatial index, not the view. If the index_id for the feature class is 10 you will see a spatial index named S10_IDX$ in the schema of the feature class owner, but there is no entry for the view in the ST_geometry_index table and therefore no separate spatial index for the spatial view.

topic Oracle join view and spatial index in Data Management Questions

Oracle join view and spatial index

Re: Oracle join view and spatial index