达梦数据库对outer join做了优化处理：
SQL>explain select * from test_obj1 a left join test_obj2 s
2 on a.object_id=S.object_id
3 where s.object_name in ('N','TEST1');
explain select * from test_obj1 a left join test_obj2 s
on a.object_id=S.object_id
where s.object_name in ('N','TEST1');
#RSET:[304, 174, 2];
#HCLP:[304, 174, 2];
#CSEK(SECOND):[8, 4, 2]; I_TEST_OBJ2(TEST_OBJ2), INDEX_IN_SEARCH
#CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
time used: 1.303(ms) clock tick:2653028.
右表的等于和in条件都能正确处理，使用了右表做驱动
SQL>create index i_test_obj1_1 on test_obj1(object_id);
create index i_test_obj1_1 on test_obj1(object_id);
time used: 615.980(ms) clock tick:1177526624.
SQL>explain select * from test_obj1 a left join test_obj2 s
2 on a.object_id=S.object_id
3 where s.object_name in ('N','TEST1');
explain select * from test_obj1 a left join test_obj2 s
on a.object_id=S.object_id
where s.object_name in ('N','TEST1');
#RSET:[9, 1, 2];
#XNLP:[9, 1, 2]; CROSS_JOIN
#CSEK(SECOND):[8, 4, 2]; I_TEST_OBJ2(TEST_OBJ2), INDEX_IN_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH
time used: 9.542(ms) clock tick:19527619.
左表关联字段的索引也能够正常使用
SQL>explain select * from test_obj1 a left join test_obj2 s
2 on a.object_id=S.object_id
3 where s.object_name in ('N','TEST1');
explain select * from test_obj1 a left join test_obj2 s
on a.object_id=S.object_id
where s.object_name in ('N','TEST1');
#RSET:[304, 174, 2];
#HCLP:[304, 174, 2];
#CSEK(SECOND):[8, 4, 2]; I_TEST_OBJ2(TEST_OBJ2), INDEX_IN_SEARCH
#CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
time used: 1.303(ms) clock tick:2653028.
右表的等于和in条件都能正确处理，使用了右表做驱动
SQL>create index i_test_obj1_1 on test_obj1(object_id);
create index i_test_obj1_1 on test_obj1(object_id);
time used: 615.980(ms) clock tick:1177526624.
SQL>explain select * from test_obj1 a left join test_obj2 s
2 on a.object_id=S.object_id
3 where s.object_name in ('N','TEST1');
explain select * from test_obj1 a left join test_obj2 s
on a.object_id=S.object_id
where s.object_name in ('N','TEST1');
#RSET:[9, 1, 2];
#XNLP:[9, 1, 2]; CROSS_JOIN
#CSEK(SECOND):[8, 4, 2]; I_TEST_OBJ2(TEST_OBJ2), INDEX_IN_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH
time used: 9.542(ms) clock tick:19527619.
左表关联字段的索引也能够正常使用
但是还存在不足：
达梦还是有些地方没有处理好
explain select * from test_obj1 a ,test_obj2 s,test_obj_s b
where a.object_id=S.object_id(+)
and s.object_id=b.object_id
;
#RSET:[487, 341, 11];
#HCLP:[487, 341, 11];
#CSEK:[2, 2, 11]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#HCLP:[355, 339, 13176];
#CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
#CSEK:[169, 169, 13178]; INDEX33555492(TEST_OBJ2), FULL_SCAN
time used: 1.451(ms) clock tick:2959810.
left join下执行路径与oracle 9i差不多，而oracle在10G以后就都没问题了
理想的执行计划应该如下面的先访问小表test_obj_s然后利用object_id列的索引Nest loop join test_obj2表，再NLJ test_obj1
explain select * from test_obj1 a ,test_obj2 s,test_obj_s b
where a.object_id=S.object_id
and s.object_id=b.object_id;
#RSET:[4, 1, 11];
#XFLT:[0, 0, 0]; EXPR18 = EXPR33
#XNLP:[4, 1, 11]; CROSS_JOIN
#XNLP:[3, 1, 11]; CROSS_JOIN
#CSEK:[2, 2, 11]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#CSEK(SECOND):[0, 0, 0]; I_TEST_OBJ2_2(TEST_OBJ2), INDEX_EQU_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH
time used: 2.141(ms) clock tick:4370581.
MYSQL里的null-rejected Conditions ，完全能被转换为inner join的：
explain select * from test_obj1 a , test_obj_s s
where a.object_id=S.object_id(+)
and s.object_name is not null;
#RSET:[302, 172, 11];
#XFLT:[0, 0, 0]; NOT(IS NULL(EXPR1 ))
#HASH RIGHT JOIN[HRO]:[302, 172, 11];
#CSEK:[2, 2, 11]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
time used: 0.522(ms) clock tick:1062118.
SQL>explain select * from test_obj1 a , test_obj_s s
2 where a.object_id=S.object_id
3 and s.object_name is not null;
explain select * from test_obj1 a , test_obj_s s
where a.object_id=S.object_id
and s.object_name is not null;
#RSET:[3, 1, 11];
#XNLP:[3, 1, 11]; CROSS_JOIN
#CSEK:[2, 2, 0]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH
time used: 0.583(ms) clock tick:1185674.
SQL>
explain select * from test_obj1 a ,test_obj2 s,test_obj_s b
where a.object_id=S.object_id(+)
and s.object_id=b.object_id
;
#RSET:[487, 341, 11];
#HCLP:[487, 341, 11];
#CSEK:[2, 2, 11]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#HCLP:[355, 339, 13176];
#CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
#CSEK:[169, 169, 13178]; INDEX33555492(TEST_OBJ2), FULL_SCAN
time used: 1.451(ms) clock tick:2959810.
left join下执行路径与oracle 9i差不多，而oracle在10G以后就都没问题了
理想的执行计划应该如下面的先访问小表test_obj_s然后利用object_id列的索引Nest loop join test_obj2表，再NLJ test_obj1
explain select * from test_obj1 a ,test_obj2 s,test_obj_s b
where a.object_id=S.object_id
and s.object_id=b.object_id;
#RSET:[4, 1, 11];
#XFLT:[0, 0, 0]; EXPR18 = EXPR33
#XNLP:[4, 1, 11]; CROSS_JOIN
#XNLP:[3, 1, 11]; CROSS_JOIN
#CSEK:[2, 2, 11]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#CSEK(SECOND):[0, 0, 0]; I_TEST_OBJ2_2(TEST_OBJ2), INDEX_EQU_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH
time used: 2.141(ms) clock tick:4370581.
MYSQL里的null-rejected Conditions ，完全能被转换为inner join的：
explain select * from test_obj1 a , test_obj_s s
where a.object_id=S.object_id(+)
and s.object_name is not null;
#RSET:[302, 172, 11];
#XFLT:[0, 0, 0]; NOT(IS NULL(EXPR1 ))
#HASH RIGHT JOIN[HRO]:[302, 172, 11];
#CSEK:[2, 2, 11]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
time used: 0.522(ms) clock tick:1062118.
SQL>explain select * from test_obj1 a , test_obj_s s
2 where a.object_id=S.object_id
3 and s.object_name is not null;
explain select * from test_obj1 a , test_obj_s s
where a.object_id=S.object_id
and s.object_name is not null;
#RSET:[3, 1, 11];
#XNLP:[3, 1, 11]; CROSS_JOIN
#CSEK:[2, 2, 0]; INDEX33555494(TEST_OBJ_S), FULL_SCAN
#CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH
time used: 0.583(ms) clock tick:1185674.
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27378/viewspace-683843/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
