In OceanBase (MySQL Mode), I know that I can set the parallel degree of a table or index using SQL commands like:
alter table my_table parallel 4;
alter index my_index parallel 2;
However, for compliance and governance reasons, we need to ensure that all tables and indexes have the default degree (i.e., no explicit parallelism).
In Oracle, I can query the DEGREE column from DBA_TABLES or DBA_INDEXES to check this. For example:
SELECT table_name, degree FROM dba_tables WHERE owner = 'MY_SCHEMA';
But in OceanBase, although those views (DBA_TABLES, DBA_INDEXES) do exist, the DEGREE column seems to always return null or default values, and doesn’t reflect what was actually set.
My question is:
Is there a supported way in OceanBase to query the actual parallel degree value of a table or index after it’s been set?
I’ve looked through the official docs and system views, but haven’t found a clear method to confirm the degree setting.
I’ve looked through the official docs and system views, but haven’t found a clear method to confirm the degree setting. I was expecting to find a way — either via a system view, SQL function, or any metadata query — to confirm that a table or index has a specific parallel degree set, similar to how it’s done in Oracle using the DEGREE column in DBA_TABLES or DBA_INDEXES.
The parallelism of the mysql tenant table can be checked through show create table or through internal views.
The parallelism of the mysql tenant index can be checked through internal views.
For example,
MySQL [sql_collect]> alter table t1 parallel 4;
Query OK, 0 rows affected (0.021 sec)
MySQL [sql_collect]> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
KEY `idx1` (`c1`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARALLEL 4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
MySQL [oceanbase]> select d.database_name as database_name, t.database_id as database_id, t.table_name as table_name, t.table_id as table_id, REGEXP_REPLACE(i.table_name, '__idx_(\\d)*_', '') as index_name, i.dop as dop from oceanbase.__all_table_v2 t join oceanbase.__all_table_v2 i on t.table_id = i.data_table_id JOIN oceanbase.__all_database d on t.database_id = d.database_id where d.database_name = 'jingshun0804' and t.table_name = 't1';
+---------------+-------------+------------+----------+------------+-----+
| database_name | database_id | table_name | table_id | index_name | dop |
+---------------+-------------+------------+----------+------------+-----+
| jingshun0804 | 1206 | t1 | 264812 | idx1 | 2 |
+---------------+-------------+------------+----------+------------+-----+