听闻Oceanbase对于Oracle的兼容做的不错，尤其是查询转换方面是强项。这里我简单测试一下，供大家参考，不足之处，还请指正，谢谢。
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也有类似的参数：
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 的处理是否一样呢 ？
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 ：
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呢？ 我们修改一下表结构，再测一把。
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 谓词推入：
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也无法进行展开。
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一致，可见兼容性还不错。非常给力！这方面应该算是我目前测试过的国产数据库方面最强的了。
注意：以上测试脚本我完全是照搬自己很早之前的测试例子，未做任何改动。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
