我有一个自定义视图,该视图从几何列查询空间数据并提取纬度/经度值。但是,检索过程确实很慢,要检索视图数据最多需要5到10分钟。
这是我的看法:
CREATE OR REPLACE FORCE VIEW PoleData
(
G3E_FID,
X_COORD,
Y_COORD,
LATITUDE,
LONGITUDE
)
AS
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
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;
G3E_GEOMETRY
列是SDO_GEOMETRY
类型。 PolePoint表具有1,310,629行,而PoleDetailPoint具有100行。该表中的数据每天更新,而视图用于报表目的。我尝试使用
status=cleanup
参数重建空间索引。但这没有任何区别。我们的版本是Oracle 11.2.0.3。
赞赏有关检索此类视图/数据的任何技巧。还是我可以用来实现此目标的任何其他空间功能?
最佳答案
尝试使用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;
导致性能下降的另一个潜在原因是,您正在使用两次调用
SDO_UTIL.GET_VERTICES
的方法,一个直接在P.G3E_GEOMETRY
上,而另一个在P.G3E_GEOMETRY
的转换上,您实际上将得到两个顶点列表的叉积,因此例如,如果特定的P.G3E_GEOMETRY
包含5个顶点,则最终会得到5 * 5个记录,其中5个顶点P.G3E_GEOMETRY
的T和T2的25种可能组合中的每一种。我不知道顶点顺序是否由SDO_CS.TRANSFORM
函数保持,但是如果是,您可以通过向查询的每半添加and 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;