ST_GEOMETRY queries on an SDE view from a remote account = not using domain index

Question

Hello -

I was just wondering if anyone had any thoughts on this.

We have found that whenever an ST_GEOMETRY query is run against an SDE view from a remote account, the query takes a very long time by comparison to that same SDE view being created in the account the query is run from.  Running the same/similar ST_GEOMETRY query on the underlying feature class runs in about the same time whether from the account that owns the feature class or from a remote account.

From looking at execution plans, it seems that when doing an ST_GEOMETRY query (such as SDE.ST_INTERSTECTS) on an SDE view from a remote account, the domain index on the underlying feature class is not used.  Whereas, if the same query is run from the account that owns the SDE view, the domain index is used in the execution plan.  This is true even when the underlying feature class is on a different account.

For instance, we have the following:

GISLIB.PARCELS  = feature class of 3 million+ parcel records 
GISLIB.V_PARCELS = view of the GISLIB.PARCELS fields (all fields minus shape), with different field names (required by web apps)

We created an SDE view using the sdetable command:

sdetable -o create_view -T PARCELS_ALL -t "parcels, v_parcels" -c "v_parcels.objectid, v_parcels.county, v_parcels.parcelid, v_parcels.oname, v_parcels.onaddr1, parcels.shape" -w "v_parcels.objectid=parcels.objectid" -i esri_sde -s earth -u gislib -p *****

In the above example, when doing an ST_GEOMETRY query on the PARCELS_ALL view when connected as GISLIB works fast.  When doing this same query from another account (example the GISAPP account), the query seems to not work and will just sit there.  From the execution plan, it turns out that it is doing a full table scan of the original feature class and is not using the domain index.

But, if this same PARCELS_ALL view is created in the account the query is to be run from, the ST_GEOMETRY query is relatively fast and looking at the execution plan does use the domain index of the feature class:

sdetable -o create_view -T PARCELS_ALL -t "gislib.parcels_all, gislib.v_parcels_all" -c "gislib.v_parcels_all.objectid, gislib.v_parcels_all.county, gislib.v_parcels_all.parcelid, gislib.v_parcels_all.oname, gislib.v_parcels_all.onaddr1, gislib.parcels_all.shape" -w "gislib.v_parcels_all.objectid=gislib.parcels_all.objectid" -i esri_sde -s earth -u gisapp -p ****

Our external web applications use and query SDE views a lot, and we've gotten around this issue by creating the same SDE views in each of the accounts used by the web applications.  This isn't our preference - we'd prefer to have one set of SDE views in one main account - but can do this.

Again, I was just wondering if anyone might have thoughts on things we might try.

We are using:  Oracle 11.2.0.1, ArcSDE 10 sp3

Thanks -

Ellen Dean
St. Johns River Water Management District

EllenDean · Answer

Hi Vince,
thanks for the suggestions.  I hadn't realized that we shouldn't be using the sdetable -o create_view command with ST_GEOMETRY, and have tried the sdelayer -o register to register an Oracle view as you suggested and it should work fine for us instead.

Unfortunately, it seems to have the same behavior (i.e. doing a full table scan when querying from a remote account), even when I tried adding a hint, both in the original view creation as well as using it in the sql query.  But, running the same query on the original feature class from the remote account uses the domain index.

Below is output from this (for reference) - again, thanks for the ideas.  It was worth a shot - we can still use separate views in each of the remote accounts we need, which will perform well and use the domain index.  And we'll switch to using the sdelayer -o register command instead of the sdetable -o create view.

Thanks -

Ellen D.

[sde@sdetest ~]$ sqlplus edean@sde

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 7 18:18:17 2012

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

create or replace view v2_stn_et_geom as
select /*+ INDEX (stn_et_geom a11232_ix1)*/
objectid, stn_id, stn_nm, stn_tp_cd, lat_no, long_no, lat_no_dd, long_no_dd, div_id,
stn_stts_cd, sftwr_id, ctrl_dtm_cd, shape
from stn_et_geom
where stn_tp_cd=460
/

View created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[sde@sdetest ~]$

--Next, registering this with the sdelayer -o command:

[sde@sdetest ~]$ sdelayer -o register -l v2_stn_et_geom,shape -e p -t ST_GEOMETRY -C objectid,USER -i esri_sde -s earth -u edean -p ****

ArcSDE 10.0  for Oracle11g Build 1937 Tue Aug 16 16:08:18  2011
Layer    Administration Utility
-----------------------------------------------------
Successfully Created Layer.
[sde@sdetest ~]$

-- Starting a SQL*Plus session connected as a different account (sdeuser)

SQL> connect sdeuser@sde
Enter password:
Connected.
SQL>

SQL> select count(1) from edean.v2_stn_et_geom
  2  where sde.st_intersects(shape,sde.st_buffer(sde.st_point(591071.612,3273176.608,11),2000))=1;

COUNT(1)
----------
         2

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     1 |    34 |   307 |
|   1 |  SORT AGGREGATE     |                |     1 |    34 |       |
|   2 |   VIEW              | V2_STN_ET_GEOM |  6155 |   204K|   307 |
|   3 |    TABLE ACCESS FULL| STN_ET_GEOM    |  6155 |  1268K|   307 |
----------------------------------------------------------------------

SQL> -- Running the same query with the hint
SQL> 
SQL> select /*+ INDEX (v a11232_ix1) */
  2  count(1) from edean.v2_stn_et_geom v
  3  where sde.st_intersects(v.shape,sde.st_buffer(sde.st_point(591071.612,3273176.608,11),2000))=1;

COUNT(1)
----------
         2

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     1 |    34 |   307 |
|   1 |  SORT AGGREGATE     |                |     1 |    34 |       |
|   2 |   VIEW              | V2_STN_ET_GEOM |  6155 |   204K|   307 |
|   3 |    TABLE ACCESS FULL| STN_ET_GEOM    |  6155 |  1268K|   307 |
----------------------------------------------------------------------

SQL> -- Running the same query on the original feature class
SQL> 
SQL> select count(1) from edean.stn_et_geom
  2  where sde.st_intersects(shape,sde.st_buffer(sde.st_point(591071.612,3273176.608,11),2000))=1;

COUNT(1)
----------
         2

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |   211 |     4 |
|   1 |  SORT AGGREGATE               |            |     1 |   211 |       |
|   2 |   DOMAIN INDEX (Sel: .0018053)| A11232_IX1 |       |       |     4 |
----------------------------------------------------------------------------

VinceAngelo · Answer

You might want to try fashioning the view in SQL with a tuning hint, then registering the
view with 'sdelayer -o register' ( 'sdetable -o create_view' isn't really intended for use
with ST_GEOMETRY or SDO_GEOMETRY storage).

I wonder why you need to join a table with a view of itself when you can simply rename
the columns with aliasing.  In theory, the optimizer should be able to work that out, but 
I try to make the queries as easy as possible.

- V

EllenDean · Answer

Correction on the second sdetable command previously - it should have read:

sdetable -o create_view -T PARCELS_ALL -t "gislib.parcels, gislib.v_parcels" -c "gislib.v_parcels.objectid, gislib.v_parcels.county, gislib.v_parcels.parcelid, gislib.v_parcels.oname, gislib.v_parcels.onaddr1, gislib.parcels.shape" -w "gislib.v_parcels.objectid=gislib.parcels.objectid" -i esri_sde -s earth -u gisapp -p ****