【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】v4.3.5.bp5
【问题描述】
MySQL租户下，想通过数据字典来查询确认分区表上所有索引哪些是Global的，哪些是local。
已经找到相关文档，但都不能解决
https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207722
https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000450145
注意：我不需要基于原理去判断，所有不用重复讲原理，我就是想通过数据字典视图直接获取到结果。
select
e.tenant_name,
b.owner as database_name,
b.table_name,
a.object_id as index_table_id,
b.index_name,
group_concat(d.column_name order by d.column_position separator ', ') as index_columns,
case
when c.index_type in (1, 2) then ‘local’
when c.index_type in (3, 4) then ‘global’
end as indextype
from
cdb_objects a
join
cdb_indexes b on a.con_id = b.con_id and a.owner = b.owner and a.object_name = b.index_name
join
__all_virtual_table c on a.con_id=c.tenant_id and a.object_id = c.table_id
join
cdb_ind_columns d on b.con_id = d.con_id and b.owner = d.index_owner and b.table_name = d.table_name and b.index_name = d.index_name
join
dba_ob_tenants e on a.con_id=e.tenant_id
where
a.object_type like ‘%index%’
and e.tenant_name in (‘test1’,‘test7’)
and b.owner = ‘test’ – database_name
– and b.table_name = ‘t123’
group by
e.tenant_name,
b.owner,
b.table_name,
a.object_id,
b.index_name,
indextype
order by e.tenant_name,b.owner,b.table_name;
