【OceanBase 小知识】—— 如何查询表的数据量占用磁盘大小

SELECT /*+ query_timeout(30000000) */
    a.TENANT_ID,
    a.DATABASE_NAME,
    a.TABLE_NAME,
    a.TABLE_ID,
    SUM(
        CASE 
            WHEN b.nested_offset = 0 THEN IFNULL(b.data_block_count + b.index_block_count + b.linked_block_count, 0) * 2 * 1024 * 1024
            ELSE IFNULL(b.size, 0)
        END
    )  1024.0 / 1024 / 1024 AS data_size_in_GB
FROM oceanbase.CDB_OB_TABLE_LOCATIONS a
INNER JOIN oceanbase.__all_virtual_table_mgr b
ON a.svr_ip = b.svr_ip
AND a.svr_port = b.svr_port
AND a.tenant_id = b.tenant_id
AND a.LS_ID = b.LS_ID
AND a.TABLET_ID = b.TABLET_ID
WHERE a.role = 'LEADER'  
AND b.table_type >= 10
AND b.size > 0
AND a.TABLE_NAME NOT REGEXP '^__'
GROUP BY a.TABLE_ID;

SELECT /*+ query_timeout(30000000) */
    a.TENANT_ID,
    a.DATABASE_NAME,
    a.TABLE_NAME,
    a.TABLE_ID,
    SUM(
        CASE 
            WHEN b.nested_offset = 0 THEN IFNULL(b.data_block_count + b.index_block_count + b.linked_block_count, 0) * 2 * 1024 * 1024
            ELSE IFNULL(b.size, 0)
        END
    ) / 1024.0 / 1024 / 1024 AS data_size_in_GB
FROM oceanbase.CDB_OB_TABLE_LOCATIONS a
INNER JOIN oceanbase.__all_virtual_table_mgr b
ON a.svr_ip = b.svr_ip
AND a.svr_port = b.svr_port
AND a.tenant_id = b.tenant_id
AND a.LS_ID = b.LS_ID
AND a.TABLET_ID = b.TABLET_ID
WHERE a.role = 'LEADER'
AND a.tenant_id = ${租户ID}
AND b.table_type >= 10
AND b.size > 0
AND a.TABLE_NAME NOT REGEXP '^__'
GROUP BY a.TABLE_ID;