转载：SDE ST_Geometry SQL st_intersects查询很慢的解决方法

环境：服务端 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判断时直接用这个对象