Oceanbase 2.2版本分区裁剪增强

李真旭 2020-06-10

1170

看ob官方文档2.1版本，说对于分区表，如果SQL where条件存在表达式的话，那么是无法走分区的，这是一个非常严重的功能缺陷。经测试这个功能在2.2版本可以完美支持。

obclient>  create table enmotech_part(a number,b number)
    -> partition by range(a)
    -> (partition p_1 values less than (10),
    ->  partition p_2 values less than (20),
    ->  partition p_3 values less than (30),
    ->  partition p_4 values less than (100),
    ->  partition p_5 values less than (99999)
    -> );
Query OK, 0 rows affected (0.05 sec)
 
obclient> create sequence seq_enmotech_part start with 1 increment by 1 cache 50 nocycle;
Query OK, 0 rows affected (0.01 sec)
 
obclient> 
obclient> delimiter //
obclient> CREATE OR REPLACE PROCEDURE pro_enmotech_part( p_name varchar2 )
    -> AS  
    -> BEGIN
    ->     INSERT INTO enmotech_part(a, b) values(seq_enmotech_part.nextval, p_name) ;
    ->     COMMIT;
    ->     dbms_output.put_line('Add a row which name is : ' || p_name );
    -> EXCEPTION
    ->     WHEN OTHERS THEN 
    ->         ROLLBACK;
    ->         dbms_output.put_line('Exception raised!');
    -> END ;
    -> //
Query OK, 0 rows affected (0.01 sec)
 
obclient> delimiter ;
obclient> call pro_enmotech_part(100);
Query OK, 0 rows affected (0.26 sec)
 
obclient> call pro_enmotech_part(101);
Query OK, 0 rows affected (0.00 sec)
 
obclient> call pro_enmotech_part(102);
Query OK, 0 rows affected (0.00 sec)
 
obclient> call pro_enmotech_part(103);
Query OK, 0 rows affected (0.01 sec)
 
obclient> call pro_enmotech_part(10000);
Query OK, 0 rows affected (0.00 sec)
......
obclient> select * from enmotech_part  
    -> ;
+------+-------+
| A    | B     |
+------+-------+
|    1 |   100 |
|    2 |   101 |
|    3 |   102 |
|    4 |   103 |
|    5 | 10000 |
|    6 |   111 |
|    7 |  1121 |
|    8 | 11211 |
|    9 |    11 |
|   10 |   101 |
|   11 |   100 |
+------+-------+
11 rows in set (0.00 sec)
 
 
obclient> select * from enmotech_part partition (P_2);
+------+------+
| A    | B    |
+------+------+
|   10 |  101 |
|   11 |  100 |
+------+------+
2 rows in set (0.00 sec)
 
obclient> explain select * from enmotech_part where a > 8 and a < 12 \G;
*************************** 1. row ***************************
Query Plan: =========================================================
|ID|OPERATOR               |NAME         |EST. ROWS|COST|
---------------------------------------------------------
|0 |EXCHANGE IN DISTR      |             |1        |47  |
|1 | EXCHANGE OUT DISTR    |:EX10000     |1        |47  |
|2 |  PX PARTITION ITERATOR|             |1        |47  |
|3 |   TABLE SCAN          |ENMOTECH_PART|1        |47  |
=========================================================
 
Outputs & filters: 
-------------------------------------
  0 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter(nil)
  1 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter(nil), dop=1
  2 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter(nil)
  3 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter([ENMOTECH_PART.A > 8], [ENMOTECH_PART.A < 12]), 
      access([ENMOTECH_PART.A], [ENMOTECH_PART.B]), partitions(p[0-1])
 
1 row in set (0.00 sec)
 
ERROR: 
No query specified

大家看上面的直接计划可以发现，ID=3的地方走了 PX PARTITION ITERATOR操作；通过条件直接走的access，定位到了某个partition。这是一个正常操作。
那么ob支持统计信息收集吗？下面来模拟多insert一些数据。

obclient> insert into enmotech_part select * from enmotech_part;
Query OK, 18 rows affected (0.01 sec)
Records: 18  Duplicates: 0  Warnings: 0
......
obclient> insert into enmotech_part select * from enmotech_part;
Query OK, 18432 rows affected (0.29 sec)
Records: 18432  Duplicates: 0  Warnings: 0
 
obclient> commit;
Query OK, 0 rows affected (0.01 sec)
 
obclient> select OWNER,table_name,num_rows,blocks,EMPTY_BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,PARTITIONED from dba_tables where table_name=upper('enmotech_part');
+-------+---------------+----------+--------+--------------+-------------+---------------+-------------+
| OWNER | TABLE_NAME    | NUM_ROWS | BLOCKS | EMPTY_BLOCKS | AVG_ROW_LEN | LAST_ANALYZED | PARTITIONED |
+-------+---------------+----------+--------+--------------+-------------+---------------+-------------+
| ROGER | ENMOTECH_PART |       11 |   NULL |         NULL |        NULL | NULL          | YES         |
+-------+---------------+----------+--------+--------------+-------------+---------------+-------------+
1 row in set (0.01 sec)
 
obclient> analyze table enmotech_part compute statistics;
ERROR-00600: internal error code, arguments: -5602, Should collect histogram after major freeze
 
obclient>  alter system major freeze;
Query OK, 0 rows affected (0.00 sec)
 
obclient>  alter system major freeze;
Query OK, 0 rows affected (0.00 sec)
 
obclient> select
    ->   r_c as row_count,
    ->   s.num_distinct as NDV, 
    ->   s.num_null as num_null,
    ->   des_hex_str(s.min_value) as min,
    ->   des_hex_str(s.max_value) as max
    -> from
    ->   __all_column_statistic s,
    ->   __all_database d,
    ->   __all_table t,
    ->   __all_column c,
    ->   (select max(row_count)as r_c, table_id from __all_meta_table group by table_id) m
    -> where
    ->   s.table_id= t.table_id
    ->   and t.database_id= d.database_id
    ->   and d.database_name= 'oboracle'
    ->   and t.table_name= upper('enmotech_part')   
    ->   and c.table_id= t.table_id
    ->   and s.column_id= c.column_id
    ->   and s.column_id= c.column_id
    ->   and s.table_id= m.table_id;
Empty set (0.02 sec)

看上去analyze命令是支持的，不过不支持手工收集统计信息。当major freeze 操作完成之后，我们再来查一下统计信息是否更新了：

obclient> select OWNER,table_name,num_rows,blocks,EMPTY_BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,PARTITIONED
    -> from dba_tables where table_name=upper('enmotech_part');
+-------+---------------+----------+--------+--------------+-------------+---------------+-------------+
| OWNER | TABLE_NAME    | NUM_ROWS | BLOCKS | EMPTY_BLOCKS | AVG_ROW_LEN | LAST_ANALYZED | PARTITIONED |
+-------+---------------+----------+--------+--------------+-------------+---------------+-------------+
| ROGER | ENMOTECH_PART |    36864 |   NULL |         NULL |        NULL | NULL          | YES         |
+-------+---------------+----------+--------+--------------+-------------+---------------+-------------+
1 row in set (0.04 sec)
obclient> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS,BLOCKS,EMPTY_BLOCKS
    -> from dba_tab_partitions where table_name=upper('enmotech_part');
+---------------+----------------+------------+----------+--------+--------------+
| TABLE_NAME    | PARTITION_NAME | HIGH_VALUE | NUM_ROWS | BLOCKS | EMPTY_BLOCKS |
+---------------+----------------+------------+----------+--------+--------------+
| ENMOTECH_PART | P_5            | 99999      |     NULL |   NULL |         NULL |
| ENMOTECH_PART | P_4            | 100        |     NULL |   NULL |         NULL |
| ENMOTECH_PART | P_3            | 30         |     NULL |   NULL |         NULL |
| ENMOTECH_PART | P_2            | 20         |     NULL |   NULL |         NULL |
| ENMOTECH_PART | P_1            | 10         |     NULL |   NULL |         NULL |
+---------------+----------------+------------+----------+--------+--------------+
5 rows in set (0.08 sec)
obclient> select owner,segment_name,partition_name,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,INITIAL_EXTENT,NEXT_EXTENT,MAX_SIZE,RETENTION
    -> FREELISTS,RELATIVE_FNO,FLASH_CACHE,CELL_FLASH_CACHE from dba_segments where segment_name=upper('enmotech_part');
+-------+---------------+----------------+-----------------+-------------+--------------+---------+--------+----------------+-------------+----------+-----------+--------------+-------------+------------------+
| OWNER | SEGMENT_NAME  | PARTITION_NAME | SEGMENT_TYPE    | HEADER_FILE | HEADER_BLOCK | BYTES   | BLOCKS | INITIAL_EXTENT | NEXT_EXTENT | MAX_SIZE | FREELISTS | RELATIVE_FNO | FLASH_CACHE | CELL_FLASH_CACHE |
+-------+---------------+----------------+-----------------+-------------+--------------+---------+--------+----------------+-------------+----------+-----------+--------------+-------------+------------------+
| ROGER | ENMOTECH_PART | P_1            | TABLE PARTITION |        NULL |         NULL | 2097152 |  16384 |           NULL |        NULL |     NULL | NULL      |         NULL | DEFAULT     | DEFAULT          |
| ROGER | ENMOTECH_PART | P_2            | TABLE PARTITION |        NULL |         NULL | 2097152 |  16384 |           NULL |        NULL |     NULL | NULL      |         NULL | DEFAULT     | DEFAULT          |
| ROGER | ENMOTECH_PART | P_3            | TABLE PARTITION |        NULL |         NULL |       0 |  16384 |           NULL |        NULL |     NULL | NULL      |         NULL | DEFAULT     | DEFAULT          |
| ROGER | ENMOTECH_PART | P_4            | TABLE PARTITION |        NULL |         NULL |       0 |  16384 |           NULL |        NULL |     NULL | NULL      |         NULL | DEFAULT     | DEFAULT          |
| ROGER | ENMOTECH_PART | P_5            | TABLE PARTITION |        NULL |         NULL |       0 |  16384 |           NULL |        NULL |     NULL | NULL      |         NULL | DEFAULT     | DEFAULT          |
+-------+---------------+----------------+-----------------+-------------+--------------+---------+--------+----------------+-------------+----------+-----------+--------------+-------------+------------------+
5 rows in set (0.13 sec)
obclient> show create table enmotech_part \G;
*************************** 1. row ***************************
       TABLE: ENMOTECH_PART
CREATE TABLE: CREATE TABLE "ENMOTECH_PART" (
  "A" NUMBER,
  "B" NUMBER
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
 partition by range(a)
(partition P_1 values less than (10),
partition P_2 values less than (20),
partition P_3 values less than (30),
partition P_4 values less than (100),
partition P_5 values less than (99999))
1 row in set (0.01 sec)
 
ERROR: 
No query specified
 
obclient> 
 
obclient> select owner,table_name,num_rows,ROW_MOVEMENT,MONITORING,COMPRESSION,COMPRESS_FOR,DROPPED,READ_ONLY,RESULT_CACHE
    -> from dba_tables where table_name=upper('enmotech_part');
+-------+---------------+----------+--------------+------------+-------------+--------------+---------+-----------+--------------+
| OWNER | TABLE_NAME    | NUM_ROWS | ROW_MOVEMENT | MONITORING | COMPRESSION | COMPRESS_FOR | DROPPED | READ_ONLY | RESULT_CACHE |
+-------+---------------+----------+--------------+------------+-------------+--------------+---------+-----------+--------------+
| ROGER | ENMOTECH_PART |    36864 | NULL         | NULL       | NULL        | NULL         | NO      | NULL      | NULL         |
+-------+---------------+----------+--------------+------------+-------------+--------------+---------+-----------+--------------+
1 row in set (0.02 sec)
 
obclient> drop table test2;
Query OK, 0 rows affected (0.20 sec)
 
obclient> create table test2 as select * from enmotech_part;
Query OK, 36864 rows affected (0.37 sec)
 
obclient> show create table test2 \G;
*************************** 1. row ***************************
       TABLE: TEST2
CREATE TABLE: CREATE TABLE "TEST2" (
  "A" NUMBER,
  "B" NUMBER
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
 
obclient> set ob_enable_trace_log=on;
Query OK, 0 rows affected (0.00 sec)
 
obclient> explain select * from enmotech_part where a > 9and a < 12 \G;  
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR               |NAME         |EST. ROWS|COST |
----------------------------------------------------------
|0 |EXCHANGE IN DISTR      |             |4458     |22523|
|1 | EXCHANGE OUT DISTR    |:EX10000     |4458     |21679|
|2 |  PX PARTITION ITERATOR|             |4458     |21679|
|3 |   TABLE SCAN          |ENMOTECH_PART|4458     |21679|
==========================================================
 
Outputs & filters: 
-------------------------------------
  0 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter(nil)
  1 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter(nil), dop=1
  2 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter(nil)
  3 - output([ENMOTECH_PART.A], [ENMOTECH_PART.B]), filter([ENMOTECH_PART.A > 9], [ENMOTECH_PART.A < 12]), 
      access([ENMOTECH_PART.A], [ENMOTECH_PART.B]), partitions(p[0-1])
 
1 row in set (0.00 sec)
 
ERROR: 
No query specified

我们可以看到，对于统计信息方面，ob目前支持还非常弱，可以说还停留在原始阶段。另外就是DBA_xxx相关试图的支持也还很弱，很多信息都没有。
其次从上面的测试大家看出，对于Oracle租户模式下，默认数据库使用16k blocksize；同时会默认启用compress for archive的压缩方式。这一点类似Oracle 11gR2的新特性。
由此我们不难看出，OceanBase基于Oracle的兼容支持，是直接对标Oracle 11gR2版本(猜测).
另外很多DBA试图看上去完全是照搬Oracle的定义，这似乎行不通，比如dba_objects.
总的来说，不可否则,个人觉得OB还是很不错的，希望功能不断完善。

oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oceanbase 2.2版本分区裁剪增强

评论