连续时间间隔数据聚合优化：寻求UNION ALL替代方案

针对一次性查询7天数据耗时过长、当前采用分2天批次+UNION ALL聚合的场景，以下是几个不需要UNION ALL的优化替代方案：

一、分区裁剪+合理并行度（表为分区表时优先用） #

如果你的table是按DTM字段做范围分区的，直接让Oracle利用分区裁剪只扫描目标日期分区，配合适配服务器配置的并行度，效率比拆分UNION ALL更高：

SELECT /*+ full(t) PARALLEL(t, 8) partition(t, p_20240520, p_20240526) */ -- 替换为实际需要扫描的分区名
       TRUNC(DATE) AS CREATE_DATE, NAME1, NAME2, TYPE, STATUS, COUNT(1) AS COUNT, ID 
FROM table t
WHERE DTM < SYSDATE + 1 
  AND DTM > SYSDATE - 6 
  AND STATUS != 'DONE' 
  AND (TEST_MODE IS NULL OR TEST_MODE = 'N') 
GROUP BY ID, NAME1, NAME2, TYPE, TRUNC(DATE), STATUS 
ORDER BY ID, NAME1, NAME2, TYPE, STATUS, TRUNC(DATE) DESC;

• 说明：partition(t, 分区名)强制Oracle只扫描指定日期分区，避免全表扫描；并行度建议根据服务器CPU核心数设置（比如8核设8），不要盲目填固定值。

二、预聚合物化视图（高频查询首选） #

如果这个查询是日常高频执行的，直接创建按分组字段+日期预聚合的物化视图，定期刷新后直接查询物化视图，速度会有数量级提升：

创建物化视图 #

CREATE MATERIALIZED VIEW mv_table_stats
BUILD IMMEDIATE
REFRESH FAST ON DEMAND -- 可改为每天定时刷新，比如`REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1`
AS
SELECT TRUNC(DATE) AS CREATE_DATE, NAME1, NAME2, TYPE, STATUS, ID, COUNT(1) AS COUNT
FROM table
WHERE STATUS != 'DONE' 
  AND (TEST_MODE IS NULL OR TEST_MODE = 'N')
GROUP BY ID, NAME1, NAME2, TYPE, TRUNC(DATE), STATUS;

查询物化视图（仅过滤7天数据） #

SELECT * FROM mv_table_stats
WHERE CREATE_DATE >= TRUNC(SYSDATE - 6)
ORDER BY ID, NAME1, NAME2, TYPE, STATUS, CREATE_DATE DESC;

三、添加覆盖索引避免全表扫描 #

如果表不是分区表，给过滤字段和分组字段建覆盖索引，让Oracle直接通过索引完成过滤、聚合，不需要回表查询：

CREATE INDEX idx_table_dtm_status_test ON table(DTM, STATUS, TEST_MODE)
INCLUDE (DATE, NAME1, NAME2, TYPE, ID); -- 包含所有需要返回和分组的字段

修改原查询，去掉强制全表扫描的提示，让优化器自动选择走索引：

SELECT TRUNC(DATE) AS CREATE_DATE, NAME1, NAME2, TYPE, STATUS, COUNT(1) AS COUNT, ID 
FROM table
WHERE DTM < SYSDATE + 1 
  AND DTM > SYSDATE - 6 
  AND STATUS != 'DONE' 
  AND (TEST_MODE IS NULL OR TEST_MODE = 'N') 
GROUP BY ID, NAME1, NAME2, TYPE, TRUNC(DATE), STATUS 
ORDER BY ID, NAME1, NAME2, TYPE, STATUS, TRUNC(DATE) DESC;

四、更新表统计信息 #

如果Oracle的表统计信息过时，优化器可能生成低效执行计划。执行以下命令更新统计信息，让优化器能正确判断最优扫描方式：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'table', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);

附：你提供的两种原始查询（中文标注） #

原始全量7天查询

SELECT /*+ full(t) PARALLEL(t,10) */ TRUNC(DATE) AS CREATE_DATE, NAME1, NAME2, TYPE, STATUS, COUNT(1) AS COUNT, ID 
FROM table 
WHERE REQ_DTM < SYSDATE + 1 AND DTM > SYSDATE - 6 
    AND (STATUS != 'DONE') 
    AND (TEST_MODE is null or TEST_MODE ='N') 
GROUP BY ID, NAME1, NAME2, TYPE, TRUNC(DATE), STATUS 
ORDER BY ID, NAME1, NAME2, TYPE, STATUS, TRUNC(DATE) DESC;

当前分批次UNION ALL查询

SELECT TRUNC(DATE) AS CREATE_DATE, NAME1, NAME2, TYPE, STATUS, COUNT(1) AS COUNT, ID 
FROM (SELECT /*+ full(t) PARALLEL(t,10) */ 
        DATE, NAME1, NAME2, TYPE, STATUS, ID 
        FROM table 
        WHERE _DTM < SYSDATE + 1 AND DTM >= SYSDATE - 1
            AND (STATUS != 'DONE')
            AND (TEST_MODE is null or TEST_MODE ='N') 
        UNION ALL
      SELECT /*+ full(t) PARALLEL(t,10) */ 
        DATE, NAME1, NAME2, TYPE, STATUS, ID 
        FROM table 
        WHERE DTM < SYSDATE - 1 AND DTM >= SYSDATE - 3
            AND (STATUS != 'DONE') 
            AND (TEST_MODE is null or TEST_MODE ='N') 
        UNION ALL
      SELECT /*+ full(t) PARALLEL(t,10) */ 
        DATE, NAME1, NAME2, TYPE, STATUS, ID 
        FROM table 
        WHERE DTM < SYSDATE -3 AND DTM >= SYSDATE - 5
            AND (STATUS != 'DONE')
            AND (TEST_MODE is null or TEST_MODE ='N') 
        UNION ALL
      SELECT /*+ full(t) PARALLEL(t,10) */ 
        DATE, NAME1, NAME2, TYPE, STATUS, ID 
        FROM table
        WHERE DTM < SYSDATE - 5 AND DTM > SYSDATE - 6
            AND (STATUS != 'DONE') 
            AND (TEST_MODE is null or TEST_MODE ='N'))
GROUP BY ID, NAME1, NAME2, TYPE, TRUNC(DATE), STATUS 
ORDER BY ID, NAME1, NAME2, TYPE, STATUS, TRUNC(DATE) DESC;

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