环境：服务端 SDE 10.0 oracle 11.2，客户端 PLSQL 11，oracle 11.2
为了调试方便，以下测试都是把sql提取出来在PLSQL上做
需求是已知一个多边形的点坐标，要在一个线图层里，做关系为intersect的空间查询，原来的sql是这样
select LNO
from HSLINE t
where sde.st_intersects(t.shape,
sde.st_geometry(‘polygon ((536001.227128728 3646345.3998618745,535967.7465966925 3646245.5819230564,536081.5112751485 3646231.6260272274,536096.174854475 3646337.021285943,536096.174854475 3646337.021285943,536001.227128728 3646345.3998618745))‘,
t.shape.srid)) = 1
可是查询很慢，要几分钟，图层数据量才几万，理论上不应该这么慢
后来发现是构建几何对象（st_geometry）造成的，如果把st_geometry放在一个select里，查询速度就正常了
select LNO
from HSLINE t
where sde.st_intersects(t.shape,
(select sde.st_geometry(‘polygon ((536001.227128728 3646345.3998618745,535967.7465966925 3646245.5819230564,536081.5112751485 3646231.6260272274,536096.174854475 3646337.021285943,536096.174854475 3646337.021285943,536001.227128728 3646345.3998618745))‘,
t.shape.srid)
from HSLINE where rownum=1)) = 1
然而这样还有个缺陷，万一表HSLINE一行都没有就会出错，后来同事出了注意，最后做成完美版本
select LNO
from HSLINE t
where sde.st_intersects(t.shape,
(select sde.st_geometry(‘polygon ((536001.227128728 3646345.3998618745,535967.7465966925 3646245.5819230564,536081.5112751485 3646231.6260272274,536096.174854475 3646337.021285943,536096.174854475 3646337.021285943,536001.227128728 3646345.3998618745))‘,
t.shape.srid)
from dual)) = 1
dual的解释http://www.cnblogs.com/qiangqiang/archive/2010/10/15/1852229.html
最后，这个慢的原因，我觉得是可能每一行做where判断时，都会执行一次st_geometry生成几何对象，如果把它放在一个子select里，数据库就会只做一次子select查询，然后把结果存起来，主表where判断时直接用这个对象
