根据您的问题，您希望使用一条 SQL 或存储过程来统计一张二级分区表中每个分区的数据量。以下是一个可行的解决方案：
解决方案
方法 1：通过 EXPLAIN PARTITIONS 获取分区信息
OceanBase 支持通过 EXPLAIN PARTITIONS 查看分区表的分区信息，并结合查询语句统计每个分区的数据量。
EXPLAIN PARTITIONS SELECT * FROM tab_certoperate_log;
虽然 EXPLAIN PARTITIONS 只能展示分区的元信息，但可以结合以下方法进一步统计每个分区的数据量。
方法 2：直接查询每个分区的数据量
您可以使用如下 SQL 查询每个一级分区和二级分区的数据量。此方法需要手动构造查询语句或通过动态 SQL 实现。
SELECT
CONCAT(partition_name, 's', subpartition_name) AS partition_info,
COUNT(*) AS row_count
FROM
tab_certoperate_log PARTITION (p2018)
WHERE
SUBSTRING_INDEX(SUBSTRING_INDEX(partition_name, 'p', -1), 's', 1) = '2018'
GROUP BY
partition_name, subpartition_name
WITH ROLLUP;
-- 动态生成所有分区的查询
SELECT
CONCAT('p', YEAR(createtime) - 1, 'sm', MONTH(createtime) - 1) AS partition_info,
COUNT(*) AS row_count
FROM
tab_certoperate_log
GROUP BY
YEAR(createtime) - 1, MONTH(createtime) - 1;
上述 SQL 的逻辑是：
- 使用
YEAR(createtime) 和 MONTH(createtime) 提取分区和子分区的关键字段。
- 拼接分区名称（如
p2018sm1）。
- 统计每个分区的数据量。
方法 3：通过存储过程实现动态查询
如果分区较多，可以通过存储过程动态生成并执行查询语句。
DELIMITER $$
CREATE PROCEDURE count_partition_rows()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE year_val INT;
DECLARE month_val INT;
DECLARE cur CURSOR FOR
SELECT DISTINCT YEAR(createtime) - 1 AS year_val, MONTH(createtime) - 1 AS month_val
FROM tab_certoperate_log;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TEMPORARY TABLE IF NOT EXISTS partition_counts (
partition_info VARCHAR(50),
row_count BIGINT
);
TRUNCATE TABLE partition_counts;
OPEN cur;
read_loop: LOOP
FETCH cur INTO year_val, month_val;
IF done THEN
LEAVE read_loop;
END IF;
SET @partition_name = CONCAT('p', year_val, 'sm', month_val);
SET @sql = CONCAT('INSERT INTO partition_counts (partition_info, row_count)
SELECT ''', @partition_name, ''' AS partition_info, COUNT(*) AS row_count
FROM tab_certoperate_log PARTITION (p', year_val, ')
WHERE MONTH(createtime) - 1 = ', month_val);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
SELECT * FROM partition_counts ORDER BY partition_info;
END$$
DELIMITER ;
CALL count_partition_rows();
存储过程逻辑说明：
- 创建一个临时表
partition_counts 用于存储分区名称和数据量。
- 使用游标遍历表中的所有年份和月份组合。
- 动态生成 SQL 查询每个分区的数据量，并插入到临时表中。
- 最终查询临时表的结果。
方法 4：通过系统视图获取分区信息
OceanBase 提供了系统视图 oceanbase.DBA_OB_PARTITIONS 和 oceanbase.DBA_OB_SUBPARTITIONS，可以用来查询分区和子分区的信息。
SELECT
CONCAT(t1.partition_name, 's', t2.subpartition_name) AS partition_info,
t2.num_rows AS row_count
FROM
oceanbase.DBA_OB_PARTITIONS t1
JOIN
oceanbase.DBA_OB_SUBPARTITIONS t2
ON
t1.table_id = t2.table_id AND t1.partition_name = t2.partition_name
WHERE
t1.table_name = 'tab_certoperate_log';
依据文档库中的信息：
-
oceanbase.DBA_OB_PARTITIONS 包含分区的元信息。
-
oceanbase.DBA_OB_SUBPARTITIONS 包含子分区的元信息。
-
num_rows 字段表示分区或子分区的数据量。
总结
以上方法均可实现对分区数据量的统计。推荐使用 方法 4，因为它直接利用系统视图，无需额外计算，性能更优。如果需要动态生成查询语句，则可以选择 方法 3。
检索到的文档如下:
- 数据分布
- 分区概述
- 使用 OceanBase 数据库分区表进行水平拆分
- oceanbase.CDB_WR_SYSSTAT
- oceanbase.CDB_WR_STATNAME
- 数据表设计最佳实践
- OceanBase 术语
赞