如何查询分区索引为 local 还是 global

全局索引的信息存放在表 cdb_indexes 里面，却未在表 cdb_part_indexes 里面，所以如何去区分哪些索引是全局索引呢？

• 方法1：

  当 dba/cdb_ob_table_locations 视图不存在 index_type 字段，或者查询 index_type 字段为 NULL 的时候，可以查询内部表 __all_virtual_table 的 index_type 字段来判断该索引情况，该方法适用于当前所有的 observer 4.x 版本。

  具体语句如下，可按需选择注释或者取消注释 database_name,table_name 以便查询需要的维度下的信息：

  select /*+ query_timeout(1000000000),parallel(2) */ 
      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
  join
      cdb_part_indexes f on a.object_name = f.index_name
  where
      a.object_type like '%index%'
      and e.tenant_name in ('test1','test7')
      and b.owner = 'test' -- database_name
      and b.table_name = f.table_name
  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;
  

• 方法2：

  当 dba/cdb_ob_table_locations 视图存在 index_type 字段，则可以直接通过查询该字段获取。该操作从 observer 4.2.5.1 版本开始支持。

  具体查询语句如下，可按需选择注释或者取消注释 database_name,table_name 以便查询需要的维度下的信息：

  select /*+ query_timeout(1000000000),parallel(2) */ 
      b.tenant_name,
      a.database_name, 
      b.table_name, 
      a.table_id as index_table_id,
      a.index_name, 
      group_concat(distinct c.column_name order by c.column_position separator ', ') as index_columns,
      a.index_type
  from
      (select distinct 
          t.table_id, 
          t.table_name,
          t.tenant_id,
          t.database_name,
          d.tenant_name
      from 
          cdb_ob_table_locations t
      join 
          dba_ob_tenants d
      on 
          t.tenant_id = d.tenant_id
      join
          cdb_part_indexes f
      on
          t.table_name=f.table_name      
      where 
          d.tenant_name in ('test9', 'test1')
          and t.table_type = 'user table'
          -- and t.database_name = 'test'
          -- and t.table_name = 't123'
      ) as b
  left join 
      cdb_ob_table_locations a
  on  
      a.data_table_id = b.table_id
      and a.tenant_id = b.tenant_id
      and a.database_name = b.database_name
  left join 
      cdb_ind_columns c
  on 
      a.tenant_id = c.con_id
      and a.database_name = c.index_owner
      and a.index_name = c.index_name
  group by 
      a.tenant_id, a.database_name, index_table_id
  order by 
      a.tenant_id, a.database_name,b.table_name;
  

适用版本

OceanBase 数据库 4.x 版本。