Oracle中的UNION操作用于合并多個SELECT語句的結果集，并自動去除重復行。其核心原理可分為以下幾個步驟：

1.?執行各個子查詢

• 每個SELECT語句獨立執行，生成各自的結果集。

• 如果子查詢包含過濾條件（如WHERE）、排序（如ORDER BY）或分組（如GROUP BY），會先處理這些操作。

?2.?合并結果集

?

• 所有子查詢的結果集會被合并到一個臨時工作區（通常在臨時表空間）。

• UNION會隱式執行UNION ALL操作（即不去重的合并），然后對合并后的結果進行去重。

• 如果使用UNION ALL，則跳過去重步驟，直接合并結果，性能更高。

3.?去重（僅UNION）?

?

• 排序去重（Sort Unique）：

  • Oracle默認對合并后的結果集進行排序（SORT ORDER BY），然后移除相鄰的重復行。

  • 排序可能消耗大量內存和I/O資源，尤其是處理大數據集時。

• 哈希去重（Hash Unique）：

  • 若優化器認為更高效，可能使用哈希算法（HASH UNIQUE）在內存中構建哈希表，快速判斷重復行。

• 去重的依據是所有列的值的組合。只有當兩行的所有列值完全相同時，才會被視為重復。

?4.?返回最終結果

• 去重后的結果集返回給用戶。

• 如果查詢包含ORDER BY，最終結果會按指定排序。

?性能影響因素

• 數據量大小：大數據集排序/哈希會消耗更多資源。

• 索引利用：若子查詢能利用索引，可能減少排序開銷。

• 臨時表空間：排序操作依賴臨時表空間，配置不足可能導致磁盤I/O瓶頸。

?

與UNION ALL的區別：?

• UNION ALL直接拼接結果，不去重，性能顯著優于UNION。

• 僅在需要去重時使用UNION。

優化建議：

1. 優先使用UNION ALL，除非明確需要去重。

2. 為子查詢的過濾條件添加索引，減少全表掃描。

3. 監控臨時表空間使用，避免磁盤溢出（Temp Space不足）。

?資源消耗的核心原理及關鍵因素：

?

1.?子查詢執行階段的資源消耗

• I/O消耗：
  每個子查詢可能需要全表掃描或索引掃描，具體取決于查詢條件和索引是否可用。若子查詢涉及大表且缺少索引，會導致高I/O開銷。

• CPU消耗：
  子查詢中的過濾（WHERE）、聚合（GROUP BY）或排序（ORDER BY）操作會占用CPU資源。

• 內存消耗：
  若子查詢使用哈希連接或排序操作（如GROUP BY），需要內存（PGA）存儲中間結果。

?2.?合并與去重的資源消耗

?UNION的核心資源消耗來源于去重操作，而UNION ALL無需去重，因此資源消耗顯著更低。

（1）去重機制與資源消耗

• 排序去重（SORT UNIQUE）：

  • 原理：Oracle將合并后的結果集按所有列進行排序，然后遍歷移除相鄰重復行。

  • 資源消耗：

    • 內存：排序操作優先使用內存（PGA的排序區），若數據量超出內存容量，會使用臨時表空間進行磁盤排序。

    • I/O：磁盤排序會產生大量臨時文件讀寫，導致高I/O開銷。

    • CPU：排序算法的復雜度（如快速排序）導致高CPU占用，尤其是大結果集。

  • 典型場景：結果集較小或內存充足時，排序去重效率較高。

• 哈希去重（HASH UNIQUE）：

  • 原理：Oracle在內存中構建哈希表，逐行計算哈希值，僅保留唯一哈希值對應的行。

  • 資源消耗：

    • 內存：哈希表需要足夠內存存儲所有唯一行的哈希值。若內存不足，會觸發磁盤溢出（Hash Area Size不足）。

    • CPU：哈希計算和沖突處理（如鏈表法）需要CPU資源。

  • 典型場景：結果集較大且內存充足時，哈希去重比排序更高效。

（2）合并結果集的資源消耗

• 臨時表空間：
  合并和去重操作可能需要將中間結果寫入臨時表空間，尤其是在內存不足時。

• 數據傳輸：
  多個子查詢的結果需要傳輸到合并工作區（內存或磁盤），網絡或I/O帶寬可能成為瓶頸（如分布式查詢）。

3.?關鍵影響因素?

?

（1）數據量大小

• 結果集越大，去重所需的排序或哈希操作消耗的資源（CPU、內存、I/O）呈指數級增長。

• 閾值：當結果集超過PGA或臨時表空間容量時，性能急劇下降。

（2）列數與數據類型

• 列數：列數越多，排序或哈希的計算量越大（需比較所有列的值）。

• 數據類型：

  • 長文本（CLOB）或二進制（BLOB）類型會增加比較的復雜度。

  • 隱式類型轉換（如VARCHAR2轉NUMBER）可能導致額外CPU開銷。

（3）索引與過濾條件

• 若子查詢能通過索引快速縮小結果集（如WHERE條件命中索引），可顯著減少后續去重的數據量。

• 無索引時，全表掃描會導致高I/O和CPU消耗。

（4）并行處理

• 若啟用并行查詢（PARALLEL提示），資源消耗會分散到多個進程，但可能增加總體CPU和內存使用。

4.?資源消耗優化建議

（1）避免不必要的去重

• 優先使用UNION ALL：除非明確需要去重，否則用UNION ALL替代UNION，直接跳過排序/哈希步驟。

（2）優化子查詢

• 添加過濾條件：減少每個子查詢的結果集大小。

• 利用索引：確保子查詢的WHERE、JOIN條件能命中索引。

• 避免SELECT *：僅選擇必要的列，減少數據傳輸和處理量。

（3）調整內存配置

• 增大PGA：
  調整PGA_AGGREGATE_TARGET或MEMORY_TARGET，確保排序和哈希操作盡量在內存中完成。

• 臨時表空間優化：
  使用高速存儲（如SSD）并確保臨時表空間足夠大，避免磁盤排序成為瓶頸。

（4）監控與調優工具

• 執行計劃分析：
  使用EXPLAIN PLAN或DBMS_XPLAN查看是否觸發了SORT UNIQUE或HASH UNIQUE。

?EXPLAIN PLAN FOR
SELECT col1 FROM table1
UNION
SELECT col2 FROM table2;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

總結

Oracle?UNION的資源消耗主要集中于去重階段的排序或哈希操作，其性能受數據量、內存配置、索引利用等因素直接影響。優化方向包括：

1. 減少數據量（過濾條件、索引）。

2. 避免不必要的去重（優先UNION ALL）。

3. 調整內存和臨時表空間。

4. 利用執行計劃分析工具定位瓶頸。

?

?