oracle idcs,Oracle Spatial函数SDO_CS.Transform(value)结果非常慢

尝试使用UNION ALL而不是UNION：

SELECT P.g3e_fid,

T2.X * 1000 AS x_coord,

T2.Y * 1000 AS y_coord,

T.Y AS latitude,

T.X AS longitude

FROM PolePoint P,

TABLE (

SDO_UTIL.GETVERTICES (SDO_CS.TRANSFORM (P.G3E_GEOMETRY, 8265))) T,

TABLE (SDO_UTIL.GETVERTICES (P.G3E_GEOMETRY)) T2

WHERE P.ltt_id = 0

UNION ALL

SELECT P.g3e_fid,

T2.X * 1000 AS x_coord,

T2.Y * 1000 AS y_coord,

T.Y AS latitude,

T.X AS longitude

FROM PoleDetailPoint P,

TABLE (

SDO_UTIL.GETVERTICES (SDO_CS.TRANSFORM (P.G3E_GEOMETRY, 8265))) T,

TABLE (SDO_UTIL.GETVERTICES (P.G3E_GEOMETRY)) T2

WHERE P.ltt_id = 0;

性能下降的另一个潜在来源是你直接在P.G3E_GEOMETRY上使用两个SDO_UTIL.GET_VERTICES调用而另一个在P.G3E_GEOMETRY的变换上你将基本上有两个顶点列表的叉积的事实,所以例如,如果特定的P.G3E_GEOMETRY包含5个顶点,那么对于该5个顶点P.G3E_GEOMETRY,对于T和T2的25种可能组合中的每一种,最终将得到5 * 5条记录.我不知道顶点顺序是否由SDO_CS.TRANSFORM函数维护,但如果是,则可以通过在查询的每一半中添加和t1.id = t2.id谓词来提高性能：

SELECT P.g3e_fid,

T2.X * 1000 AS x_coord,

T2.Y * 1000 AS y_coord,

T.Y AS latitude,

T.X AS longitude

FROM PolePoint P,

TABLE (

SDO_UTIL.GETVERTICES (SDO_CS.TRANSFORM (P.G3E_GEOMETRY, 8265))) T,

TABLE (SDO_UTIL.GETVERTICES (P.G3E_GEOMETRY)) T2

WHERE P.ltt_id = 0

AND T.ID = T2.ID

UNION ALL

SELECT P.g3e_fid,

T2.X * 1000 AS x_coord,

T2.Y * 1000 AS y_coord,

T.Y AS latitude,

T.X AS longitude

FROM PoleDetailPoint P,

TABLE (

SDO_UTIL.GETVERTICES (SDO_CS.TRANSFORM (P.G3E_GEOMETRY, 8265))) T,

TABLE (SDO_UTIL.GETVERTICES (P.G3E_GEOMETRY)) T2

WHERE P.ltt_id = 0

AND T.ID = T2.ID;