Tableview in ArcSDE

[TorbjørnDalløkken]

Hi.
I've just started to learn ArcSDE, and I have a question about creating views. My database is Oracle Spatial and uses SDE. In my SDE-geodatabase there are several featureclasses; both polygons, polylines and points. I'm going to create a view based on some of the polyline featureclasses, with some of the attributes from the classes. I've create a geodatabasetable containing the featureinformation using SQL (Union).

I'm trying to create the view using:

"%SDEHOME%"\bin\sdetable -o create_view ^
-T DV_Points ^
-t "RiverPoints,dv_points" ^
-c "dv_points.description,dv_points.historic,dv_points.station,RiverPoints.SHAPE,RiverPoints.OBJECTID" ^
-w "RiverPoints.OBJECTID = dv_points.OBJECTID" ^
-i %1 -s %2 -u %3 -p %4

The query executes ok, and the view shows up in the geodatabase. But it's really slow. The view takes contains around 2000 features and it takes several minutes (10-30) to draw or query it. Is it right to use just one of the featuresclasses SHAPE-field (in the c-parameter), or should I use the shape-field from every featureclass?

Regards,
Torbjørn

[VinceAngelo]

What storage type is being used in your layer?  If the storage is ST_GEOMETRY or SDO_GEOMETRY,
then you really shouldn't use 'sdetable -o create_view' (instead, create the view with SQL, and register
the result with 'sdelayer -o register').

What version of Oracle are you using (A.B.C.D notation)? I've seen extremely ugly query plans from
Oracle 10gR1 (10.1.0.2) servers on views over SDO_GEOMETRY layers that resulted in 8 minute
queries on a  view with 2000 rows (with a 7M row base table), but haven't seen anything like that
with modern Oracle builds (unless the table was a remote view into another instance).

Keep in mind that views can generate unhealthy queries without a spatial component, but that some
spatial comparisons can be very expensive, so aligning the spatial comparison on the first table in
the join is much more likely to hit the right index.  You should also explore the impact of hints and
various indexes, and of table join order.

- V