Oracle中的UNION原理

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. 利用执行计划分析工具定位瓶颈。