达梦不支持filter类型的执行路径导致慢SQL

最近有个政府项目的库往政务云上迁移到达梦库，源库的业务量不是很大，库本身也不大。

迁移后抓取达梦的AWR，发现有一条SQL每次执行需要15s多，而在原来的Oracle里边执行0.1s。

查看后发现是达梦不支持filter执行路径导致的。

模拟如下：

创建dgd，p，s共3个表。

create table dgd (hsstr varchar2(20),exstr varchar2(20));
create table p   (id number);
create table s   (id number,hs_str varchar2(40));

业务SQL如下：

select * from dgd
where exists (select 1
from p, s
where p.id = s.id
and dgd.hsstr || dgd.exstr like s.hs_str || '%');

SQL为半连接，关联条件使用了like，因此无论是在Oracle还是达梦都无法使用HASH JOIN算法。

SQL在Oracle和达梦的执行计划如下：

在Oracle和达梦里边，P和S表都使用了HASH JOIN算法。

由于Oracle的FILTER有去重效果，会减少ID为3，4，5的执行次数，

而在达梦里边，NEST LOOP SEMI JOIN2会导致ID为6，7，8的执行次数为DGD的结果集数，没有去重效果。

因此在驱动表关联字段重复数据多的情况下，因为无去重导致达梦实际上就慢很多了。

Oracle：
Plan hash value: 166352517

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |       3 |       |       |          |
|*  1 |  FILTER             |      |      1 |        |      0 |00:00:00.01 |       3 |       |       |          |
|   2 |   TABLE ACCESS FULL | DGD  |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|*  3 |   HASH JOIN         |      |      0 |      1 |      0 |00:00:00.01 |       0 |  1393K|  1393K|          |
|   4 |    TABLE ACCESS FULL| P    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |    TABLE ACCESS FULL| S    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NOT NULL)
   3 - access("P"."ID"="S"."ID")
   5 - filter(:B1||:B2 LIKE "S"."HS_STR"||'%')


达梦：
1   #NSET2: [1, 1, 108] 
2     #PRJT2: [1, 1, 108]; exp_num(3), is_atom(FALSE) 
3       #NEST LOOP SEMI JOIN2: [1, 1, 108];  join condition(DGD.HSSTR || DGD.EXSTR LIKE S.HS_STR || '%')[with var]
4         #CSCN2: [1, 1, 108]; INDEX33557305(DGD)
5         #SLCT2: [1, 1, 108]; var4 LIKE S.HS_STR || '%'
6           #HASH2 INNER JOIN: [1, 1, 108];  KEY_NUM(1); KEY(P.ID=S.ID) KEY_NULL_EQU(0)
7             #CSCN2: [1, 1, 30]; INDEX33557306(P)
8             #CSCN2: [1, 1, 78]; INDEX33557308(S)

仔细看上边达梦的执行计划，[with var]表示使用了变量改写方式实现连接。

使用hint禁止变量改写之后，执行计划如下：

还是无法避免NEST LOOP。

1   #NSET2: [14, 1, 108] 
2     #PRJT2: [14, 1, 108]; exp_num(3), is_atom(FALSE) 
3       #NEST LOOP SEMI JOIN2: [14, 1, 108];  join condition(DGD.HSSTR || DGD.EXSTR LIKE S.HS_STR || '%')
4         #CSCN2: [1, 1, 108]; INDEX33557305(DGD)
5         #HASH2 INNER JOIN: [1, 1, 108];  KEY_NUM(1); KEY(P.ID=S.ID) KEY_NULL_EQU(0)
6           #CSCN2: [1, 1, 30]; INDEX33557306(P)
7           #CSCN2: [1, 1, 78]; INDEX33557308(S)

关于变量改写，资料太少。官网也搜不到详细的介绍信息。

只有相关hint的时候提到了一下：SQL调优连接方法hint

拓展

当半连接的关联条件为等价连接则可以使用HASH JOIN算法了。

但是需要各自添加hint。

Oracle                                                                                                                      达梦：
select * from dgd                                                                                                           explain select /*+ NO_USE_CVT_VAR OPTIMIZER_MODE(1) */ * from dgd
where exists (select /*+ unnest hash_sj */ 1                                                                                where exists (select  1
from p, s                                                                                                                   from p, s
where p.id = s.id                                                                                                           where p.id = s.id
and dgd.hsstr = s.hs_str );                                                                                                 and dgd.hsstr = s.hs_str );
                                                                                                                            
                                                                                                                            1   #NSET2: [1, 1, 108] 
Plan hash value: 1223244540                                                                                                 2     #PRJT2: [1, 1, 108]; exp_num(3), is_atom(FALSE) 
                                                                                                                            3       #HASH LEFT SEMI JOIN2: [1, 1, 108]; KEY_NUM(1);  KEY(DGD.HSSTR=DMTEMPVIEW_889204724.colname) KEY_NULL_EQU(0)
---------------------------------------------------------------------------------------------------------------------       4         #CSCN2: [1, 1, 108]; INDEX33557305(DGD)
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       5         #PRJT2: [1, 1, 108]; exp_num(1), is_atom(FALSE) 
---------------------------------------------------------------------------------------------------------------------       6           #HASH2 INNER JOIN: [1, 1, 108];  KEY_NUM(1); KEY(P.ID=S.ID) KEY_NULL_EQU(0)
|   0 | SELECT STATEMENT     |         |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |       7             #CSCN2: [1, 1, 30]; INDEX33557306(P)
|*  1 |  HASH JOIN SEMI      |         |      1 |      1 |      0 |00:00:00.01 |       1 |  1160K|  1160K|  451K (0)|       8             #CSCN2: [1, 1, 78]; INDEX33557308(S)
|   2 |   TABLE ACCESS FULL  | DGD     |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |       
|   3 |   VIEW               | VW_SQ_1 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |       used time: 1.409(ms). Execute id is 0.
|*  4 |    HASH JOIN         |         |      0 |      1 |      0 |00:00:00.01 |       0 |  1393K|  1393K|          |
|   5 |     TABLE ACCESS FULL| P       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |     TABLE ACCESS FULL| S       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DGD"."HSSTR"="ITEM_1")
   4 - access("P"."ID"="S"."ID")