Oceanbase系列之–2.2版本分区裁剪增强
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oceanbase系列之–2.2版本分区裁剪增强
前一篇文章测试了Ob对于分区的支持，看ob官方文档2.1版本，说对于分区表，如果SQL where条件存在表达式的话，那么是无法走分区的，这是一个非常严重的功能缺陷。经测试这个功能在2.2版本可以完美支持。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
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一些数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
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 操作完成之后，我们再来查一下统计信息是否更新了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
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还是很不错的，希望功能不断完善。
Leave a Reply
You must be logged in to post a comment.