如何让SQL查询批量输出表列表中各表的平均行大小？

你的思路非常实用——用实际的段字节数和行数来计算平均行大小，不依赖可能过时的统计信息，确实是个靠谱的容量预估方法。不过你原来的多表查询写法有两个关键问题，导致没有返回预期结果：

1. 第一个子查询把所有目标表的字节数总和算了出来，而不是单个表的字节数；
2. 第二个子查询统计的是table_list这个表的行数（也就是你要遍历的表的数量），而不是每个目标表自己的行数；
3. 两个子查询没有按表名关联，所以最终只会得到一个“所有表的平均行大小的平均值”，而不是每个表单独的结果。

针对需求的修改方案 #

因为你需要不依赖统计信息，必须实际统计每个表的行数，纯SQL很难动态处理不同表名的COUNT查询，所以推荐用PL/SQL块来实现遍历计算，这样能准确获取每个表的行数和对应字节数：

SET SERVEROUTPUT ON;
DECLARE
    v_total_bytes NUMBER;
    v_num_rows NUMBER;
    v_avg_row_size NUMBER;
BEGIN
    -- 遍历table_list里的每个表
    FOR table_rec IN (SELECT table_name, owner FROM table_list) LOOP
        -- 获取当前表的总占用字节数（只统计表段，排除索引等）
        SELECT SUM(bytes)
        INTO v_total_bytes
        FROM dba_extents
        WHERE owner = table_rec.owner
          AND segment_name = table_rec.table_name
          AND segment_type = 'TABLE'; -- 过滤表类型的段，避免干扰
        
        -- 动态执行COUNT(*)获取当前表的行数
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_rec.owner || '.' || table_rec.table_name
        INTO v_num_rows;
        
        -- 计算平均行大小，处理空表避免除以0
        IF v_num_rows > 0 THEN
            v_avg_row_size := v_total_bytes / v_num_rows;
            DBMS_OUTPUT.PUT_LINE('表: ' || table_rec.owner || '.' || table_rec.table_name 
                                 || ' 平均行大小: ' || ROUND(v_avg_row_size, 2) || ' 字节');
        ELSE
            DBMS_OUTPUT.PUT_LINE('表: ' || table_rec.owner || '.' || table_rec.table_name 
                                 || ' 无数据行，无法计算平均大小');
        END IF;
    END LOOP;
END;
/

如果你一定要用纯SQL实现（可以接受用ALL_TABLES里的统计行数，注意这个数值可能不是实时的），可以用关联查询来逐个匹配每个表的字节数和统计行数：

SELECT
    tl.owner,
    tl.table_name,
    COALESCE(de.total_bytes / at.num_rows, 0) AS avg_row_size
FROM table_list tl
LEFT JOIN (
    -- 按表分组计算总字节数
    SELECT
        owner,
        segment_name,
        SUM(bytes) AS total_bytes
    FROM dba_extents
    WHERE segment_type = 'TABLE'
    GROUP BY owner, segment_name
) de ON tl.owner = de.owner AND tl.table_name = de.segment_name
LEFT JOIN all_tables at ON tl.owner = at.owner AND tl.table_name = at.table_name
ORDER BY tl.owner, tl.table_name;

关键注意事项 #

• 确保你有DBA_EXTENTS和对应表的查询权限，否则会报错；
• 加上segment_type = 'TABLE'过滤，避免把索引、分区子段等无关的字节数算进去；
• 处理空表的情况，避免出现除以0的错误；
• 必须指定owner，防止不同用户下的同名表混淆。

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