在數字表上交叉連接以獲取線頂點，有沒有更好的方法？
問題：
我有一個空間表（道路線），使用 ESRI 的
SDE.ST_GEOMETRY使用者定義數據類型儲存在 Oracle 12c地理數據庫中。我想列出線的頂點，以便我最終可以訪問和更新它們的座標。如果我使用的是 SDO_GEOMETRY/Oracle Locator，那麼我會使用該SDO_UTIL.GETVERTICES函式。但是我沒有使用 SDO_GEOMETRY/Oracle Locator，並且SDE.ST_GEOMETRY. 我能找到的唯一與頂點有關的SDE.ST_GEOMETRY函式ST_PointN是和ST_NumPoints。我想出了一個成功完成所有這些的查詢 - 將線頂點作為行（受此頁面啟發）：
1 SELECT a.ROAD_ID 2 ,b.NUMBERS VERTEX_INDEX 3 ,a.SDE.ST_X(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS X 4 ,a.SDE.ST_Y(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS Y 5 FROM ENG.ROADS a 6 CROSS JOIN ENG.NUMBERS b 7 WHERE b.NUMBERS <= SDE.ST_NumPoints(a.SHAPE) 8 --removed to do explain plan: ORDER BY ROAD_ID, b.NUMBERS ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 1 | MERGE JOIN | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 2 | INDEX FULL SCAN | R23715_SDE_ROWID_UK | 30 | 90 | | 1 (0)| 00:00:01 | |* 3 | SORT JOIN | | 3997 | 1018K| 2392K| 261 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL| ROAD | 3997 | 1018K| | 34 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 3 - access(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))" " filter(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))"它將表中
CROSS JOINS的行ROADS轉換為NUMBERS表（並將結果限制為每行中的頂點數）。統計：（更新）
- 每行最多有 30 個頂點（平均每行 4.38 個頂點）
- ROADS 有 3,997 條線路
- NUMBERS 有 30 行（從 1 開始的連續數字）
- 結果集有 17,536 行
但是，性能很差（40 秒），我不禁想——有沒有更優雅的方法來做到這一點？對我來說，使用數字表和交叉連接似乎是一種草率的方法。有沒有更好的辦法？
外行的條款將不勝感激；我是公共工程人員，而不是 DBA。
更新#1：
如果我從查詢中刪除第 3 行和第 4 行（X 和 Y 相關函式的字元串），它會立即執行。但當然，我不能只刪除這些行，我需要X 和 Y 列。所以這讓我相信緩慢的性能與 X & Y 函式有關。
但是，如果我將點導出到靜態表，然後在其上執行 X 和 Y 函式，這也會立即執行。
那麼，這是否意味著性能緩慢是由 X 和 Y 函式引起的，除了，好吧，不是嗎？我糊塗了。
更新#2：
如果我將 X 和 Y 帶出查詢，將它們放在外部查詢中，並將 ROWNUM 添加到內部查詢中，那麼它會更快（16 秒 - 更新）：
SELECT ROWNUM ,ROAD_ID ,VERTEX_INDEX ,SDE.ST_X(ST_POINT) AS X ,SDE.ST_Y(ST_POINT) AS Y FROM ( SELECT ROWNUM ,a.ROAD_ID ,b.NUMBERS VERTEX_INDEX ,SDE.ST_PointN(a.SHAPE, b.NUMBERS) AS ST_POINT FROM ENG.ROAD a CROSS JOIN ENG.NUMBERS b WHERE b.NUMBERS <= SDE.ST_NumPoints(a.SHAPE) ) --removed to do explain plan: ORDER BY ROAD_ID, VERTEX_INDEX ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5996 | 322K| | 262 (1)| 00:00:01 | | 1 | COUNT | | | | | | | | 2 | VIEW | | 5996 | 322K| | 262 (1)| 00:00:01 | | 3 | COUNT | | | | | | | | 4 | MERGE JOIN | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 5 | INDEX FULL SCAN | R23715_SDE_ROWID_UK | 30 | 90 | | 1 (0)| 00:00:01 | |* 6 | SORT JOIN | | 3997 | 1018K| 2392K| 261 (1)| 00:00:01 | | 7 | TABLE ACCESS FULL| ROAD | 3997 | 1018K| | 34 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 6 - access(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))" " filter(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))"Justin Cave 解釋了為什麼 ROWNUM 有助於提高性能：為什麼將 ROWNUM 添加到查詢中會提高性能？
雖然這種性能改進很好，但還不夠好。而且我不禁認為我仍然不完全理解查詢是如何工作的，或者為什麼它這麼慢。
問題仍然存在：有沒有更好的方法？
我對 Oracle 性能有一點了解，對自定義數據類型幾乎一無所知，但我會嘗試為您提供一個提高性能的計劃。
1) 確認您無法獲得解釋計劃。
即使您沒有復雜的數據庫軟體，也可以獲得解釋計劃。如果你執行會發生什麼
set autotrace on explain？您也可以嘗試DBMS_XPLAN。首先通過使用一些額外的關鍵字包裝查詢來節省計劃：
explain plan for (SELECT... your query goes here);然後執行這個：
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());有可能這些都不起作用，你真的無法得到解釋計劃。我只是想驗證一下，因為有了解釋計劃，社區可以更容易地幫助你。
2) 考慮需求。
你說20秒不夠好。您或其他人是否準確定義了什麼是足夠好的？有沒有商量的餘地？您的查詢是否需要恰好是一個 SELECT 查詢？您能否一步填充全域臨時表並在下一步中選擇您想要的結果？你能創建一個返回結果集並呼叫它的儲存過程嗎？
3) 建立完成查詢所需時間的下限。
我建議執行一個“作弊”的簡單查詢，以找出優化良好的查詢是什麼樣的。例如，這個只獲取第一個頂點的查詢需要多長時間？
SELECT ROWNUM ,ROAD_ID ,VERTEX_INDEX ,SDE.ST_X(ST_POINT) AS X ,SDE.ST_Y(ST_POINT) AS Y FROM ( SELECT ROWNUM ,a.ROAD_ID ,1 VERTEX_INDEX ,SDE.ST_PointN(a.SHAPE, 1) AS ST_POINT FROM ENG.ROAD a ) ORDER BY ROAD_ID, VERTEX_INDEX;我懷疑這會給你 4000 行。如果將該查詢的響應時間乘以 17.5/4，則可以為您提供一個很好的總執行時間下限。
如果您的總執行時間的下限比您在步驟 2 中建立的時間長，那麼您需要通過提前計算結果並將其儲存在表格中來創造性地使用您的數據模型，或者您需要重新協商所需的響應時間。
4) 基準測試以確定哪些功能對您的執行時間貢獻最大。
您在 Update #1 中處於正確的軌道上，但您需要嘗試控制正在完成的工作量。例如，是否可以編寫一組相對簡單的查詢，將每個函式恰好執行 10000 次？響應時間如何比較？
5) 去上班。
根據在第 2 步中建立的要求以及在第 4 步中找到的內容，嘗試任何您能想到的減少查詢執行時間的技巧。您是否能夠預先計算結果並保存它們？如果問題與函式執行的次數有關，那麼未記錄的具體化提示可能會有所幫助。這迫使 Oracle 在幕後創建一個隱藏的臨時表來儲存結果。我不知道它是否與您使用的特殊數據類型兼容。
例如，也許這樣的事情表現更好？抱歉，如果它無法編譯，但我無法測試。
WITH ROAD_CTE (ROAD_ID, VERTEX_INDEX, SHAPE) AS ( SELECT /*+ materalize */ a.ROAD_ID , b.NUMBERS VERTEX_INDEX , a.SHAPE FROM ENG.ROAD a CROSS JOIN ENG.NUMBERS b WHERE b.NUMBERS <= SDE.ST_NUMPOINTS(a.SHAPE) ) , CTE_WITH_ST_POINT (ROAD_ID, VERTEX_INDEX, ST_POINT) AS ( SELECT /*+ materalize */ rcte.ROAD_ID , rcte.VERTEX_INDEX , SDE.ST_PointN(rcte.SHAPE, rcte.VERTEX_INDEX) ST_POINT FROM ROAD_CTE rcte ) SELECT ROAD_ID , VERTEX_INDEX , SDE.ST_X(ST_POINT) AS X , SDE.ST_Y(ST_POINT) AS Y FROM CTE_WITH_ST_POINT ORDER BY ROAD_ID, VERTEX_INDEX;如果您在所有這些之後仍然被卡住，我懷疑它至少會為您提供可以編輯到問題中的其他資訊。祝你好運！