如何获取到表的分区名称对应的数据分布情况

OceanBase 技术问题
,
#1

【产品名称】OceanBase

【产品版本】社区版3.1.1

【问题描述】rt，下面的SQL可以获取到表的分步情况，如何增加一列表的分区名称的信息？增加是否有意义，意义是什么？

# 需登录系统租户sys obclient -h127.1 -uroot@sys -P2881 -p -c -A oceanbase MySQL [oceanbase]> select tenant.tenant_name,meta.table_id, tab.table_name, partition_id,zone,svr_ip,svr_port, case when role=1 then ‘leader’ when role=2 then ‘follower’ else NULL end as role, tab.primary_zone from __all_virtual_meta_table meta inner join __all_tenant tenant on meta.tenant_id=tenant.tenant_id inner join __all_virtual_table tab on meta.tenant_id=tab.tenant_id and meta.table_id=tab.table_id where tenant.tenant_id=1001 order by tenant.tenant_name,table_name,partition_id,zone ;

#2

常用 SQL：OceanBase 社区版


[Q5] 查看某个实例（租户）下库表分区主副本的位置和大小

SELECT  t.tenant_id, a.tenant_name, t.table_name, d.database_name,  tg.tablegroup_name , t.part_num , t2.partition_id, t2.ZONE, t2.svr_ip , round(t2.data_size/1024/1024/1024) data_size_gb
	, a.primary_zone	  , IF(t.locality = '' OR t.locality IS NULL, a.locality, t.locality) AS locality	
FROM oceanbase.__all_tenant AS a  
	JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id  ) 
	JOIN oceanbase.__all_virtual_table AS t  ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)	
	JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id)  AND t2.ROLE IN (1) )
	LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and  t.tablegroup_id = tg.tablegroup_id) 
WHERE a.tenant_id IN (1006 ) AND  t.table_type IN (3)  
-- AND d.database_name = 'T_FUND60PUB' 
-- and table_name in ('BMSQL_HISTORY')
ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
;

备注：

  • role : 1 表示 主副本； 2 表示备副本
  • tablegroup_name 为空，表示没有用到表分组。


1 个赞
#3

登录到业务租户：

root@172.20.0.10 : test 10:52:58> show create table t3 ;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(c1)
(partition p0,
partition p1,
partition p2) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.048 sec)


root@172.20.0.10 : test 10:53:05> show tenant ;
+---------------------+
| Current_tenant_name |
+---------------------+
| obcp_t1             |
+---------------------+
1 row in set (0.006 sec)


#4

分区名称关联能获取到吗？这里是有partition_id。