Spatial View very slow

[ScottSugden]

Hi,

Environment: Windows Server 2008 R2 Standard, Oracle 11g, ArcGisServer 10.1 SP1

Using an existing Feature class, CADASTRE, I created an Oracle view as follows:

CREATE OR REPLACE FORCE VIEW "CADASTRE_TEST" (
"OBJECTID",
"CADID",
"LOTNUMBER",
"SECTIONNUMBER",
"PLANNUMBER",
"PLANLABEL",
"SHAPE")
AS
  select /*+FIRST_ROWS*/ c.objectid, c.cadid, c.lotnumber, c.sectionnumber, c.plannumber, c.planlabel, c.shape
  from CADASTRE c;

As you can see this is about as basic as it gets. There is no join etc. The only thing it does it select a subset of columns. In theory this should be faster than selecting * on CADASTRE. I tried it with and without the /*+FIRST_ROWS*/ hint.

I then described the feature class CADASTRE using sde as follows:
sdelayer -o describe_long -l CADASTRE,SHAPE -i sde:oracle11g:xxx -u GIS -p xxx   (xxx is dumy)

From that I worked out the shape data type, the SRID and the feature types. I then registered CADASTRE_TEST with sde as follows:
sdelayer -o register -l CADASTRE_TEST,SHAPE -e nac+ -t ST_GEOMETRY -C OBJECTID,USER -R 7 -i sde:oracle11g:xxx -u GIS -p xxx

The spatial view is then visible, selectable, identifiable etc in ArcMap/ArcCatalog. However, it is very slow. It is almost as if the spatial view is not using the spatial index from the feature class?

Any ideas? Thank you in advance.

[ScottSugden]

Same result.

I recreated the view to make sure it was not using any hints. I then ran the following query on both CADASTRE and CADASTRE_TEST:

Select *
From cadastre_test c
Where sde.st_envintersects (c.Shape, 143, -31.9, 144.5, -31.5) = 1;

Returning the first 500 rows over a WAN the CADASTRE table took 1.3 seconds and the CADASTRE_TEST view took 1.2 seconds.

So it would appear that there is no Oracle optimiser issue?

Thanks,

[ScottSugden]

Ok, the plot thickens. It appears to be an issue with registering the spatial view. I have been registering the spatial view from an Oracle view as follows:

sdelayer -o register -l CADASTRE_TEST,SHAPE -e nac+ -t ST_GEOMETRY -C OBJECTID,USER -R 7 -i sde:oracle11g:xxx -u xxx -p xxx

If I use sdetable to create a view as follows the performance of the spatial view is just fine:
sdetable -o create_view -T cadastre_test1 -t cadastre -c objectid,cadid,lotnumber,sectionnumber,plannumber,planlabel,shape -i sde:oracle11g:xxx -u xxx -p xxx

I have looked through the various SDE tables and found the following differences:
Table COLUMN_REGISTRY (Column OBJECT_ID different)
CADASTRE_TEST1 SDE SHAPE 8 32772 17
CADASTRE_TEST SDE SHAPE 8 32772 0

Table LAYERS (Columns EFLAGS, GSIZE1, BASELAYERID different)
17 SDE CADASTRE_TEST1 SHAPE 608436369 0 0.029 0 0 140.9995956528 -37.4407374802425 159.084596196734 -28.1570205549076 1353439530 DEFAULTS 1 6 7 0 0
16 SDE CADASTRE_TEST SHAPE 1682178193 0 0 0 0 140.9995956528 -37.4407374802425 159.084596196734 -28.1570205549076 1353210279 DEFAULTS 1 6 0 0 0

Table ST_GEOMETRY_COLUMNS (No entry for CADASTRE_TEST1)
SDE CADASTRE_TEST SHAPE ST_GEOMETRY

Table TABLE_REGISTRY (Columns OBJECT_FLAGS, REGISTRATION_DATE (expected), MINIUM_ID different)
22 CADASTRE_TEST1 SDE OBJECTID 131207 1353439389 DEFAULTS 1
21 CADASTRE_TEST SDE OBJECTID 16517 1353210142 DEFAULTS

Obviously the two methods of registration produce different results.

Is there something wrong with my sdelayer -o register?
Is this by design?
Is this a bug?

Thanks

[ScottSugden]

More info. I just did a sdelayer -o describe_long on both CADASTRE_TEST and CADASTRE_TEST1 and the only difference is in the Spatial Index.

For CADASTRE_TEST - Spatial Index: SPIDX_AUTOMATIC, exist: No, array form: 0,0,0
For CADASTRE_TEST1 - Spatial Index: SPIDX_GRID, GRID0=0.029, FULL, exist: Yes, array form: 0.029,0,0

I have some complex spatial views to create and would much prefer to use the 'sdelayer -o register' method. How can I use 'sdelayer -o register' and get the view to use the spatial index?

Thanks,

[VinceAngelo]

Please contact Tech Support.

- V

[ScottSugden]

Many thanks for you help.

[ScottSugden]

For those of you interested the following sde command creates a spatial view with good performance:

sdetable -o create_view -T cadastre_test -t cadastre -c objectid,cadid,lotnumber,sectionnumber,plannumber,planlabel,shape -i sde:oracle11g:xxx -u xxx -p xxx

It is not as nice as 'sdelayer -o register' as that provides more flexibility with complex views, but it is an option.

[VinceAngelo]

This goes against "best practice" guidelines.

The 'sdetable -o create_view' command should only be used with SDEBINARY or
SDELOB storage layers, where SQL views are not possible.  The preferred mechanism
for ST_GEOMETRY or native geometry types is to generate the view with SQL and use
'sdelayer -o register' (or the geodatabase toolbox equivalent). 

I haven't seen the issue you describe, but I'll try to reproduce it.

- V