【产品名称】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 ;
常用 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 个赞
登录到业务租户:
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)
分区名称关联能获取到吗?这里是有partition_id。