Oracle查询优化：动态识别2024年及之前的空分区

需求背景 #

需要筛选出2024年及之前的空分区，当前数据库环境中存在未来日期的分区（如2025、2026年及以后的分区）。现有查询通过硬编码排除未来年份的方式灵活性差、健壮性不足，需实现无需硬编码的动态查询方案。

样本数据 #

schema_name         table_name            Partition Name           Row_count
A                       tab1                P202512                    0
A                       tab1                P202601                    0
A                       tab1                P202701                    0
A                       tab1                P202702                    0 

现有问题查询（健壮性不足） #

SELECT table_owner, table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_owner NOT IN ('AUDSYS', 'SYS', 'SYSTEM')
AND num_rows < 1 
AND partition_name NOT LIKE '%2025%'
AND partition_name NOT LIKE '%2026%'
AND partition_name NOT LIKE '%2027%'
AND partition_name NOT LIKE '%2028%'
AND partition_name NOT LIKE '%2029%'
AND partition_name NOT LIKE '%2030%'
ORDER BY TABLE_OWNER, TABLE_NAME, partition_name, num_rows;

动态解决方案 #

核心思路是从分区名称中提取年份数值，直接与2024做比较，无需硬编码未来年份。假设分区名称格式为PYYYYMM（前缀P+4位年份+2位月份），可通过字符串函数提取年份后判断：

基础版（适用于标准格式分区）

SELECT table_owner, table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_owner NOT IN ('AUDSYS', 'SYS', 'SYSTEM')
  AND num_rows < 1
  -- 提取分区名称中第2位开始的4位字符作为年份，转为数值后判断是否≤2024
  AND TO_NUMBER(SUBSTR(partition_name, 2, 4)) <= 2024
ORDER BY table_owner, table_name, partition_name, num_rows;

兼容版（处理非标准格式分区）

如果存在不符合PYYYYMM格式的分区，可通过CASE WHEN做安全转换，避免报错：

SELECT table_owner, table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_owner NOT IN ('AUDSYS', 'SYS', 'SYSTEM')
  AND num_rows < 1
  AND CASE 
        WHEN partition_name LIKE 'P______' THEN TO_NUMBER(SUBSTR(partition_name, 2, 4))
        ELSE 9999 -- 非标准格式分区默认排除，可根据需求调整逻辑
      END <= 2024
ORDER BY table_owner, table_name, partition_name, num_rows;

说明 #

• 该方案通过动态提取年份判断，无论未来新增多少年份的分区，都无需修改查询语句，健壮性大幅提升。
• 若分区名称格式不同（如无前缀P、年份位置不同），只需调整SUBSTR的参数即可适配。

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