Examples|V4.1.0| docs|Distributed Database

Examples

Last Updated：2023-07-28 02:55:42 Updated

This topic provides examples of statistics collection.

Prepare data, create a table, and insert data into the table.

obclient> CREATE TABLE 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 t_subpart SELECT LEVEL-1, MOD(LEVEL-1, 1000), MOD(LEVEL-1, 2000) FROM DUAL CONNECT BY LEVEL <= 10000;
Query OK, 10000 rows affected
Records: 10000  Duplicates: 0  Warnings: 0

obclient> SELECT COUNT(*) FROM t_subpart;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set

Collect 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

Query the statistics.

obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM 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    |     2504 |          60 |
| T_SUBPART  | PARTITION    |     2530 |          60 |
| T_SUBPART  | PARTITION    |     4966 |          60 |
| T_SUBPART  | SUBPARTITION |      549 |          60 |
| T_SUBPART  | SUBPARTITION |      560 |          60 |
| T_SUBPART  | SUBPARTITION |      829 |          60 |
| T_SUBPART  | SUBPARTITION |      830 |          60 |
| T_SUBPART  | SUBPARTITION |     1122 |          60 |
| T_SUBPART  | SUBPARTITION |     1125 |          60 |
| T_SUBPART  | SUBPARTITION |     1130 |          60 |
| T_SUBPART  | SUBPARTITION |     1141 |          60 |
| T_SUBPART  | SUBPARTITION |     2714 |          60 |
| T_SUBPART  | TABLE        |    10000 |          60 |
+------------+--------------+----------+-------------+
13 rows in set

obclient> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM FROM 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          |        10191 | 0         | 9999       | .000097999999999999997 |         0 |           5 |       10000 |          20 | HYBRID    |
| T_SUBPART  | C2          |         1031 | 0         | 999        |  .00097000000000000005 |         0 |           5 |       10000 |          20 | HYBRID    |
| T_SUBPART  | C3          |         1973 | 0         | 1999       |  .00050699999999999996 |         0 |           5 |       10000 |          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 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          |             634 |           NULL | 2468                  | 2468                      |                     1 |
| T_SUBPART  | P0             | C1          |               1 |           NULL | 2                     | 2                         |                     1 |
| T_SUBPART  | P0             | C1          |            2530 |           NULL | 9998                  | 9998                      |                     1 |
| T_SUBPART  | P0             | C1          |            1267 |           NULL | 5010                  | 5010                      |                     1 |
| T_SUBPART  | P0             | C1          |            1899 |           NULL | 7555                  | 7555                      |                     1 |
| T_SUBPART  | P0             | C2          |               4 |           NULL | 0                     | 0                         |                     4 |
| T_SUBPART  | P0             | C2          |             636 |           NULL | 245                   | 245                       |                     1 |
| T_SUBPART  | P0             | C2          |            1269 |           NULL | 496                   | 496                       |                     3 |
| T_SUBPART  | P0             | C2          |            1901 |           NULL | 753                   | 753                       |                     3 |
| T_SUBPART  | P0             | C2          |            2530 |           NULL | 999                   | 999                       |                     3 |
| T_SUBPART  | P0             | C3          |            1899 |           NULL | 1493                  | 1493                      |                     1 |
| T_SUBPART  | P0             | C3          |            2530 |           NULL | 1999                  | 1999                      |                     1 |
| T_SUBPART  | P0             | C3          |            1267 |           NULL | 1004                  | 1004                      |                     2 |
| T_SUBPART  | P0             | C3          |             634 |           NULL | 502                   | 502                       |                     1 |
| T_SUBPART  | P0             | C3          |               1 |           NULL | 0                     | 0                         |                     1 |
| T_SUBPART  | P1             | C1          |               1 |           NULL | 0                     | 0                         |                     1 |
| T_SUBPART  | P1             | C1          |            1243 |           NULL | 2531                  | 2531                      |                     1 |
| T_SUBPART  | P1             | C1          |            2485 |           NULL | 5028                  | 5028                      |                     1 |
| T_SUBPART  | P1             | C1          |            3726 |           NULL | 7459                  | 7459                      |                     1 |
| T_SUBPART  | P1             | C1          |            4966 |           NULL | 9999                  | 9999                      |                     1 |
| T_SUBPART  | P1             | C2          |            4966 |           NULL | 999                   | 999                       |                     4 |
| T_SUBPART  | P1             | C2          |            3730 |           NULL | 744                   | 744                       |                     3 |
| T_SUBPART  | P1             | C2          |            2489 |           NULL | 498                   | 498                       |                     6 |
| T_SUBPART  | P1             | C2          |               6 |           NULL | 0                     | 0                         |                     6 |
| T_SUBPART  | P1             | C2          |            1248 |           NULL | 245                   | 245                       |                     5 |
| T_SUBPART  | P1             | C3          |               4 |           NULL | 0                     | 0                         |                     4 |
| T_SUBPART  | P1             | C3          |            1246 |           NULL | 507                   | 507                       |                     2 |
| T_SUBPART  | P1             | C3          |            2490 |           NULL | 1012                  | 1012                      |                     5 |
| T_SUBPART  | P1             | C3          |            3729 |           NULL | 1502                  | 1502                      |                     2 |
| T_SUBPART  | P1             | C3          |            4966 |           NULL | 1999                  | 1999                      |                     3 |
| T_SUBPART  | P2             | C1          |            2504 |           NULL | 9995                  | 9995                      |                     1 |
| T_SUBPART  | P2             | C1          |            1879 |           NULL | 7535                  | 7535                      |                     1 |
| T_SUBPART  | P2             | C1          |            1253 |           NULL | 4933                  | 4933                      |                     1 |
| T_SUBPART  | P2             | C1          |             627 |           NULL | 2489                  | 2489                      |                     1 |
| T_SUBPART  | P2             | C1          |               1 |           NULL | 3                     | 3                         |                     1 |
| T_SUBPART  | P2             | C2          |               3 |           NULL | 1                     | 1                         |                     3 |
| T_SUBPART  | P2             | C2          |             629 |           NULL | 267                   | 267                       |                     6 |
| T_SUBPART  | P2             | C2          |            1256 |           NULL | 509                   | 509                       |                     2 |
| T_SUBPART  | P2             | C2          |            1882 |           NULL | 758                   | 758                       |                     3 |
| T_SUBPART  | P2             | C2          |            2504 |           NULL | 999                   | 999                       |                     3 |
| T_SUBPART  | P2             | C3          |               1 |           NULL | 1                     | 1                         |                     1 |
| T_SUBPART  | P2             | C3          |             627 |           NULL | 488                   | 488                       |                     1 |
| T_SUBPART  | P2             | C3          |            1253 |           NULL | 974                   | 974                       |                     2 |
| T_SUBPART  | P2             | C3          |            1879 |           NULL | 1502                  | 1502                      |                     3 |
| T_SUBPART  | P2             | C3          |            2504 |           NULL | 1999                  | 1999                      |                     1 |
+------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
45 rows in set

Prepare the statistics user table.

obclient> CALL dbms_stats.create_stat_table('test', 'test_stat');
Query OK, 0 rows affected

obclient> DESC test_stat;
+----------+-----------------------------+------+-----+---------+-------+
| FIELD    | TYPE                        | NULL | KEY | DEFAULT | EXTRA |
+----------+-----------------------------+------+-----+---------+-------+
| STATID   | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| TYPE     | CHAR(1)                     | YES  | NULL | NULL    | NULL  |
| VERSION  | NUMBER                      | YES  | NULL | NULL    | NULL  |
| FLAGS    | NUMBER                      | YES  | NULL | NULL    | NULL  |
| C1       | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| C2       | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| C3       | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| C4       | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| C5       | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| C6       | VARCHAR2(128)               | YES  | NULL | NULL    | NULL  |
| N1       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N2       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N3       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N4       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N5       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N6       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N7       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N8       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N9       | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N10      | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N11      | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N12      | NUMBER                      | YES  | NULL | NULL    | NULL  |
| N13      | NUMBER                      | YES  | NULL | NULL    | NULL  |
| D1       | DATE                        | YES  | NULL | NULL    | NULL  |
| T1       | TIMESTAMP(6) WITH TIME ZONE | YES  | NULL | NULL    | NULL  |
| R1       | RAW(1000)                   | YES  | NULL | NULL    | NULL  |
| R2       | RAW(1000)                   | YES  | NULL | NULL    | NULL  |
| R3       | RAW(1000)                   | YES  | NULL | NULL    | NULL  |
| CH1      | VARCHAR2(1000)              | YES  | NULL | NULL    | NULL  |
| CL1      | CLOB                        | YES  | NULL | NULL    | NULL  |
| BL1      | BLOB                        | YES  | NULL | NULL    | NULL  |
| OB_SPEC1 | NUMBER                      | YES  | NULL | NULL    | NULL  |
| OB_SPEC2 | NUMBER                      | YES  | NULL | NULL    | NULL  |
| OB_SPEC3 | VARCHAR2(4096)              | YES  | NULL | NULL    | NULL  |
+----------+-----------------------------+------+-----+---------+-------+
34 rows in set

Export statistics.

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_stat;
+----------+
| COUNT(*) |
+----------+
|      208 |
+----------+
1 row in set

Delete 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 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 set

Set 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 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

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 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);
ORA-20001: object statistics are locked
at oceanbase.DBMS_STATS.GATHER_TABLE_STATS , line : 1, col : 0

Unlock 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 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

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 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    |     2504 |          60 | NULL            |
| T_SUBPART  | PARTITION    |     2530 |          60 | NULL            |
| T_SUBPART  | PARTITION    |     4966 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |      549 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |      560 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |      829 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |      830 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |     1122 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |     1125 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |     1130 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |     1141 |          60 | NULL            |
| T_SUBPART  | SUBPARTITION |     2714 |          60 | NULL            |
| T_SUBPART  | TABLE        |    10000 |          60 | NULL            |
+------------+--------------+----------+-------------+-----------------+
13 rows in set

Restore historical statistics.

obclient> SELECT * FROM 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  | NULL           | NULL              | 2021-09-26 21:46:26.530287 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp2             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp1             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp0             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp2             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp1             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp0             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp2             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp1             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp0             | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P2             | NULL              | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P1             | NULL              | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P0             | NULL              | 2021-09-26 21:48:06.837279 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp0             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp0             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp1             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp2             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp2             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp1             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P0             | NULL              | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp0             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp1             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P2             | NULL              | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp2             | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P1             | NULL              | 2021-09-26 21:48:19.986693 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp0             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp0             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp2             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp1             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp1             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp2             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P1             | NULL              | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp2             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp1             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P2             | NULL              | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp0             | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | P0             | NULL              | 2021-09-26 21:56:10.418878 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 21:58:36.743517 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 21:59:29.930730 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 22:01:57.232698 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp0             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp2             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P1             | NULL              | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P0             | NULL              | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp1             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P2             | NULL              | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P2             | P2sp0             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | NULL           | NULL              | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp2             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp1             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp1             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P0             | P0sp2             | 2021-09-26 22:03:22.234808 +08:00  |
| TEST  | T_SUBPART  | P1             | P1sp0             | 2021-09-26 22:03:22.234808 +08:00  |
+-------+------------+----------------+-------------------+------------------------------------+
56 rows in set

obclient> CALL dbms_stats.restore_table_stats('test', 't_subpart', to_timestamp('2021-09-26 22:03:22.234808', 'YYYY-MM-DD HH24:MI:SS.FF'));       
Query OK, 0 rows affected

obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM 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

Delete historical statistics.

obclient> CALL dbms_stats.purge_stats(to_timestamp('2021-09-26 22:08:42.115370', 'YYYY-MM-DD HH24:MI:SS.FF'));
Query OK, 0 rows affected

obclient> SELECT * FROM  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  | NULL           | NULL              | NULL              |
| TEST  | T_SUBPART  | P0             | NULL              | NULL              |
| TEST  | T_SUBPART  | P1             | NULL              | NULL              |
| TEST  | T_SUBPART  | P2             | NULL              | NULL              |
| TEST  | T_SUBPART  | P0             | P0sp0             | NULL              |
| TEST  | T_SUBPART  | P0             | P0sp1             | NULL              |
| TEST  | T_SUBPART  | P0             | P0sp2             | NULL              |
| TEST  | T_SUBPART  | P1             | P1sp0             | NULL              |
| TEST  | T_SUBPART  | P1             | P1sp1             | NULL              |
| TEST  | T_SUBPART  | P1             | P1sp2             | NULL              |
| TEST  | T_SUBPART  | P2             | P2sp0             | NULL              |
| TEST  | T_SUBPART  | P2             | P2sp1             | NULL              |
| TEST  | T_SUBPART  | P2             | P2sp2             | NULL              |
+-------+------------+----------------+-------------------+-------------------+
13 rows in set