达梦不支持filter类型的执行路径导致慢SQL
达梦不支持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
现在发现把半连接改写为内连接在达梦则可以加快速度。
如下：
select dgd.* from dgd,p,s where p.id = s.id and dgd.hsstr || dgd.exstr like s.hs_str || '%';
找个时间再研究研究。
拓展
当半连接的关联条件为等价连接则可以使用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")
