ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 达梦对于outer join 的优化 及其不足

达梦对于outer join 的优化 及其不足

原创 Linux操作系统 作者:anlinew 时间:2011-01-14 15:59:55 0 删除 编辑
达梦数据库对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.
左表关联字段的索引也能够正常使用
 
但是还存在不足:
 
达梦还是有些地方没有处理好

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博客 ” ,链接:http://blog.itpub.net/27378/viewspace-683843/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-06

  • 博文量
    28
  • 访问量
    71222