Oracle 11g统计已分析/未分析分区数的SQL简化方案咨询

场景说明 #

当前运行环境为 Oracle 11g 数据库，需求为按表维度统计两类分区数量：

• 已完成分析（已收集统计信息）的分区数
• 未完成分析的分区数
  原有实现搭配SQL*Plus报表命令，通过3个聚合子查询外连接实现统计，可返回预期结果，代码如下：

COMPUTE SUM OF "UNANALYZED" ON REPORT
COMPUTE SUM OF "ANALYZED" ON REPORT
COMPUTE SUM OF "TOTAL" ON REPORT
BREAK ON REPORT
select t1.table_name, 
       decode(t2.unanalyzed,null,0,t2.unanalyzed) unanalyzed,
       decode(t3.analyzed,null,0,t3.analyzed) analyzed,
       t1.total
from 
      ( SELECT table_name, count(1) total
        FROM DBA_TAB_PARTITIONS p
        WHERE 1=1
        AND table_owner = 'ABC'
        GROUP BY table_name ) t1 ,
      ( SELECT table_name, count(1) unanalyzed
        FROM DBA_TAB_PARTITIONS p
        WHERE 1=1
        AND table_owner = 'ABC'
        AND last_analyzed is NULL 
        GROUP BY table_name ) t2 ,
      ( SELECT table_name, count(1) analyzed
        FROM DBA_TAB_PARTITIONS p
        WHERE 1=1
        AND table_owner = 'ABC'
        AND last_analyzed is NOT NULL 
        GROUP BY table_name ) t3
where t1.table_name = t2.table_name (+)
      and t1.table_name = t3.table_name (+)
order by t1.table_name
;

更简洁的等效写法 #

原SQL三次访问DBA_TAB_PARTITIONS视图做聚合再外连接，完全可以用条件聚合一次扫描实现，逻辑完全等效，性能更好，写法也更精简：

-- 原有SQL*Plus报表命令无需任何修改
COMPUTE SUM OF "UNANALYZED" ON REPORT
COMPUTE SUM OF "ANALYZED" ON REPORT
COMPUTE SUM OF "TOTAL" ON REPORT
BREAK ON REPORT

SELECT 
    table_name,
    COUNT(CASE WHEN last_analyzed IS NULL THEN 1 END) unanalyzed,
    COUNT(CASE WHEN last_analyzed IS NOT NULL THEN 1 END) analyzed,
    COUNT(1) total
FROM DBA_TAB_PARTITIONS
WHERE table_owner = 'ABC'
GROUP BY table_name
ORDER BY table_name;

写法说明 #

• 仅对DBA_TAB_PARTITIONS做一次范围扫描，相比原写法三次扫描+外连接的逻辑，分区数量越多性能优势越明显
• 利用COUNT函数自动忽略NULL值的特性：CASE表达式仅在满足条件时返回1，不满足时返回NULL，不需要额外用DECODE/NVL处理空值，某张表无对应状态分区时会直接返回0，和原逻辑返回值完全一致
• 完全兼容原有的SQL*Plus合计、打印配置，不需要调整报表命令

分析函数实现方式（不推荐生产用） #

如果需要参考分析函数的写法，也可以通过窗口函数实现，但是这个场景下分组聚合已经是最优执行路径，分析函数写法需要做去重，性能不会比条件聚合更好，仅作示例：

COMPUTE SUM OF "UNANALYZED" ON REPORT
COMPUTE SUM OF "ANALYZED" ON REPORT
COMPUTE SUM OF "TOTAL" ON REPORT
BREAK ON REPORT

SELECT DISTINCT
    table_name,
    COUNT(CASE WHEN last_analyzed IS NULL THEN 1 END) OVER (PARTITION BY table_name) unanalyzed,
    COUNT(CASE WHEN last_analyzed IS NOT NULL THEN 1 END) OVER (PARTITION BY table_name) analyzed,
    COUNT(1) OVER (PARTITION BY table_name) total
FROM DBA_TAB_PARTITIONS
WHERE table_owner = 'ABC'
ORDER BY table_name;

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