This guide provides a complete example of how to collect, query, export, restore, and manage table statistics in OceanBase Database, covering the entire process from creating table structures to managing statistics history.
The following examples are based on the MySQL mode. The operations are fully compatible in the Oracle mode, so no further details are provided.
Step 1: Create a test table and insert data
obclient> CREATE TABLE test.t_subpart (c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1)
SUBPARTITION BY HASH(c2)
SUBPARTITION TEMPLATE
(
SUBPARTITION sp0,
SUBPARTITION sp1,
SUBPARTITION sp2
)
PARTITIONS 3;
Query OK, 0 rows affected
obclient> INSERT INTO test.t_subpart WITH RECURSIVE numbers AS (SELECT 0 AS n UNION ALL
SELECT n + 1 FROM numbers WHERE n < 999
)
SELECT n, MOD(n, 1000), MOD(n, 2000) FROM numbers;
Query OK, 10000 rows affected
Records: 10000 Duplicates: 0 Warnings: 0
obclient> SELECT COUNT(*) FROM test.t_subpart;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set
Step 2: Collect table statistics
obclient> CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't_subpart', method_opt=>'FOR ALL COLUMNS SIZE 5', granularity=>'ALL', degree=>4);
Query OK, 0 rows affected
Step 3: Query statistics
Query table-level statistics.
obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM oceanbase.DBA_TAB_STATISTICS WHERE TABLE_NAME = 'T_SUBPART' AND OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | +------------+--------------+----------+-------------+ | t_subpart | PARTITION | 333 | 60 | | t_subpart | PARTITION | 333 | 60 | | t_subpart | PARTITION | 334 | 60 | | t_subpart | SUBPARTITION | 0 | 0 | | t_subpart | SUBPARTITION | 0 | 0 | | t_subpart | SUBPARTITION | 0 | 0 | | t_subpart | SUBPARTITION | 0 | 0 | | t_subpart | SUBPARTITION | 0 | 0 | | t_subpart | SUBPARTITION | 0 | 0 | | t_subpart | SUBPARTITION | 333 | 60 | | t_subpart | SUBPARTITION | 333 | 60 | | t_subpart | SUBPARTITION | 334 | 60 | | t_subpart | TABLE | 1000 | 60 | +------------+--------------+----------+-------------+ 13 rows in setQuery column-level statistics
obclient> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM FROM oceanbase.DBA_TAB_COL_STATISTICS WHERE table_name ='T_SUBPART' AND OWNER = 'TEST' ORDER BY 1, 2, 3; ++------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+-------------+-----------+ | TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | SAMPLE_SIZE | AVG_COL_LEN | HISTOGRAM | +------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+-------------+-----------+ | t_subpart | c1 | 1012 | 0 | 999 | 0 | 0 | 5 | 1000 | 20 | HYBRID | | t_subpart | c2 | 1012 | 0 | 999 | 0 | 0 | 5 | 1000 | 20 | HYBRID | | t_subpart | c3 | 1012 | 0 | 999 | 0 | 0 | 5 | 1000 | 20 | HYBRID | +------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+-------------+-----------+ 3 rows in set obclient> SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE, ENDPOINT_ACTUAL_VALUE_RAW, ENDPOINT_REPEAT_COUNT from oceanbase.DBA_PART_HISTOGRAMS WHERE table_name ='T_SUBPART' AND OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+ | TABLE_NAME | PARTITION_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_VALUE | ENDPOINT_ACTUAL_VALUE | ENDPOINT_ACTUAL_VALUE_RAW | ENDPOINT_REPEAT_COUNT | +------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+ | t_subpart | p0 | c1 | 1 | NULL | 0 | 04053F0000 | 1 | | t_subpart | p0 | c1 | 334 | NULL | 999 | 04053F00E707 | 1 | | t_subpart | p0 | c1 | 85 | NULL | 252 | 04053F00FC01 | 1 | | t_subpart | p0 | c1 | 252 | NULL | 753 | 04053F00F105 | 1 | | t_subpart | p0 | c1 | 169 | NULL | 504 | 04053F00F803 | 1 | | t_subpart | p0 | c2 | 85 | NULL | 252 | 04053F00FC01 | 1 | | t_subpart | p0 | c2 | 1 | NULL | 0 | 04053F0000 | 1 | | t_subpart | p0 | c2 | 169 | NULL | 504 | 04053F00F803 | 1 | | t_subpart | p0 | c2 | 252 | NULL | 753 | 04053F00F105 | 1 | | t_subpart | p0 | c2 | 334 | NULL | 999 | 04053F00E707 | 1 | | t_subpart | p0 | c3 | 334 | NULL | 999 | 04053F00E707 | 1 | | t_subpart | p0 | c3 | 1 | NULL | 0 | 04053F0000 | 1 | | t_subpart | p0 | c3 | 85 | NULL | 252 | 04053F00FC01 | 1 | | t_subpart | p0 | c3 | 169 | NULL | 504 | 04053F00F803 | 1 | | t_subpart | p0 | c3 | 252 | NULL | 753 | 04053F00F105 | 1 | | t_subpart | p1 | c1 | 251 | NULL | 751 | 04053F00EF05 | 1 | | t_subpart | p1 | c1 | 333 | NULL | 997 | 04053F00E507 | 1 | | t_subpart | p1 | c1 | 168 | NULL | 502 | 04053F00F603 | 1 | | t_subpart | p1 | c1 | 85 | NULL | 253 | 04053F00FD01 | 1 | | t_subpart | p1 | c1 | 1 | NULL | 1 | 04053F0001 | 1 | | t_subpart | p1 | c2 | 333 | NULL | 997 | 04053F00E507 | 1 | | t_subpart | p1 | c2 | 251 | NULL | 751 | 04053F00EF05 | 1 | | t_subpart | p1 | c2 | 1 | NULL | 1 | 04053F0001 | 1 | | t_subpart | p1 | c2 | 168 | NULL | 502 | 04053F00F603 | 1 | | t_subpart | p1 | c2 | 85 | NULL | 253 | 04053F00FD01 | 1 | | t_subpart | p1 | c3 | 333 | NULL | 997 | 04053F00E507 | 1 | | t_subpart | p1 | c3 | 251 | NULL | 751 | 04053F00EF05 | 1 | | t_subpart | p1 | c3 | 168 | NULL | 502 | 04053F00F603 | 1 | | t_subpart | p1 | c3 | 85 | NULL | 253 | 04053F00FD01 | 1 | | t_subpart | p1 | c3 | 1 | NULL | 1 | 04053F0001 | 1 | | t_subpart | p2 | c1 | 251 | NULL | 752 | 04053F00F005 | 1 | | t_subpart | p2 | c1 | 1 | NULL | 2 | 04053F0002 | 1 | | t_subpart | p2 | c1 | 85 | NULL | 254 | 04053F00FE01 | 1 | | t_subpart | p2 | c1 | 168 | NULL | 503 | 04053F00F703 | 1 | | t_subpart | p2 | c1 | 333 | NULL | 998 | 04053F00E607 | 1 | | t_subpart | p2 | c2 | 85 | NULL | 254 | 04053F00FE01 | 1 | | t_subpart | p2 | c2 | 333 | NULL | 998 | 04053F00E607 | 1 | | t_subpart | p2 | c2 | 1 | NULL | 2 | 04053F0002 | 1 | | t_subpart | p2 | c2 | 251 | NULL | 752 | 04053F00F005 | 1 | | t_subpart | p2 | c2 | 168 | NULL | 503 | 04053F00F703 | 1 | | t_subpart | p2 | c3 | 85 | NULL | 254 | 04053F00FE01 | 1 | | t_subpart | p2 | c3 | 168 | NULL | 503 | 04053F00F703 | 1 | | t_subpart | p2 | c3 | 1 | NULL | 2 | 04053F0002 | 1 | | t_subpart | p2 | c3 | 251 | NULL | 752 | 04053F00F005 | 1 | | t_subpart | p2 | c3 | 333 | NULL | 998 | 04053F00E607 | 1 |
+------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+ 45 rows in set
## Step 4: Create a statistics user table
```sql
obclient> CALL DBMS_STATS.CREATE_STAT_TABLE('test', 'test_stat');
Query OK, 0 rows affected
obclient> DESC test.test_stat;
+----------+---------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+------+---------+-------+
| STATID | varchar(128) | YES | | NULL | |
| TYPE | char(1) | YES | | NULL | |
| VERSION | decimal(10,0) | YES | | NULL | |
| FLAGS | decimal(10,0) | YES | | NULL | |
| C1 | varchar(128) | YES | | NULL | |
| C2 | varchar(128) | YES | | NULL | |
| C3 | varchar(128) | YES | | NULL | |
| C4 | varchar(128) | YES | | NULL | |
| C5 | varchar(128) | YES | | NULL | |
| C6 | varchar(128) | YES | | NULL | |
| N1 | decimal(10,0) | YES | | NULL | |
| N2 | double | YES | | NULL | |
| N3 | decimal(10,0) | YES | | NULL | |
| N4 | decimal(10,0) | YES | | NULL | |
| N5 | decimal(10,0) | YES | | NULL | |
| N6 | decimal(10,0) | YES | | NULL | |
| N7 | decimal(10,0) | YES | | NULL | |
| N8 | decimal(10,0) | YES | | NULL | |
| N9 | decimal(10,0) | YES | | NULL | |
| N10 | decimal(10,0) | YES | | NULL | |
| N11 | decimal(10,0) | YES | | NULL | |
| N12 | decimal(10,0) | YES | | NULL | |
| N13 | decimal(10,0) | YES | | NULL | |
| D1 | timestamp(6) | YES | | NULL | |
| T1 | timestamp | YES | | NULL | |
| R1 | text | YES | | NULL | |
| R2 | text | YES | | NULL | |
| R3 | text | YES | | NULL | |
| CH1 | varchar(1000) | YES | | NULL | |
| CL1 | blob | YES | | NULL | |
| BL1 | blob | YES | | NULL | |
| OB_SPEC1 | decimal(10,0) | YES | | NULL | |
| OB_SPEC2 | decimal(10,0) | YES | | NULL | |
| OB_SPEC3 | varchar(4096) | YES | | NULL | |
+----------+---------------+------+------+---------+-------+
34 rows in set
Step 5: Export and import statistics
Export statistics to a user table.
obclient> CALL DBMS_STATS.EXPORT_TABLE_STATS('test', 't_subpart', stattab=>'test_stat', statown=>'test'); Query OK, 0 rows affected obclient> SELECT COUNT(*) FROM test.test_stat; +----------+ | COUNT(*) | +----------+ | 136 | +----------+ 1 row in setDelete statistics.
obclient> CALL DBMS_STATS.DELETE_TABLE_STATS('test', 't_subpart'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_SUBPART' and OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | +------------+--------------+----------+-------------+ | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | TABLE | NULL | NULL | +------------+--------------+----------+-------------+ 13 rows in setSet statistics.
obclient> CALL DBMS_STATS.SET_TABLE_STATS('test', 't_subpart', numrows=>10000); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_SUBPART' AND OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | +------------+--------------+----------+-------------+ | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | TABLE | 10000 | 0 | +------------+--------------+----------+-------------+ 13 rows in set
Step 6: Lock and unlock statistics
Lock statistics.
obclient> CALL DBMS_STATS.LOCK_TABLE_STATS('test', 't_subpart'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_SUBPART' and OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+-----------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED | +------------+--------------+----------+-------------+-----------------+ | T_SUBPART | PARTITION | 0 | 0 | ALL | | T_SUBPART | PARTITION | 0 | 0 | ALL | | T_SUBPART | PARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | TABLE | 10000 | 0 | ALL | +------------+--------------+----------+-------------+-----------------+ 13 rows in set obclient> CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't_subpart', method_opt=>'FOR ALL COLUMNS SIZE 5', granularity=>'ALL', degree=>4); ERROR 5935 (HY000): object statistics are lockedUnlock statistics.
obclient> CALL DBMS_STATS.UNLOCK_TABLE_STATS('test', 't_subpart'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_SUBPART' AND OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+-----------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED | +------------+--------------+----------+-------------+-----------------+ | T_SUBPART | PARTITION | 0 | 0 | NULL | | T_SUBPART | PARTITION | 0 | 0 | NULL | | T_SUBPART | PARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | TABLE | 10000 | 0 | NULL | +------------+--------------+----------+-------------+-----------------+ 13 rows in set
Step 7: Manage statistics history
Import statistics.
obclient> CALL DBMS_STATS.IMPORT_TABLE_STATS('test', 't_subpart', stattab=>'test_stat', statown=>'test'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_SUBPART' AND OWNER = 'TEST' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+-----------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED | +------------+--------------+----------+-------------+-----------------+ | t_subpart | PARTITION | 333 | 60 | NULL | | t_subpart | PARTITION | 333 | 60 | NULL | | t_subpart | PARTITION | 334 | 60 | NULL | | t_subpart | SUBPARTITION | 0 | 0 | NULL | | t_subpart | SUBPARTITION | 0 | 0 | NULL | | t_subpart | SUBPARTITION | 0 | 0 | NULL | | t_subpart | SUBPARTITION | 0 | 0 | NULL | | t_subpart | SUBPARTITION | 0 | 0 | NULL | | t_subpart | SUBPARTITION | 0 | 0 | NULL | | t_subpart | SUBPARTITION | 333 | 60 | NULL | | t_subpart | SUBPARTITION | 333 | 60 | NULL | | t_subpart | SUBPARTITION | 334 | 60 | NULL | | t_subpart | TABLE | 1000 | 60 | NULL | +------------+--------------+----------+-------------+-----------------+ 13 rows in setRestore historical statistics.
obclient> SELECT * FROM oceanbase.DBA_TAB_STATS_HISTORY WHERE table_name = 'T_SUBPART' AND OWNER = 'TEST' ORDER BY STATS_UPDATE_TIME; +-------+------------+----------------+-------------------+----------------------------+ | OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | STATS_UPDATE_TIME | +-------+------------+----------------+-------------------+----------------------------+ | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | NULL | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | NULL | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | NULL | NULL | 2025-06-25 16:44:51.248090 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | NULL | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | NULL | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | NULL | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:46:52.326696 | +-------+------------+----------------+-------------------+----------------------------+ 66 rows in set obclient> CALL DBMS_STATS.RESTORE_TABLE_STATS('test', 't_subpart', STR_TO_DATE('2025-06-18 22:03:22', '%Y-%m-%d %H:%i:%s')); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN, STATTYPE_LOCKED FROM oceanbase.DBA_TAB_STATISTICS WHERE table_name = 'T_SUBPART' and OWNER = 'TEST' ORDER BY 1, 2, 3; +-------+------------+----------------+-------------------+----------------------------+ | OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | STATS_UPDATE_TIME | +-------+------------+----------------+-------------------+----------------------------+ | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | NULL | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | NULL | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | NULL | NULL | 2025-06-25 16:44:51.248090 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | NULL | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | NULL | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | NULL | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:46:52.326696 | +-------+------------+----------------+-------------------+----------------------------+ 66 rows in setDelete historical statistics.
obclient> CALL DBMS_STATS.PURGE_STATS(STR_TO_DATE('2025-06-18 22:03:22', '%Y-%m-%d %H:%i:%s')); Query OK, 0 rows affected obclient> SELECT * FROM oceanbase.DBA_TAB_STATS_HISTORY WHERE table_name = 'T_SUBPART' AND OWNER = 'TEST' ORDER BY STATS_UPDATE_TIME; +-------+------------+----------------+-------------------+----------------------------+ | OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | STATS_UPDATE_TIME | +-------+------------+----------------+-------------------+----------------------------+ | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:36:19.921257 | | test | t_subpart | NULL | NULL | 2025-06-25 16:36:19.921257 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p1 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:44:21.186757 | | test | t_subpart | p0 | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:44:21.186757 | | test | t_subpart | NULL | NULL | 2025-06-25 16:44:21.186757 | | test | t_subpart | NULL | NULL | 2025-06-25 16:44:51.248090 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:45:20.504647 | | test | t_subpart | NULL | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p2 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:45:20.504647 | | test | t_subpart | p0 | NULL | 2025-06-25 16:45:20.504647 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | NULL | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:46:24.723984 | | test | t_subpart | p0 | NULL | 2025-06-25 16:46:24.723984 | | test | t_subpart | NULL | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp0 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp0 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | p2ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p2 | NULL | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p1 | p1ssp2 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp1 | 2025-06-25 16:46:52.326696 | | test | t_subpart | p0 | p0ssp0 | 2025-06-25 16:46:52.326696 | +-------+------------+----------------+-------------------+----------------------------+ 66 rows in set