Query the primary table of an index

If you have obtained the index name, you can query the CDB_INDEXES view (for the sys tenant), the information_schema.statistics view (for MySQL user tenants), or the DBA_INDEXES, ALL_INDEXES, or USER_INDEXES views (for Oracle user tenants) to obtain the primary table information corresponding to the index name. The method is as follows:

• Sys tenant

  In the sys tenant, you can use the following statement to query:

  SELECT CON_ID, TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM oceanbase.CDB_INDEXES WHERE CON_ID=tenant_id AND INDEX_NAME='index_name';
  

  For example, if the index name is tbl2_f_rl_idx1 in the tenant with tenant_id 1002, you can execute the following statement in the sys tenant:

  obclient> SELECT CON_ID, TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM oceanbase.CDB_INDEXES WHERE CON_ID=1002 AND INDEX_NAME='tbl2_f_rl_idx1';
  

  The query result is as follows:

  +--------+-------------+------------+----------------+
  | CON_ID | TABLE_OWNER | TABLE_NAME | INDEX_NAME     |
  +--------+-------------+------------+----------------+
  |   1002 | infotest    | tbl2_f_rl  | tbl2_f_rl_idx1 |
  +--------+-------------+------------+----------------+
  1 row in set
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query:

  obclient> SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_SCHEMA, INDEX_NAME FROM information_schema.statistics WHERE INDEX_NAME='index_name';
  

  In an Oracle-compatible user tenant, you can use the following statements to query:

  obclient> SELECT CON_ID, TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM SYS.DBA_INDEXES WHERE INDEX_NAME='index_name';
  

  obclient> SELECT CON_ID, TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM SYS.ALL_INDEXES WHERE INDEX_NAME='index_name';
  

  obclient> SELECT CON_ID, TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM SYS.USER_INDEXES WHERE INDEX_NAME='index_name';