Oracle 10g环境下UNION ALL子查询与递归结果左连接性能优化求助

• 驱动表选择错误：GroupedData仅返回1500行属于极小结果集，优化器误判基数后大概率选择了RecursiveCte作为驱动表走哈希连接/排序合并连接，产生了大量无效运算。
• 子查询被意外展开：Oracle 10g优化器会默认将内联视图、未加提示的CTE展开到外层查询，导致UNION ALL的每个分支都单独和CONNECT BY递归逻辑做关联，相当于递归逻辑被执行了数十次，直接放大了耗时。
• 连接算法不合适：小结果集关联大结果集的场景下，嵌套循环连接是性能最优的选择，优化器误判后没有走嵌套循环，导致关联阶段耗时飙升。

方案1：加提示固定执行计划（最便捷，优先尝试） #

给内联视图加no_merge提示禁止优化器展开，同时加leading和use_nl提示强制小表驱动走嵌套循环，修改后SQL如下：

Select /*+ leading(GroupedData RecursiveCte) use_nl(RecursiveCte) */ * From
(
  SELECT * FROM table1
  Union All
  SELECT * FROM table2
  Union All
  SELECT * FROM table3
  Union All
  SELECT * FROM table4
) GroupedData /*+ no_merge */
LEFT JOIN
(
  -- 此处替换为你实际的CONNECT BY递归逻辑
  SELECT * FROM RecursiveCte
) RecursiveCte /*+ no_merge */
ON GroupedData.id = RecursiveCte.id

方案2：CTE物化后关联（方案1无效时尝试） #

Oracle 10g的WITH子句默认不会物化结果，加materialize提示强制将两个子查询的结果先写入临时表空间，再做关联，避免子查询展开，修改后SQL如下：

WITH GroupedData AS (
  SELECT /*+ materialize */ * FROM table1
  Union All
  SELECT /*+ materialize */ * FROM table2
  Union All
  SELECT /*+ materialize */ * FROM table3
  Union All
  SELECT /*+ materialize */ * FROM table4
),
RecursiveCte AS (
  -- 此处替换为你实际的CONNECT BY递归逻辑
  SELECT /*+ materialize */ * FROM RecursiveCte
)
Select /*+ leading(GroupedData RecursiveCte) use_nl(RecursiveCte) */ * 
From GroupedData
LEFT JOIN RecursiveCte ON GroupedData.id = RecursiveCte.id

方案3：改写为标量子查询（前两个方案无效时尝试） #

因为GroupedData仅有1500行，用标量子查询逐行匹配递归结果，完全避免子查询展开和关联算法选错的问题，性能表现极稳定，修改后SQL如下（需把递归子查询中你需要返回的字段逐个写出来，不要用*）：

SELECT 
  g.*,
  -- 示例：递归子查询需要返回a、b、c三个字段，逐个写标量子查询
  (SELECT a FROM RecursiveCte r WHERE r.id = g.id) a,
  (SELECT b FROM RecursiveCte r WHERE r.id = g.id) b,
  (SELECT c FROM RecursiveCte r WHERE r.id = g.id) c
FROM (
  SELECT * FROM table1
  Union All
  SELECT * FROM table2
  Union All
  SELECT * FROM table3
  Union All
  SELECT * FROM table4
) g

• 尽可能避免使用select *，仅查询你需要的字段，能大幅减少数据扫描、传输和运算的开销。
• 确认RecursiveCte返回结果的id字段是否有索引，若有索引能进一步提升关联匹配的速度。

内容的提问来源于stack exchange，提问作者bobster2dope