Oracle超大规模表SUM聚合查询性能优化咨询

我有一张包含约10亿行、50列的表table_a，在Oracle Analytics中执行包含多个SUM(CASE...)聚合函数与GROUP BY子句的查询时，性能极差甚至无响应。
移除所有SUM与GROUP BY后，返回100万行结果耗时约1分钟；添加后结果行数约5000行，因需导入Excel做进一步分析，需保持输出行数尽可能少以控制数据量与耗时。

查询语句如下：

Select Col_1,
       Col_2,
       ...,
       Col_10,
       SUM(CASE WHEN col_5 = 1 and Col_6 = 'a' then AMOUNT ELSE 0 END) as AMT_1,
       SUM(CASE WHEN col_5 = 2 and Col_6 = 'b' then AMOUNT ELSE 0 END) as AMT_2,
       ...
       SUM(CASE WHEN col_5 = 6 and Col_6 = 'd' then AMOUNT ELSE 0 END) as AMT_24
from table_a 
where col_1 = 1
      and col_5 in (1,2,3,4,5,6)
      and col_6 in ('a','b','c','d')
Group by Col_1, Col_2,...,Col_10

注：原语句中字符值补加单引号，where子句补全and逻辑符。

已尝试用EXPLAIN查看执行计划、用CREATE INDEX创建索引，但均不被支持。系统仅支持SELECT语句或WITH子句，且无其他可访问该数据库的系统，请问该如何优化查询性能？

• 用WITH子句提前过滤并缩小数据集
  原表有50列，仅提取聚合和分组必需的字段（10个分组列+col_5+col_6+AMOUNT），减少后续聚合处理的数据量与IO开销：

  WITH filtered_data AS (
      SELECT Col_1, Col_2, ..., Col_10, col_5, col_6, AMOUNT
      FROM table_a
      WHERE col_1 = 1
        AND col_5 IN (1,2,3,4,5,6)
        AND col_6 IN ('a','b','c','d')
  )
  SELECT Col_1, Col_2, ..., Col_10,
         SUM(CASE WHEN col_5 = 1 AND col_6 = 'a' THEN AMOUNT ELSE 0 END) AS AMT_1,
         SUM(CASE WHEN col_5 = 2 AND col_6 = 'b' THEN AMOUNT ELSE 0 END) AS AMT_2,
         ...
         SUM(CASE WHEN col_5 = 6 AND col_6 = 'd' THEN AMOUNT ELSE 0 END) AS AMT_24
  FROM filtered_data
  GROUP BY Col_1, Col_2, ..., Col_10
  

• 简化CASE表达式逻辑，先预聚合再转置
  将col_5和col_6的组合合并为一个标识键，先做轻量预聚合，再用MAX转置结果，减少多次SUM(CASE)的计算复杂度：

  WITH filtered_data AS (
      SELECT Col_1, Col_2, ..., Col_10,
             CONCAT(col_5, '_', col_6) AS group_key,
             AMOUNT
      FROM table_a
      WHERE col_1 = 1
        AND col_5 IN (1,2,3,4,5,6)
        AND col_6 IN ('a','b','c','d')
  ),
  pre_agg AS (
      SELECT Col_1, Col_2, ..., Col_10,
             group_key,
             SUM(AMOUNT) AS total_amt
      FROM filtered_data
      GROUP BY Col_1, Col_2, ..., Col_10, group_key
  )
  SELECT Col_1, Col_2, ..., Col_10,
         MAX(CASE WHEN group_key = '1_a' THEN total_amt ELSE 0 END) AS AMT_1,
         MAX(CASE WHEN group_key = '2_b' THEN total_amt ELSE 0 END) AS AMT_2,
         ...
         MAX(CASE WHEN group_key = '6_d' THEN total_amt ELSE 0 END) AS AMT_24
  FROM pre_agg
  GROUP BY Col_1, Col_2, ..., Col_10
  

• 利用分区裁剪特性
  若table_a在Oracle端已按col_1或col_5分区，WHERE子句中的过滤条件会自动触发分区裁剪，跳过无关分区，大幅减少扫描的数据量。

• 移除冗余分组列
  检查Col_1到Col_10之间的函数依赖关系（比如Col_1=1时，Col_2的取值完全由Col_3决定），若存在依赖，可去掉冗余分组列，用聚合函数（如MAX(Col_2)）在SELECT中获取对应值，降低GROUP BY的计算复杂度。

• 拆分聚合任务后合并结果
  若上述方法无效，可将大聚合拆分为多个小聚合任务，分别处理不同的col_5+col_6组合，再用UNION ALL合并后做最终聚合：

  WITH part_1 AS (
      SELECT Col_1, Col_2, ..., Col_10,
             SUM(AMOUNT) AS AMT_1,
             0 AS AMT_2,
             ...,
             0 AS AMT_24
      FROM table_a
      WHERE col_1=1 AND col_5=1 AND col_6='a'
      GROUP BY Col_1, Col_2, ..., Col_10
  ),
  part_2 AS (
      SELECT Col_1, Col_2, ..., Col_10,
             0 AS AMT_1,
             SUM(AMOUNT) AS AMT_2,
             ...,
             0 AS AMT_24
      FROM table_a
      WHERE col_1=1 AND col_5=2 AND col_6='b'
      GROUP BY Col_1, Col_2, ..., Col_10
  ),
  -- 依次定义其他22个part子查询
  all_parts AS (
      SELECT * FROM part_1
      UNION ALL SELECT * FROM part_2
      -- 依次UNION ALL其他part结果
  )
  SELECT Col_1, Col_2, ..., Col_10,
         SUM(AMT_1) AS AMT_1,
         SUM(AMT_2) AS AMT_2,
         ...,
         SUM(AMT_24) AS AMT_24
  FROM all_parts
  GROUP BY Col_1, Col_2, ..., Col_10
  

  拆分后的小聚合任务数据量更小，更容易被Oracle Analytics高效处理。

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