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 setMySQL 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_IDindicates the ID of the tenant to which the table group belongs.TABLEGROUP_NAMEindicates the name of the table group.PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,DEF_SUBPARTITION_COUNT,PARTITIONING_KEY_COUNT, andSUBPARTITIONING_KEY_COUNTindicate 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 isNONEorNULL.SHARDINGindicates theSHARDINGattribute of the table group.For a table group with the
SHARDINGattribute set toNONE, 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
SHARDINGattribute set toPARTITION, 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
SHARDINGattribute set toADAPTIVE, 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_nameindicates the name of the table group.Table_nameindicates the name of the table.Database_nameindicates the name of the database to which the table belongs.Shardingindicates theSHARDINGattribute of the table group.For a table group with the
SHARDINGattribute set toNONE, 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
SHARDINGattribute set toPARTITION, 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
SHARDINGattribute set toADAPTIVE, 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 setMySQL 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_IDindicates the ID of the tenant to which the table group belongs.TABLEGROUP_NAMEindicates the name of the table group.OWNERindicates the database to which the table belongs.TABLE_NAMEindicates the name of the table.SHARDINGindicates theSHARDINGattribute of the table group.For a table group with the
SHARDINGattribute set toNONE, 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
SHARDINGattribute set toPARTITION, 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
SHARDINGattribute set toADAPTIVE, 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 setMySQL 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_NAMEindicates the object name, which is the name of the queried table group.CREATEDindicates the time when the table group was created.LAST_DDL_TIMEindicates 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_NAMEindicates the name of the database to which the table belongs.TABLE_NAMEindicates the name of the table.TABLE_IDindicates the ID of the table.TABLE_TYPEindicates 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_NAMEindicates the name of the partition. For a non-partitioned table, the value isNULLby default.SUBPARTITION_NAMEindicates the name of the subpartition. The valueNULLindicates that no subpartition exists.INDEX_NAMEindicates the name of the index table. If the table is not an index table, the value isNULL.DATA_TABLE_IDindicates the ID of the primary table. If the table is not an index table or LOB table, the value isNULL.TABLET_IDindicates the tablet ID of the partition.LS_IDindicates the ID of the log stream to which the table belongs.ZONEindicates the zone where the replica is located.SVR_IPindicates the IP address of the OBServer node where the replica is located.SVR_PORTindicates the port of the OBServer node where the replica is located.ROLEindicates the role of the log stream replica.REPLICA_TYPEindicates the type of the log stream replica. Valid values are the following ones:FULL: a full-featured replica.READONLY: a read-only replica.
OBJECT_IDindicates 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_NAMEindicates the name of the queried table group.TABLEGROUP_IDindicates the ID of the queried table group.SHARDINGindicates theSHARDINGattribute of the table group.For a table group with the
SHARDINGattribute set toNONE, 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
SHARDINGattribute set toPARTITION, 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
SHARDINGattribute set toADAPTIVE, 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.