暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 分布式数据库学习Note70：OceanBase社区版中，收集统计信息的综合示例

分布式数据库学习Note70：OceanBase社区版中，收集统计信息的综合示例

分布式数据库爱好者 2023-02-13
310

本文提供收集统计信息的综合示例。

  1. 数据准备，创建表和数据插入。

    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 values (1,1,1),(1,2,1),(1,2,3);
Query OK, 3 rows affected 

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

  2. 收集统计信息。

    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

  3. 查询统计信息。

    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    |        0 |           0 |
| t_subpart  | PARTITION    |        0 |           0 |
| t_subpart  | PARTITION    |        3 |          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 |        0 |           0 |
| t_subpart  | SUBPARTITION |        1 |          60 |
| t_subpart  | SUBPARTITION |        2 |          60 |
| t_subpart  | TABLE        |        3 |          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          |            1 | 1         | 1          |       0 |         0 |           1 |           3 |          20 | FREQUENCY |
| t_subpart  | c2          |            2 | 1         | 2          |       0 |         0 |           2 |           3 |          20 | FREQUENCY |
| t_subpart  | c3          |            2 | 1         | 3          |       0 |         0 |           2 |           3 |          20 | FREQUENCY |
+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+-------------+-----------+
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  | p1             | c1          |               3 |           NULL | 1                     | 04053F0001                |                     3 |
| t_subpart  | p1             | c2          |               1 |           NULL | 1                     | 04053F0001                |                     1 |
| t_subpart  | p1             | c2          |               3 |           NULL | 2                     | 04053F0002                |                     2 |
| t_subpart  | p1             | c3          |               2 |           NULL | 1                     | 04053F0001                |                     2 |
| t_subpart  | p1             | c3          |               3 |           NULL | 3                     | 04053F0003                |                     1 |
+------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
5 rows in set

  4. 准备统计信息用户表。

    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

  5. 导出统计信息。

    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(*) |
+----------+
|      57 |
+----------+
1 row in set

  6. 删除统计信息。

    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

  7. 设置统计信息。

    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

  8. 锁住统计信息。

    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);
ERROR 5935 (HY000): object statistics are locked

  9. 解锁统计信息。

    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

  10. 导入统计信息。

    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    |        0 |           0 | NULL            |
| t_subpart  | PARTITION    |        0 |           0 | NULL            |
| t_subpart  | PARTITION    |        3 |          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 |        0 |           0 | NULL            |
| t_subpart  | SUBPARTITION |        1 |          60 | NULL            |
| t_subpart  | SUBPARTITION |        2 |          60 | NULL            |
| t_subpart  | TABLE        |        3 |          60 | NULL            |
+------------+--------------+----------+-------------+-----------------+
13 rows in set

  11. 恢复历史统计信息。

    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  | p0             | p0ssp1            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p1             | p1ssp2            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p0             | p0ssp0            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p2             | NULL              | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p2             | p2ssp0            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p1             | p1ssp1            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p1             | NULL              | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p2             | p2ssp1            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p0             | NULL              | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p2             | p2ssp2            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p1             | p1ssp0            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p0             | p0ssp2            | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | NULL           | NULL              | 2022-10-21 19:26:07.800867 |
| test  | t_subpart  | p0             | p0ssp1            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p2             | NULL              | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p1             | p1ssp0            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p1             | p1ssp1            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p0             | p0ssp0            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p0             | p0ssp2            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p1             | p1ssp2            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p2             | p2ssp0            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p1             | NULL              | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p2             | p2ssp1            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p0             | NULL              | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | p2             | p2ssp2            | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | NULL           | NULL              | 2022-10-21 19:30:23.297601 |
| test  | t_subpart  | NULL           | NULL              | 2022-10-21 19:31:00.235332 |
| test  | t_subpart  | p1             | p1ssp2            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p0             | p0ssp2            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p1             | p1ssp1            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p2             | p2ssp0            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p2             | p2ssp1            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p1             | p1ssp0            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p2             | p2ssp2            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p1             | NULL              | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p0             | p0ssp1            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | NULL           | NULL              | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p2             | NULL              | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p0             | p0ssp0            | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p0             | NULL              | 2022-10-21 19:31:20.117674 |
| test  | t_subpart  | p1             | p1ssp1            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p1             | NULL              | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p2             | p2ssp1            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p0             | p0ssp2            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p1             | p1ssp2            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p2             | NULL              | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p2             | p2ssp0            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p0             | p0ssp0            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | NULL           | NULL              | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p1             | p1ssp0            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p0             | p0ssp1            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p2             | p2ssp2            | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | p0             | NULL              | 2022-10-21 19:32:03.472738 |
| test  | t_subpart  | NULL           | NULL              | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p2             | p2ssp1            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p1             | p1ssp2            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p2             | p2ssp2            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p0             | NULL              | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p2             | p2ssp0            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p1             | NULL              | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p2             | NULL              | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p1             | p1ssp1            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p0             | p0ssp0            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p1             | p1ssp0            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p0             | p0ssp1            | 2022-10-21 19:32:46.328983 |
| test  | t_subpart  | p0             | p0ssp2            | 2022-10-21 19:32:46.328983 |
+-------+------------+----------------+-------------------+----------------------------+
66 rows in set

obclient> CALL dbms_stats.restore_table_stats('test', 't_subpart', date_format('2022-10-21 19:03:22.234808', '%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 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

  12. 删除历史统计信息。

    obclient> CALL dbms_stats.purge_stats(date_format('2022-10-21 19:03:22.234808', '%Y-%m-%d %H:%i:%s'));
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
oceanbasetest分布式数据库selectnum
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论