ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 测试 达梦 complex_view_merging

测试 达梦 complex_view_merging

原创 Linux操作系统 作者:anlinew 时间:2011-01-14 15:48:12 0 删除 编辑
select object_id from test_obj1 a left join
(select sum(DATA_OBJECT_ID) sm,b.object_id  from test_obj1 b group by b.object_id) c
on a.object_id=c.object_id
where a.object_name='TEST1' and a.DATA_OBJECT_ID=c.sm
 
#RSET:[21, 1, 10];
 #XFLT:[0, 0, 0]; (EXPR1 = TEST1 AND EXPR6 = EXPR2 )
  #XNLP:[21, 1, 10]; LEFT_JOIN
   #CSEK(SECOND):[20, 20, 10]; i_test_obj1_2(TEST_OBJ1), INDEX_EQU_SEARCH
   #TTS:[0, 0, 0]; tmp_table(sorted by column 1)
    #XEVL:[0, 0, 0];
     #SAGR:[0, 0, 0]; group_by_num(1), function_num(1)
      #XSORT:[0, 0, 0]; keys_num(1), is_distinct(FALSE)
       #CSEK:[170, 170, 13176]; INDEX33555491(TEST_OBJ1), FULL_SCAN
显然,达梦没有将子查询改写合并
 
手工改写可以达到这个目的:
       
select a.object_id from test_obj1 a
    left join test_obj1 b
        on a.object_id=b.object_id
     where a.object_name='TEST1'
group by a.object_id
having max(a.DATA_OBJECT_ID)/count(*)=sum(b.DATA_OBJECT_ID)       
       
#RSET:[21, 1, 10];
 #XFLT:[0, 0, 0]; EXPR5 / EXPR6 = CAST(EXPR7 )
  #HAGR:[0, 0, 0]; group_by_num(1), function_num(3)
   #SAGR:[0, 0, 0]; group_by_num(1), function_num(3) (Part Virtual)
    #XSORT:[0, 0, 0]; keys_num(1), is_distinct(FALSE) (Virtual)
     #XFLT:[0, 0, 0]; EXPR1 = TEST1
      #XNLP:[21, 1, 10]; LEFT_JOIN
       #CSEK(SECOND):[20, 20, 10]; i_test_obj1_2(TEST_OBJ1), INDEX_EQU_SEARCH
       #CSEK(SECOND):[0, 0, 0]; i_test_obj1_1(TEST_OBJ1), INDEX_EQU_SEARCH       

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27378/viewspace-683842/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2007-12-06

  • 博文量
    28
  • 访问量
    71208