测试 达梦 complex_view_merging

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27378/viewspace-683842/