Oracle 19c按月每日提取多表大小：现有查询遇问题求优化

一、解决空值导致无结果的问题 #

你的原SQL中，当某列所有值为NULL时，SUM(VSIZE(col))会返回NULL，进而导致整个sum_size为NULL。可以用NVL函数将空值转换为0，确保计算结果有效：

SELECT 
    dt AS "日期",
    sum_size AS "当日表大小(GB)",
    SUM(sum_size) OVER (ORDER BY dt) AS "累计表大小(GB)"
FROM (
    SELECT 
        dt,
        -- 对每个列的VSIZE结果做NVL处理，空值转0
        (SUM(NVL(VSIZE(id), 0)) + SUM(NVL(VSIZE(time), 0)) + SUM(NVL(VSIZE(name), 0))) / 1024 / 1024 / 1024 AS sum_size
    FROM the_table
    -- 限定目标月份范围
    WHERE dt BETWEEN TO_DATE('2024-05-01', 'YYYY-MM-DD') AND TO_DATE('2024-05-31', 'YYYY-MM-DD')
    GROUP BY dt
) t
ORDER BY dt;

注：把原查询中的date改名为dt，避免与Oracle关键字冲突。

二、多表多列的动态执行方案 #

利用Oracle数据字典user_tab_columns自动获取表的所有列，动态生成统计SQL，无需手动逐个列编写：

方案1：生成单表查询SQL #

执行以下语句，会为指定列表中的每张表生成对应的统计SQL，复制后直接执行即可：

SELECT 
    'SELECT 
        dt AS "日期",
        ''' || table_name || ''' AS "表名",
        (SUM(NVL(VSIZE(' || LISTAGG(column_name, '), 0)) + SUM(NVL(VSIZE(') WITHIN GROUP (ORDER BY column_name) || '), 0))) / 1024 / 1024 / 1024 AS "当日表大小(GB)",
        SUM((SUM(NVL(VSIZE(' || LISTAGG(column_name, '), 0)) + SUM(NVL(VSIZE(') WITHIN GROUP (ORDER BY column_name) || '), 0))) / 1024 / 1024 / 1024) OVER (ORDER BY dt) AS "累计表大小(GB)"
    FROM ' || table_name || '
    WHERE dt BETWEEN TO_DATE(''2024-05-01'', ''YYYY-MM-DD'') AND TO_DATE(''2024-05-31'', ''YYYY-MM-DD'')
    GROUP BY dt
    ORDER BY dt;' AS dynamic_sql
FROM user_tab_columns
-- 替换为你的目标表名集合
WHERE table_name IN ('TABLE_A', 'TABLE_B', 'TABLE_C')
GROUP BY table_name;

方案2：批量汇总到结果表 #

如果需要把所有表的统计结果统一存储，先创建结果表：

CREATE TABLE table_daily_size (
    table_name VARCHAR2(128),
    record_date DATE,
    daily_size_gb NUMBER(10,4),
    cumulative_size_gb NUMBER(10,4)
);

再执行以下语句生成批量插入SQL，运行这些插入语句后，查询table_daily_size即可查看所有表的统计数据：

SELECT 
    'INSERT INTO table_daily_size (table_name, record_date, daily_size_gb, cumulative_size_gb)
    SELECT 
        ''' || table_name || ''' AS table_name,
        dt AS record_date,
        daily_size_gb,
        cumulative_size_gb
    FROM (
        SELECT 
            dt,
            (SUM(NVL(VSIZE(' || LISTAGG(column_name, '), 0)) + SUM(NVL(VSIZE(') WITHIN GROUP (ORDER BY column_name) || '), 0))) / 1024 / 1024 / 1024 AS daily_size_gb,
            SUM((SUM(NVL(VSIZE(' || LISTAGG(column_name, '), 0)) + SUM(NVL(VSIZE(') WITHIN GROUP (ORDER BY column_name) || '), 0))) / 1024 / 1024 / 1024) OVER (ORDER BY dt) AS cumulative_size_gb
        FROM ' || table_name || '
        WHERE dt BETWEEN TO_DATE(''2024-05-01'', ''YYYY-MM-DD'') AND TO_DATE(''2024-05-31'', ''YYYY-MM-DD'')
        GROUP BY dt
    );' AS dynamic_insert_sql
FROM user_tab_columns
WHERE table_name IN ('TABLE_A', 'TABLE_B', 'TABLE_C')
GROUP BY table_name;

注意事项 #

• 确保所有目标表都有统一的日期列（示例中为dt），如果列名不同，需调整SQL中的WHERE和GROUP BY部分。
• 大型表建议在日期列上创建索引，避免全表扫描影响性能。
• 若表包含LOB列，VSIZE会计算LOB的存储开销（含头部信息），如需精确数据大小，可替换为NVL(DBMS_LOB.GETLENGTH(col), 0)。

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