如何查询索引所在的主表

本文介绍在 OceanBase 数据库中如何查询索引所在的主表。

通过查询 __all_virtual_table 表获取索引所在主表

OceanBase 数据库中通过查询 __all_virtual_table 表获取索引实体表信息和索引表对应的用户数据表信息，示例如下。

1. 创建表。

   obclient [mysql]> create table test (id number(10),name varchar(50));
   Query OK, 0 rows affected (0.064 sec)
   

2. 创建索引。

   obclient [mysql]> create index indx_test_id on test(id);
   Query OK, 0 rows affected (0.544 sec)
   

3. 通过内部表 __all_virtual_table 获取 _idx 开头表的信息。

   obclient [mysql]> select tenant_id,data_table_id,table_name,table_id,database_id,table_type from oceanbase.__all_virtual_table where table_name like '%__idx%';
   

   输出结果如下。

   +-----------+------------------+---------------------------------------------------------+------------------+------------------+------------+
   | tenant_id | data_table_id    | table_name                                              | table_id         | database_id      | table_type |
   +-----------+------------------+---------------------------------------------------------+------------------+------------------+------------+
   |         1 |    1099511628090 | __idx_1099511628090_idx_data_table_id                   |    1099511637773 |    1099511627777 |          5 |
   |         1 |    1099511628058 | __idx_1099511628058_idx_data_table_id                   |    1099511637774 |    1099511627777 |          5 |
   |         1 |    1099511627890 | __idx_1099511627890_idx_data_table_id                   |    1099511637775 |    1099511627777 |          5 |
   |         1 |    1099511639791 | __idx_1099511639791_all_virtual_replica_task_i1         |    1099511642767 |    1099511627777 |          5 |
   |         1 |    1099511638807 | __idx_1099511638807_all_virtual_sql_audit_i1            |    1099511642768 |    1099511627777 |          5 |
   |         1 |    1099511638797 | __idx_1099511638797_all_virtual_sysstat_i1              |    1099511642769 |    1099511627777 |          5 |
   |         1 |    1099511638796 | __idx_1099511638796_all_virtual_sesstat_i1              |    1099511642770 |    1099511627777 |          5 |
   |         1 |    1099511638793 | __idx_1099511638793_all_virtual_system_event_i1         |    1099511642771 |    1099511627777 |          5 |
   |         1 |    1099511638791 | __idx_1099511638791_all_virtual_session_wait_history_i1 |    1099511642772 |    1099511627777 |          5 |
   |         1 |    1099511638790 | __idx_1099511638790_all_virtual_session_wait_i1         |    1099511642773 |    1099511627777 |          5 |
   |         1 |    1099511638789 | __idx_1099511638789_all_virtual_session_event_i1        |    1099511642774 |    1099511627777 |          5 |
   |         1 |    1099511638779 | __idx_1099511638779_all_virtual_plan_cache_stat_i1      |    1099511642775 |    1099511627777 |          5 |
   |         1 |    1099511642820 | __idx_1099511642820_all_virtual_system_event_i1         |    1099511647769 |    1099511627782 |          5 |
   |         1 |    1099511642819 | __idx_1099511642819_all_virtual_sysstat_i1              |    1099511647770 |    1099511627782 |          5 |
   |         1 |    1099511642818 | __idx_1099511642818_all_virtual_sesstat_i1              |    1099511647771 |    1099511627782 |          5 |
   |         1 |    1099511642813 | __idx_1099511642813_all_virtual_session_wait_history_i1 |    1099511647772 |    1099511627782 |          5 |
   |         1 |    1099511642812 | __idx_1099511642812_all_virtual_session_wait_i1         |    1099511647773 |    1099511627782 |          5 |
   |         1 |    1099511642810 | __idx_1099511642810_all_virtual_plan_cache_stat_i1      |    1099511647774 |    1099511627782 |          5 |
   |         1 |    1099511642785 | __idx_1099511642785_all_virtual_sql_audit_i1            |    1099511647775 |    1099511627782 |          5 |
   |         1 |    1099511677784 | __idx_1099511677784_c1                                  |    1099511677785 |    1099511627779 |          5 |
   |         1 |    1099511677786 | __idx_1099511677786_indx_test_id                        |    1099511677787 |    1099511627779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_UK_login_id        | 1100611139453807 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_UK_email           | 1100611139453808 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_FK_added_by_id     | 1100611139453809 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_FK_upd_by_id       | 1100611139453810 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_cr_time            | 1100611139453811 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_up_time            | 1100611139453812 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_name               | 1100611139453813 | 1100611139403779 |          5 |
   |      1001 | 1100611139453806 | __idx_1100611139453806_x_portal_user_email              | 1100611139453814 | 1100611139403779 |          5 |
   |      1001 | 1100611139453815 | __idx_1100611139453815_x_role_UK_name                   | 1100611139453816 | 1100611139403779 |          5 |
   |      1001 | 1100611139453815 | __idx_1100611139453815_idx_01_x_role                    | 1100611139453817 | 1100611139403779 |          5 |
   |      1001 | 1100611139453815 | __idx_1100611139453815_idx_02_x_role                    | 1100611139453818 | 1100611139403779 |          5 |
   |      1002 | 1101710651081555 | __idx_1101710651081555_TEST_INDEX                       | 1101710651081557 | 1101710651031558 |          5 |
   |      1002 | 1101710651081559 | __idx_1101710651081559_INDEX1                           | 1101710651081560 | 1101710651031558 |          5 |
   |      1002 | 1101710651081561 | __idx_1101710651081561_IDX_14                           | 1101710651081563 | 1101710651031558 |          5 |
   +-----------+------------------+---------------------------------------------------------+------------------+------------------+------------+
   35 rows in set (0.020 sec)
   

4. 通过步骤 3 中的 table_id 值获取 table_name 索引实体表信息。

   obclient [mysql]> select tenant_id,data_table_id,table_name,table_id,database_id,table_type from oceanbase.__all_virtual_table where table_id ='1099511677787';
   

   输出结果如下。

   +-----------+---------------+----------------------------------+---------------+---------------+------------+
   | tenant_id | data_table_id | table_name                       | table_id      | database_id   | table_type |
   +-----------+---------------+----------------------------------+---------------+---------------+------------+
   |         1 | 1099511677786 | __idx_1099511677786_indx_test_id | 1099511677787 | 1099511627779 |          5 |
   +-----------+---------------+----------------------------------+---------------+---------------+------------+
   1 row in set (0.004 sec)
   

5. 通过步骤 3 中 data_table_id 字段为该索引表对应的用户数据表的 table_id 值获取用户表信息。

   obclient [mysql]> select tenant_id,table_name,table_id,database_id,table_type from oceanbase.__all_virtual_table where data_table_id ='1099511677786';
   

   输出结果如下。

   +-----------+------------+---------------+---------------+------------+
   | tenant_id | table_name | table_id      | database_id   | table_type |
   +-----------+------------+---------------+---------------+------------+
   |         1 | test       | 1099511677786 | 1099511627779 |          3 |
   +-----------+------------+---------------+---------------+------------+
   1 row in set (0.013 sec)
   

   说明

   在内部表 __all_virual_table 中，当查询结果中的 table_name 以 "_idx" 开头时（通常为 __idx + 主表 table_id + 索引名），通常表示该查表为某个用户表对应的索引表，此时 table_type 字段的值应该为5（0：系统表，1：系统视图，2：虚拟表，3：用户表，4：用户视图，5：索引表，6：临时表，7：物化视图，8：会话级别 oracle 兼容模式下的临时表，9：事务级别 oracle 兼容模式下的临时表），data_table_id 字段为该索引表对应的用户数据表的 table_id，因此可以利用该字段查询索引表的信息。data_table_id 的值就是主表 ID 的值。

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。