Oceanbase系列之–查询转换(很牛B)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oceanbase系列之–查询转换(很牛B)
听闻Oceanbase对于Oracle的兼容做的不错，尤其是查询转换方面是强项。这里我简单测试一下，供大家参考，不足之处，还请指正，谢谢。
|
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 |
obclient> create table t1 as select * from dba_objects; Query OK, 237 rows affected (0.35 sec) obclient> create table t2 as select * from dba_objects; Query OK, 238 rows affected (0.10 sec) obclient> select count(distinct owner) from dba_objects; +----------------------+ | COUNT(DISTINCTOWNER) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.03 sec) obclient> explain select distinct a.owner from t2 a where not exists (select b.owner from t1 b where b.owner=a.owner and b.owner='ROGER') \G; *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------------------- |0 |HASH DISTINCT | |100 |769 | |1 | HASH RIGHT ANTI JOIN| |233 |621 | |2 | TABLE SCAN |B |3 |272 | |3 | TABLE SCAN |A |238 |162 | ============================================== Outputs & filters: ------------------------------------- 0 - output([A.OWNER]), filter(nil), distinct([A.OWNER]) 1 - output([A.OWNER]), filter(nil), equal_conds([B.OWNER = A.OWNER]), other_conds(nil) 2 - output([B.OWNER]), filter([B.OWNER = 'ROGER']), access([B.OWNER]), partitions(p0) 3 - output([A.OWNER]), filter(nil), access([A.OWNER]), partitions(p0) 1 row in set (0.00 sec) obclient> explain select distinct a.owner from t2 a where not exists -> (select b.owner from t1 b where b.owner=a.owner and b.owner='ROGER') \G; *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------------------- |0 |HASH DISTINCT | |100 |769 | |1 | HASH RIGHT ANTI JOIN| |233 |621 | |2 | TABLE SCAN |B |3 |272 | |3 | TABLE SCAN |A |238 |162 | ============================================== Outputs & filters: ------------------------------------- 0 - output([A.OWNER]), filter(nil), distinct([A.OWNER]) 1 - output([A.OWNER]), filter(nil), equal_conds([B.OWNER = A.OWNER]), other_conds(nil) 2 - output([B.OWNER]), filter([B.OWNER = 'ROGER']), access([B.OWNER]), partitions(p0) 3 - output([A.OWNER]), filter(nil), access([A.OWNER]), partitions(p0) 1 row in set (0.00 sec) |
从上面的测试来看,OB 这里对于not exists和exists处理跟Oracle几乎一样了。（Oracle 10g版本). 查看参数发现ob也有类似的参数：
|
1 2 3 4 5 6 7 8 |
obclient> show variables like '%group%'; +-------------------------+-------+ | VARIABLE_NAME | VALUE | +-------------------------+-------+ | group_concat_max_len | 32767 | | ob_enable_hash_group_by | ON | +-------------------------+-------+ 2 rows in set (0.01 sec) |
继续看看exists 的处理是否一样呢 ？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
obclient> explain select distinct a.owner from t2 a where exists -> (select b.owner from t1 b where b.owner=a.owner and b.owner='ROGER')\G; *************************** 1. row *************************** Query Plan: ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |LIMIT | |1 |362 | |1 | MERGE SEMI JOIN| |1 |362 | |2 | TABLE SCAN |A |2 |180 | |3 | TABLE SCAN |B |2 |180 | ========================================= Outputs & filters: ------------------------------------- 0 - output([A.OWNER]), filter(nil), limit(1), offset(nil) 1 - output([A.OWNER]), filter(nil), equal_conds([B.OWNER = A.OWNER]), other_conds(nil) 2 - output([A.OWNER]), filter([A.OWNER = 'ROGER']), access([A.OWNER]), partitions(p0) 3 - output([B.OWNER]), filter([B.OWNER = 'ROGER']), access([B.OWNER]), partitions(p0) 1 row in set (0.00 sec) |
这里走了semi join,也跟Oracle 的处理方式一样了。不过Oracle这种情况下通常是走hash semi join;而OB这里走了merge semi join。继续测试一下not in ：
|
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 |
obclient> explain select distinct a.object_id from t2 a where a.object_id not in -> (select b.object_id from t1 b) \G; *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------------------- |0 |HASH DISTINCT | |3 |998 | |1 | HASH RIGHT ANTI JOIN| |3 |995 | |2 | TABLE SCAN |B |237 |161 | |3 | TABLE SCAN |A |238 |162 | ============================================== Outputs & filters: ------------------------------------- 0 - output([A.OBJECT_ID]), filter(nil), distinct([A.OBJECT_ID]) 1 - output([A.OBJECT_ID]), filter(nil), equal_conds([A.OBJECT_ID = B.OBJECT_ID]), other_conds(nil) 2 - output([B.OBJECT_ID]), filter(nil), access([B.OBJECT_ID]), partitions(p0) 3 - output([A.OBJECT_ID]), filter(nil), access([A.OBJECT_ID]), partitions(p0) 1 row in set (0.00 sec) ERROR: No query specified |
可以看到对于非null 列,这里无论是not exists/exists 还是not in；ob都走了hash anti join/semi join；这一点跟Oracle 11g一致。
那么，如果where 列 允许为空呢？ 能否还走hash anti join呢？ 我们修改一下表结构，再测一把。
|
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 107 108 109 |
obclient> alter table t1 modify OBJECT_ID NUMBER(38) ; Query OK, 0 rows affected (0.02 sec) obclient> alter table t2 modify OBJECT_ID NUMBER(38) ; Query OK, 0 rows affected (0.02 sec) obclient> show create table t2 \G; *************************** 1. row *************************** TABLE: T2 CREATE TABLE: CREATE TABLE "T2" ( "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER(38) NOT NULL, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(256), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128) ) 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> show create table t1 \G; *************************** 1. row *************************** TABLE: T1 CREATE TABLE: CREATE TABLE "T1" ( "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER(38) NOT NULL, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(256), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128) ) 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) obclient> explain select distinct a.object_id from t2 a where a.object_id not in -> (select b.object_id from t1 b) \G; *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------------------- |0 |HASH DISTINCT | |3 |998 | |1 | HASH RIGHT ANTI JOIN| |3 |995 | |2 | TABLE SCAN |B |237 |161 | |3 | TABLE SCAN |A |238 |162 | ============================================== Outputs & filters: ------------------------------------- 0 - output([A.OBJECT_ID]), filter(nil), distinct([A.OBJECT_ID]) 1 - output([A.OBJECT_ID]), filter(nil), equal_conds([A.OBJECT_ID = B.OBJECT_ID]), other_conds(nil) 2 - output([B.OBJECT_ID]), filter(nil), access([B.OBJECT_ID]), partitions(p0) 3 - output([A.OBJECT_ID]), filter(nil), access([A.OBJECT_ID]), partitions(p0) 1 row in set (0.00 sec) obclient> explain select distinct a.object_id from t2 a where a.object_id in -> (select b.object_id from t1 b) \G; *************************** 1. row *************************** Query Plan: ========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------ |0 |HASH DISTINCT | |73 |1037| |1 | HASH JOIN | |234 |909 | |2 | SUBPLAN SCAN |VIEW1|101 |325 | |3 | HASH DISTINCT| |101 |311 | |4 | TABLE SCAN |B |237 |161 | |5 | TABLE SCAN |A |238 |162 | ========================================== Outputs & filters: ------------------------------------- 0 - output([A.OBJECT_ID]), filter(nil), distinct([A.OBJECT_ID]) 1 - output([A.OBJECT_ID]), filter(nil), equal_conds([A.OBJECT_ID = VIEW1.OBJECT_ID]), other_conds(nil) 2 - output([VIEW1.OBJECT_ID]), filter(nil), access([VIEW1.OBJECT_ID]) 3 - output([B.OBJECT_ID]), filter(nil), distinct([B.OBJECT_ID]) 4 - output([B.OBJECT_ID]), filter(nil), access([B.OBJECT_ID]), partitions(p0) 5 - output([A.OBJECT_ID]), filter(nil), access([A.OBJECT_ID]), partitions(p0) 1 row in set (0.00 sec) |
从上面的测试来看OB针对not exists、exists、not in、in 的处理几乎跟Oracle 11gR2 一样了。这是一个巨大的进步。由此不难看出， Ob在针对Oracle方面的兼容还是有一定功底的。
接下来再来测一下View 谓词推入：
|
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 |
bclient> create table t3 as select * from dba_objects; Query OK, 239 rows affected (0.09 sec) obclient> create index idx_t1_id on t1(object_id) ; Query OK, 0 rows affected (0.43 sec) obclient> create index idx_t2_id on t2(object_id) ; Query OK, 0 rows affected (0.43 sec) obclient> create index idx_t3_id on t3(object_id) ; Query OK, 0 rows affected (0.43 sec) obclient> create view enmotech_view as -> select t1.* -> from t1,t2 -> where t1.object_id = t2.object_id; Query OK, 0 rows affected (0.01 sec) obclient> obclient> explain select t3.object_name -> from t3, enmotech_view -> where t3.object_name = enmotech_view.object_name -> and t3.object_id = 1100611139453886 \G; *************************** 1. row *************************** Query Plan: ================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------- |0 |NESTED-LOOP JOIN| |6 |523 | |1 | HASH JOIN | |3 |434 | |2 | TABLE SCAN |T3(IDX_T3_ID)|1 |89 | |3 | TABLE SCAN |T1 |237 |167 | |4 | TABLE SCAN |T2(IDX_T2_ID)|3 |37 | ================================================== Outputs & filters: ------------------------------------- 0 - output([T3.OBJECT_NAME]), filter(nil), conds(nil), nl_params_([T1.OBJECT_ID]) 1 - output([T3.OBJECT_NAME], [T1.OBJECT_ID]), filter(nil), equal_conds([T3.OBJECT_NAME = T1.OBJECT_NAME]), other_conds(nil) 2 - output([T3.OBJECT_NAME]), filter(nil), access([T3.OBJECT_NAME]), partitions(p0) 3 - output([T1.OBJECT_ID], [T1.OBJECT_NAME]), filter(nil), access([T1.OBJECT_ID], [T1.OBJECT_NAME]), partitions(p0) 4 - output([T2.OBJECT_ID]), filter(nil), access([T2.OBJECT_ID]), partitions(p0) 1 row in set (0.01 sec) ERROR: No query specified obclient> explain select t3.object_name -> from t3, enmotech_view -> where t3.object_name = enmotech_view.object_name(+) -> and t3.object_id = 1100611139453886 \G; *************************** 1. row *************************** Query Plan: ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------- |0 |HASH OUTER JOIN| |6 |1721| |1 | TABLE SCAN |T3(IDX_T3_ID)|1 |89 | |2 | SUBPLAN SCAN |ENMOTECH_VIEW|553 |1222| |3 | HASH JOIN | |553 |1145| |4 | TABLE SCAN |T2(IDX_T2_ID)|238 |90 | |5 | TABLE SCAN |T1 |237 |167 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T3.OBJECT_NAME]), filter(nil), equal_conds([T3.OBJECT_NAME = ENMOTECH_VIEW.OBJECT_NAME]), other_conds(nil) 1 - output([T3.OBJECT_NAME]), filter(nil), access([T3.OBJECT_NAME]), partitions(p0) 2 - output([ENMOTECH_VIEW.OBJECT_NAME]), filter(nil), access([ENMOTECH_VIEW.OBJECT_NAME]) 3 - output([T1.OBJECT_NAME]), filter(nil), equal_conds([T1.OBJECT_ID = T2.OBJECT_ID]), other_conds(nil) 4 - output([T2.OBJECT_ID]), filter(nil), access([T2.OBJECT_ID]), partitions(p0) 5 - output([T1.OBJECT_ID], [T1.OBJECT_NAME]), filter(nil), access([T1.OBJECT_ID], [T1.OBJECT_NAME]), partitions(p0) 1 row in set (0.00 sec) |
不难看出上述行为跟Oracle一致。最后再简单测一下子查询展开的情况看看，比如包含rownum,union all之类行不行，实际上这方面Oracle也无法进行展开。
|
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 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
obclient> explain select object_id from t1 where exists ( -> select 1 from t2 where t1.object_id=t2.object_id - 10) \G; *************************** 1. row *************************** Query Plan: ================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------ |0 |HASH SEMI JOIN| |3 |695 | |1 | TABLE SCAN |T1(IDX_T1_ID)|237 |90 | |2 | TABLE SCAN |T2(IDX_T2_ID)|238 |90 | ================================================ Outputs & filters: ------------------------------------- 0 - output([T1.OBJECT_ID]), filter(nil), equal_conds([T1.OBJECT_ID = T2.OBJECT_ID - 10]), other_conds(nil) 1 - output([T1.OBJECT_ID]), filter(nil), access([T1.OBJECT_ID]), partitions(p0) 2 - output([T2.OBJECT_ID - 10]), filter(nil), access([T2.OBJECT_ID]), partitions(p0) 1 row in set (0.01 sec) ERROR: No query specified obclient> explain select object_id from t1 where exists ( -> select 1 from t2 where t1.object_id=t2.object_id - 10 and rownum < 10) \G; *************************** 1. row *************************** Query Plan: ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------- |0 |SUBPLAN FILTER| |119 |30879| |1 | TABLE SCAN |T1(IDX_T1_ID)|237 |90 | |2 | TABLE SCAN |T2(IDX_T2_ID)|3 |130 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T1.OBJECT_ID]), filter([(T_OP_EXISTS, subquery(1))]), exec_params_([T1.OBJECT_ID]), onetime_exprs_(nil), init_plan_idxs_(nil) 1 - output([T1.OBJECT_ID]), filter(nil), access([T1.OBJECT_ID]), partitions(p0) 2 - output([1]), filter([? = T2.OBJECT_ID - 10]), access([T2.OBJECT_ID]), partitions(p0), limit(?), offset(nil) 1 row in set (0.01 sec) ERROR: No query specified obclient> explain select object_id -> from t1 -> where exists (select 1 from t2 where t1.object_id = t2.object_id - 100 union all -> select object_id from t2 where object_id < 500) \G; *************************** 1. row *************************** Query Plan: ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------- |0 |SUBPLAN FILTER| |119 |26766| |1 | TABLE SCAN |T1(IDX_T1_ID)|237 |90 | |2 | LIMIT | |1 |113 | |3 | UNION ALL | |1 |113 | |4 | TABLE SCAN |T2(IDX_T2_ID)|1 |76 | |5 | TABLE SCAN |T2(IDX_T2_ID)|1 |36 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T1.OBJECT_ID]), filter([(T_OP_EXISTS, subquery(1))]), exec_params_([T1.OBJECT_ID]), onetime_exprs_(nil), init_plan_idxs_(nil) 1 - output([T1.OBJECT_ID]), filter(nil), access([T1.OBJECT_ID]), partitions(p0) 2 - output([UNION(?, cast(T2.OBJECT_ID, DECIMAL(-1, -85)))]), filter(nil), limit(1), offset(nil) 3 - output([UNION(?, cast(T2.OBJECT_ID, DECIMAL(-1, -85)))]), filter(nil) 4 - output([?]), filter([? = T2.OBJECT_ID - 100]), access([T2.OBJECT_ID]), partitions(p0), limit(1), offset(nil) 5 - output([cast(T2.OBJECT_ID, DECIMAL(-1, -85))]), filter(nil), access([T2.OBJECT_ID]), partitions(p0), limit(1), offset(nil) 1 row in set (0.05 sec) obclient> explain select object_id -> from t1 -> where exists (select 1 -> from t2 -> start with owner='ROGER' -> connect by object_id > 1100611139453000 and object_id < 200) -> \G; *************************** 1. row *************************** Query Plan: ========================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------------------- |0 |SUBPLAN FILTER | |119 |1058| |1 | TABLE SCAN |T1(IDX_T1_ID)|237 |90 | |2 | NESTED-LOOP CONNECT BY| |0 |936 | |3 | SUBPLAN SCAN |VIEW1 |3 |279 | |4 | TABLE SCAN |T2 |3 |278 | |5 | TABLE SCAN |T2 |238 |167 | ========================================================= Outputs & filters: ------------------------------------- 0 - output([T1.OBJECT_ID]), filter([?]), exec_params_(nil), onetime_exprs_([(T_OP_EXISTS, subquery(1))]), init_plan_idxs_(nil) 1 - output([T1.OBJECT_ID]), filter(nil), access([T1.OBJECT_ID]), partitions(p0) 2 - output([1]), filter(nil), conds([T2.OBJECT_ID > 1100611139453000], [T2.OBJECT_ID < 200]), nl_params_(nil) 3 - output([T2.OBJECT_ID]), filter(nil), access([T2.OBJECT_ID]) 4 - output([T2.OBJECT_ID]), filter([T2.OWNER = 'ROGER']), access([T2.OWNER], [T2.OBJECT_ID]), partitions(p0) 5 - output([T2.OWNER], [T2.OBJECT_ID]), filter(nil), access([T2.OWNER], [T2.OBJECT_ID]), partitions(p0) 1 row in set (0.00 sec) obclient> select sysdate from dual; +---------------------+ | SYSDATE | +---------------------+ | 2020-06-06 20:58:41 | +---------------------+ 1 row in set (0.00 sec) |
从上述的测试来看,在查询转换方面OB做的还是非常不错的,几乎跟Oracle 一致，可见兼容性还不错。非常给力！这方面应该算是我目前测试过的国产数据库方面最强的了。
注意：以上测试脚本我完全是照搬自己很早之前的测试例子，未做任何改动。
Leave a Reply
You must be logged in to post a comment.