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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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 |
----------------------------------------------------------------------------