Query information about a table group

After you create a table group, you can query its information, including the SHARDING attribute, tables in the table group, time when the table group was created, and location information of tables in the table group.

Query the SHARDING attribute of a table group

Query table group attributes and partition information about tables in the table group through views

You can query the CDB_OB_TABLEGROUPS view in the sys tenant and the DBA_OB_TABLEGROUPS view in a MySQL user tenant for the SHARDING attribute of a table group and the partition information about tables in the table group. A sample statement is as follows:

• sys tenant

  SELECT * FROM oceanbase.CDB_OB_TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';
  

  The query result is as follows:

  +-----------+-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
  | TENANT_ID | TABLEGROUP_NAME | PARTITIONING_TYPE | SUBPARTITIONING_TYPE | PARTITION_COUNT | DEF_SUBPARTITION_COUNT | PARTITIONING_KEY_COUNT | SUBPARTITIONING_KEY_COUNT | SHARDING |
  +-----------+-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
  |      1002 | tblgroup1       | NONE              | NONE                 |            NULL |                   NULL |                   NULL |                      NULL | ADAPTIVE |
  |      1004 | TBLGROUP1       | NONE              | NONE                 |            NULL |                   NULL |                   NULL |                      NULL | NONE     |
  +-----------+-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
  2 rows in set
  

• MySQL user tenant

  SELECT * FROM oceanbase.DBA_OB_TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';
  

  The query result is as follows:

  +-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
  | TABLEGROUP_NAME | PARTITIONING_TYPE | SUBPARTITIONING_TYPE | PARTITION_COUNT | DEF_SUBPARTITION_COUNT | PARTITIONING_KEY_COUNT | SUBPARTITIONING_KEY_COUNT | SHARDING |
  +-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
  | tblgroup1       | NONE              | NONE                 |            NULL |                   NULL |                   NULL |                      NULL | ADAPTIVE |
  +-----------------+-------------------+----------------------+-----------------+------------------------+------------------------+---------------------------+----------+
  1 row in set
  

In the query results above:

• TENANT_ID indicates the ID of the tenant to which the table group belongs.

• TABLEGROUP_NAME indicates the name of the table group.

• PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT, and SUBPARTITIONING_KEY_COUNT indicate the attributes of the table group with partition definitions in OceanBase Database V3.x. In OceanBase Database V4.2.1, the value of these attributes is NONE or NULL.

• SHARDING indicates the SHARDING attribute of the table group.

  • For a table group with the SHARDING attribute set to NONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.

  • For a table group with the SHARDING attribute set to PARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.

    A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

  • For a table group with the SHARDING attribute set to ADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.

    A table to be added to the table group must have the same partition definition and subpartition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

Query table group attributes and databases of tables in the table group through the SHOW TABLEGROUPS statement

You can also execute the SHOW TABLEGROUPS statement in the sys tenant or a MySQL user tenant to view the SHARDING attribute of a table group and the databases to which tables in the table group belong. A sample statement is as follows:

SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';

The query result is as follows:

+-----------------+------------+---------------+----------+
| Tablegroup_name | Table_name | Database_name | Sharding |
+-----------------+------------+---------------+----------+
| tblgroup1       | tbl3       | test          | ADAPTIVE |
+-----------------+------------+---------------+----------+
1 row in set

In the query result above:

• Tablegroup_name indicates the name of the table group.

• Table_name indicates the name of the table.

• Database_name indicates the name of the database to which the table belongs.

• Sharding indicates the SHARDING attribute of the table group.

  • For a table group with the SHARDING attribute set to NONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.

  • For a table group with the SHARDING attribute set to PARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.

    A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

  • For a table group with the SHARDING attribute set to ADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.

    A table to be added to the table group must have the same partition definition and subpartition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

Query tables in a table group

You can query the CDB_OB_TABLEGROUP_TABLES view in the sys tenant or the DBA_OB_TABLEGROUP_TABLES view in a MySQL user tenant for the tables in a table group and the databases to which the tables belong. A sample statement is as follows:

• sys tenant

  SELECT * FROM oceanbase.CDB_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup1';
  

  The query result is as follows:

  +-----------+-----------------+-------+------------+----------+
  | TENANT_ID | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
  +-----------+-----------------+-------+------------+----------+
  |      1002 | tblgroup1       | test  | tbl3       | ADAPTIVE |
  |      1004 | TBLGROUP1       | SYS   | TBL3       | NONE     |
  +-----------+-----------------+-------+------------+----------+
  2 rows in set
  

• MySQL user tenant

  SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup1';
  

  The query result is as follows:

  +-----------------+-----------+------------+----------+
  | TABLEGROUP_NAME | OWNER     | TABLE_NAME | SHARDING |
  +-----------------+-----------+------------+----------+
  | tblgroup1       | test      | tbl3       | ADAPTIVE |
  +-----------------+-----------+------------+----------+
  1 row in set
  

In the query results above:

• TENANT_ID indicates the ID of the tenant to which the table group belongs.

• TABLEGROUP_NAME indicates the name of the table group.

• OWNER indicates the database to which the table belongs.

• TABLE_NAME indicates the name of the table.

• SHARDING indicates the SHARDING attribute of the table group.

  • For a table group with the SHARDING attribute set to NONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.

  • For a table group with the SHARDING attribute set to PARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.

    A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

  • For a table group with the SHARDING attribute set to ADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.

    A table to be added to the table group must have the same partition definition and subpartition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

Query the creation time of a table group

You can query the CDB_OBJECTS view in the sys tenant or the DBA_OBJECTS view in a MySQL user tenant for information such as the creation time and last DDL operation of a table group. A sample statement is as follows:

• sys tenant

  SELECT OBJECT_NAME,CREATED, LAST_DDL_TIME FROM oceanbase.CDB_OBJECTS WHERE object_name = 'tblgroup1';
  

  A sample query result is as follows:

  +-------------+---------------------+---------------------+
  | OBJECT_NAME | CREATED             | LAST_DDL_TIME       |
  +-------------+---------------------+---------------------+
  | tblgroup1   | 2023-06-27 14:58:53 | 2023-06-27 15:15:54 |
  | TBLGROUP1   | 2023-06-27 15:25:30 | 2023-06-27 15:25:30 |
  +-------------+---------------------+---------------------+
  2 rows in set
  

• MySQL user tenant

  SELECT OBJECT_NAME,CREATED, LAST_DDL_TIME FROM oceanbase.DBA_OBJECTS WHERE object_name = 'tblgroup1';
  

  A sample query result is as follows:

  +-------------+---------------------+---------------------+
  | OBJECT_NAME | CREATED             | LAST_DDL_TIME       |
  +-------------+---------------------+---------------------+
  | tblgroup1   | 2023-06-27 14:58:53 | 2023-06-27 15:15:54 |
  +-------------+---------------------+---------------------+
  1 row in set
  

In the query results above:

• OBJECT_NAME indicates the object name, which is the name of the queried table group.
• CREATED indicates the time when the table group was created.
• LAST_DDL_TIME indicates the time when the last DDL operation was performed.

Query the location information about tables in a table group

You can query the DBA_OB_TABLE_LOCATIONS view in the sys tenant or a MySQL user tenant for the location information about tables in a table group, including the tablet ID of each partition, mappings between tablet IDs and log stream IDs, and location information of log stream replicas. A sample statement is as follows:

SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE tablegroup_name = 'tblgroup1';

The query result is as follows:

+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE  | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
| test          | tbl3       |   500089 | USER TABLE | p0             | mp0               | NULL       |          NULL |    200055 |  1001 | zone1 | xx.xx.xx.197   |     2882 | LEADER   | FULL         | NONE            |    500092 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p0             | mp0               | NULL       |          NULL |    200055 |  1001 | zone2 | xx.xx.xx.194   |     2882 | FOLLOWER | FULL         | NONE            |    500092 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p0             | mp1               | NULL       |          NULL |    200056 |  1001 | zone1 | xx.xx.xx.197   |     2882 | LEADER   | FULL         | NONE            |    500093 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p0             | mp1               | NULL       |          NULL |    200056 |  1001 | zone2 | xx.xx.xx.194   |     2882 | FOLLOWER | FULL         | NONE            |    500093 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p0             | mp2               | NULL       |          NULL |    200057 |  1001 | zone1 | xx.xx.xx.197   |     2882 | LEADER   | FULL         | NONE            |    500094 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p0             | mp2               | NULL       |          NULL |    200057 |  1001 | zone2 | xx.xx.xx.194   |     2882 | FOLLOWER | FULL         | NONE            |    500094 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p1             | mp3               | NULL       |          NULL |    200058 |  1001 | zone1 | xx.xx.xx.197   |     2882 | LEADER   | FULL         | NONE            |    500095 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p1             | mp3               | NULL       |          NULL |    200058 |  1001 | zone2 | xx.xx.xx.194   |     2882 | FOLLOWER | FULL         | NONE            |    500095 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p1             | mp4               | NULL       |          NULL |    200059 |  1001 | zone1 | xx.xx.xx.197   |     2882 | LEADER   | FULL         | NONE            |    500096 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p1             | mp4               | NULL       |          NULL |    200059 |  1001 | zone2 | xx.xx.xx.194   |     2882 | FOLLOWER | FULL         | NONE            |    500096 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p1             | mp5               | NULL       |          NULL |    200060 |  1001 | zone1 | xx.xx.xx.197   |     2882 | LEADER   | FULL         | NONE            |    500097 | tblgroup1       |        500083 | ADAPTIVE |
| test          | tbl3       |   500089 | USER TABLE | p1             | mp5               | NULL       |          NULL |    200060 |  1001 | zone2 | xx.xx.xx.194   |     2882 | FOLLOWER | FULL         | NONE            |    500097 | tblgroup1       |        500083 | ADAPTIVE |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
12 rows in set

In the query result above:

• DATABASE_NAME indicates the name of the database to which the table belongs.

• TABLE_NAME indicates the name of the table.

• TABLE_ID indicates the ID of the table.

• TABLE_TYPE indicates the type of the table. Valid values include:

  • USER TABLE: a user table.
  • SYSTEM TABLE: a system table, which is an index table created by the system.
  • LOB AUX TABLE: a LOB auxiliary table, which is created by the system to implement LOB columns.

• PARTITION_NAME indicates the name of the partition. For a non-partitioned table, the value is NULL by default.

• SUBPARTITION_NAME indicates the name of the subpartition. The value NULL indicates that no subpartition exists.

• INDEX_NAME indicates the name of the index table. If the table is not an index table, the value is NULL.

• DATA_TABLE_ID indicates the ID of the primary table. If the table is not an index table or LOB table, the value is NULL.

• TABLET_ID indicates the tablet ID of the partition.

• LS_ID indicates the ID of the log stream to which the table belongs.

• ZONE indicates the zone where the replica is located.

• SVR_IP indicates the IP address of the OBServer node where the replica is located.

• SVR_PORT indicates the port of the OBServer node where the replica is located.

• ROLE indicates the role of the log stream replica.

• REPLICA_TYPE indicates the type of the log stream replica. Valid values are the following ones:

  • FULL: a full-featured replica.
  • READONLY: a read-only replica.

• OBJECT_ID indicates the object ID. If the table is a non-partitioned table, the value is the ID of the table. If the table is a partitioned table, this field shows the ID of each partition in the table.

• TABLEGROUP_NAME indicates the name of the queried table group.

• TABLEGROUP_ID indicates the ID of the queried table group.

• SHARDING indicates the SHARDING attribute of the table group.

  • For a table group with the SHARDING attribute set to NONE, all partitions of all tables in the table group are aggregated on the same server, and no limitations are imposed on tables to be added to the table group.

  • For a table group with the SHARDING attribute set to PARTITION, the data of each table in the table group is scattered by partition. For a subpartitioned table in the table group, all subpartitions in each partition are aggregated together.

    A table to be added to the table group must have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

  • For a table group with the SHARDING attribute set to ADAPTIVE, the data of each table in the table group is adaptively scattered. In other words, data in partitioned tables is scattered by partition, and data in subpartitioned tables is scattered by subpartition in each partition.

    A table to be added to the table group must have the same partition definition and subpartition definition as existing tables in the table group, including the partitioning type, partition count, and partition value.

References

• Table groups

• Create a table group

• Add tables to a table group

• Modify the SHARDING attribute of a table group

• Manage tables in a table group

• Drop a table group