# 回表BLKUP2

## 前提

執行計劃的每一行，就是一個計劃節點，計劃節點裡的操作符，就是這個計劃節點幹了什麼事。  
當select語句還要求查詢建立的聯合索引以外其他欄位的值，因此就產生了回表操作，即二次掃描（操作符BLKUP2）。

### 例項1
```
SELECT   
	taskList.SALESPERSON_NAME,
	taskList.PROVINCE_BUSINESS_OFFICE_NAME,
	taskList.BRANCH_NAME,
	taskList.TEAM_NAME,
	taskList.DATA_SOURCE,
	taskList.customer_name,
	taskList.LICENSE_PLATE_NO,
	taskList.CREATE_DATE,
	tmp.firstCall,
	tmp.lastCall,
	tmp.contactNum 
FROM
	TABLE1 PARTITION(P_61000000) taskList
LEFT JOIN TABLE2 trialcalc ON
	trialcalc.TASK_ID = taskList.TASK_ID
	AND trialcalc.SCHEME_USR_STATUS = '1'
LEFT JOIN TABLE3 naiOrder ON
	naiOrder.SS_ID = taskList.TASK_ID
LEFT JOIN (
	SELECT
		track.task_id,
		min(track.TRACK_TIME) firstCall,                 
		max(track.TRACK_TIME) lastCall,
		count(track.task_id) contactNum
	FROM
		TABLE4 track    --188585條資料
	WHERE
		1 = 1
		AND track.PROVINCE_BUSINESS_OFFICE = '61000000'
	GROUP BY
		track.task_id ) tmp ON
	tmp.task_id = taskList.task_id
WHERE
	taskList.CREATE_DATE >= '2022-02-12 00:00:00'                 --3856條資料
	AND taskList.CREATE_DATE <= '2022-02-16 23:59:59'
	AND taskList.DATA_SOURCE IN ( 'EAO' , 'EAK' )
```

執行計劃發生回表BLKUP2 
```
1   #NSET2: [1123, 4795, 504] 
2     #PRJT2: [1123, 4795, 504]; exp_num(11), is_atom(FALSE) 
3       #HASH LEFT JOIN2: [1123, 4795, 504]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=TMP.TASK_ID)
4         #HASH LEFT JOIN2: [74, 4795, 396]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=NAIORDER.SS_ID)
5           #INDEX JOIN LEFT JOIN2: [66, 4277, 396]  ret_null(0)
6             #HASH RIGHT SEMI JOIN2: [6, 4226, 396]; n_keys(1)   KEY(DMTEMPVIEW_17649939.colname=TASKLIST.DATA_SOURCE) KEY_NULL_EQU(0)
7               #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
8               #BLKUP2: [6, 4261, 396]; INDEX33557132_33557101(TASKLIST)
9                 #SSEK2: [6, 4261, 396]; scan_type(ASC), INDEX33557132_33557101(TABLE1_P_61000000 as TASKLIST), scan_range[exp_cast('2022-02-12 00:00:00'),exp_cast('2022-02-16 23:59:59')]
10            #SSEK2: [18, 1, 0]; scan_type(ASC), I_TRIALCALCSCHEME_LH(TABLE2 as TRIALCALC), scan_range[(TASKLIST.TASK_ID,'1',min),(TASKLIST.TASK_ID,'1',max))
11          #SSCN: [4, 36958, 48]; IDX_TS_NAI_ORDER_SS_ID(TS_NAI_ORDER as NAIORDER)
12        #PRJT2: [982, 837021, 108]; exp_num(4), is_atom(FALSE) 
13          #SAGR2: [982, 837021, 108]; grp_num(1), sfun_num(3) slave_empty(0) keys(TRACK.TASK_ID) 
14            #BLKUP2: [916, 837021, 108]; I_DMTASKTRACK_LH(TRACK)
15              #SSEK2: [916, 837021, 108]; scan_type(ASC), I_DMTASKTRACK_LH(TABLE4 as TRACK), scan_range[('61000000',min),('61000000',max))
```

I_TABLE4_LH 是PROVINCE_BUSINESS_OFFICE和TRACK_ID的聯合索引  
改為  
CREATE  INDEX I_TABLE4_LH2 ON TSS.TABLE4(PROVINCE_BUSINESS_OFFICE,TASK_ID, TRACK_TIME) ; 
```

1   #NSET2: [332, 4816, 504] 
2     #PRJT2: [332, 4816, 504]; exp_num(11), is_atom(FALSE) 
3       #HASH LEFT JOIN2: [332, 4816, 504]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=TMP.TASK_ID)
4         #HASH LEFT JOIN2: [74, 4816, 396]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=NAIORDER.SS_ID)
5           #INDEX JOIN LEFT JOIN2: [66, 4288, 396]  ret_null(0)
6             #HASH RIGHT SEMI JOIN2: [6, 4232, 396]; n_keys(1)   KEY(DMTEMPVIEW_17693082.colname=TASKLIST.DATA_SOURCE) KEY_NULL_EQU(0)
7               #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
8               #BLKUP2: [6, 4261, 396]; INDEX33557132_33557101(TASKLIST)
9                 #SSEK2: [6, 4261, 396]; scan_type(ASC), INDEX33557132_33557101(TABLE1_P_61000000 as TASKLIST), scan_range[exp_cast('2022-02-12 00:00:00'),exp_cast('2022-02-16 23:59:59')]
10            #SSEK2: [18, 1, 0]; scan_type(ASC), I_TRIALCALCSCHEME_LH(TABLE2 as TRIALCALC), scan_range[(TASKLIST.TASK_ID,'1',min),(TASKLIST.TASK_ID,'1',max))
11          #SSCN: [4, 36958, 48]; IDX_TS_NAI_ORDER_SS_ID(TS_NAI_ORDER as NAIORDER)
12        #PRJT2: [191, 838510, 108]; exp_num(4), is_atom(FALSE) 
13          #SAGR2: [191, 838510, 108]; grp_num(1), sfun_num(3) slave_empty(0) keys(TRACK.TASK_ID) 
14            #SSEK2: [126, 838510, 108]; scan_type(ASC), I_DMTASKTRACK_LH2(TABLE4 as TRACK), scan_range[('61000000',min,min),('61000000',max,max))
```

### 例項2
```
select
  da.departName,
  da.psonCount,
  da.psonOnlineCount,
  round(to_number(da.psonOnlineCount) / da.psonCount, 4) * 100 || '%' as onLineRate,
  round(to_number(da.callCount) / da.psonCount, 0) as calloutNum,
  round(to_number(da.answerNum) / da.psonCount, 0) as answerNum,
  round(
    to_number(da.answerNum) / nullif (da.callCount, 0),
    4
  ) * 100 || '%' as answerNumRate,
  round((to_number(da.times) / da.psonCount) / 60, 0) as timesAvea
from
  (select
      d1.DEPART_NAME as departName,
      count(distinct l.SALESPERSON_ID) as psonCount,
      count(distinct y.SALEPERSONNEL_ID) as psonOnlineCount,
      count(t.INFO_ID) as callCount,
      sum(
        decode(
          t.END_TYPE,
          '255',
          '1',
          '252',
          '1',
          '254',
          '1',
          '206',
          '1',
          '207',
          '1',
          '208',
          '1',
          '209',
          '1',
          '0'
        )
      ) as answerNum,
      sum(nvl(t.TALK_DURATION, 0)) as times
    from
      TAB0 l
      left join TAB1 y on l.SALESPERSON_ID = y.SALEPERSONNEL_ID
      and to_char(y.operation_time, 'yyyy-mm-dd') >= to_date('2022-02-01', 'yyyy-mm-dd')
      and to_char(y.operation_time, 'yyyy-mm-dd') <= to_date('2022-02-24', 'yyyy-mm-dd')
      left join TAB2 d1 on l.DEPART_ID = d1.DEPART_ID
      and d1.DEPART_CLASS = 'SEVEN'
      left join TAB2 d2 on d1.PARENT_ID = d2.DEPART_ID
      and d2.DEPART_CLASS = 'FIVE'
      left join TAB3 b on l.SALESPERSON_ID = b.SALESPERSON_ID
      and b.VOICE_TYPE = '1'
      left join TAB4 t on b.CTI_ID = t.AGENT_ID
      and t.CALL_TYPE = '1'
      and to_char(t.create_time, 'yyyy-mm-dd') >= to_date('2022-02-01', 'yyyy-mm-dd')
      and to_char(t.create_time, 'yyyy-mm-dd') <= to_date('2022-02-24', 'yyyy-mm-dd')
    where
      l.DELETE_FLAG = '0'
      and l.DEPART_ID = '1500000001'
    group by
      d1.DEPART_NAME
      )da
```

原本的執行計劃
```

1   #NSET2: [56, 1, 204] 
2     #PRJT2: [56, 1, 204]; exp_num(8), is_atom(FALSE) 
3       #PRJT2: [56, 1, 204]; exp_num(6), is_atom(FALSE) 
4         #SAGR2: [56, 1, 204]; grp_num(1), sfun_num(5) slave_empty(0) keys(DMTEMPVIEW_18954145.TMPCOL0) 
5           #SORT3: [56, 1, 204]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
6             #PRJT2: [55, 1, 204]; exp_num(6), is_atom(FALSE) 
7               #INDEX JOIN LEFT JOIN2: [55, 1, 204] join condition((exp_cast(exp11) >= exp11 AND exp_cast(exp11) <= exp11)) ret_null(0)
8                 #INDEX JOIN LEFT JOIN2: [55, 1, 204]  ret_null(0)
9                   #INDEX JOIN LEFT JOIN2: [54, 1, 204]  ret_null(0)
10                    #HASH LEFT JOIN2: [54, 1, 204]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(L.SALESPERSON_ID=Y.SALEPERSONNEL_ID)
11                      #BLKUP2: [1, 1, 144]; I_SPSALESPERSONNEL_LH(L)
12                        #SSEK2: [1, 1, 144]; scan_type(ASC), I_SPSALESPERSONNEL_LH(TAB0 as L), scan_range[('1500000001','0'),('1500000001','0')]
13                      #SLCT2: [53, 1062, 60]; (var2 >= var8 AND var2 <= var9)
14                        #SSCN: [53, 424298, 60]; IDX_ZZJ_20220302_SP_USER_TRAJECTORY_01(SP_USER_TRAJECTORY as Y)
15                    #BLKUP2: [1, 1, 0]; IDX_ZZJ_20220302_TAB2_01(D1)
16                      #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_ZZJ_20220302_TAB2_01(TAB2 as D1), scan_range[('SEVEN','1500000001',min),('SEVEN','1500000001',max))
17                  #SSEK2: [1, 1, 0]; scan_type(ASC), I_SPSALESPERSONCTI_LH2(TAB3 as B), scan_range[('1',L.SALESPERSON_ID,min),('1',L.SALESPERSON_ID,max))
18                #BLKUP2: [1, 42, 0]; I_QNTELEPHONEINFO_LH3(T)
19                  #SSEK2: [1, 42, 0]; scan_type(ASC), I_QNTELEPHONEINFO_LH3(TAB4 as T), scan_range[(B.CTI_ID,exp_cast('1'),min),(B.CTI_ID,exp_cast('1'),max))
```
其中I_TAB4_LH3涉及索引  
CREATE  INDEX I_TAB4_LH3 ON TSS.TAB4(AGENT_ID,CALL_TYPE,TO_CHAR(CREATE_TIME, 'yyyy-mm-dd')) ;

優化SQL後
增加索引, 去掉回表
CREATE  INDEX IDX_TAB4_LH ON TSS.TAB4(CREATE_TIME ,CALL_TYPE ,AGENT_ID ,INFO_ID ,END_TYPE ,TALK_DURATION) ;
```
select  /*+PHC_MODE_ENFORCE(4)*/
  da.departName,
  da.psonCount,
  da.psonOnlineCount,
  round(to_number(da.psonOnlineCount) / da.psonCount, 4) * 100 || '%' as onLineRate,
  round(to_number(da.callCount) / da.psonCount, 0) as calloutNum,
  round(to_number(da.answerNum) / da.psonCount, 0) as answerNum,
  round(
    to_number(da.answerNum) / nullif (da.callCount, 0),
    4
  ) * 100 || '%' as answerNumRate,
  round((to_number(da.times) / da.psonCount) / 60, 0) as timesAvea
from
  (select
      d1.DEPART_NAME as departName,
      count(distinct l.SALESPERSON_ID) as psonCount,
      count(distinct y.SALEPERSONNEL_ID) as psonOnlineCount,
      count(t.INFO_ID) as callCount,
      sum(
        decode(
          t.END_TYPE,
          '255',
          '1',
          '252',
          '1',
          '254',
          '1',
          '206',
          '1',
          '207',
          '1',
          '208',
          '1',
          '209',
          '1',
          '0'
        )
      ) as answerNum,
      sum(nvl(t.TALK_DURATION, 0)) as times
    from
      TAB0 l
      left join TAB1 y on l.SALESPERSON_ID = y.SALEPERSONNEL_ID
      and y.operation_time>= to_date('2022-02-01', 'yyyy-mm-dd hh:mi:ss')
      and y.operation_time<= to_date('2022-02-25', 'yyyy-mm-dd hh:mi:ss')
      left join TAB2 d1 on l.DEPART_ID = d1.DEPART_ID
      and d1.DEPART_CLASS = 'SEVEN'
      left join TAB2 d2 on d1.PARENT_ID = d2.DEPART_ID
      and d2.DEPART_CLASS = 'FIVE'
      left join TAB3 b on l.SALESPERSON_ID = b.SALESPERSON_ID
      and b.VOICE_TYPE = '1'
      left join TAB4 index IDX_ZZJ_20220302_TAB4_01 t on b.CTI_ID = t.AGENT_ID
      and t.CALL_TYPE = '1'
      and t.create_time >= to_date('2022-02-01', 'yyyy-mm-dd hh:mi:ss')
      and t.create_time <= to_date('2022-02-25', 'yyyy-mm-dd hh:mi:ss')
    where
      l.DELETE_FLAG = '0' and l.DEPART_ID = '1500000001'
    group by
      d1.DEPART_NAME
      )da
      
```

優化後的SQL執行計劃
```

1   #NSET2: [327, 1, 756] 
2     #PRJT2: [327, 1, 756]; exp_num(8), is_atom(FALSE) 
3       #PRJT2: [327, 1, 756]; exp_num(6), is_atom(FALSE) 
4         #SAGR2: [327, 1, 756]; grp_num(1), sfun_num(5) slave_empty(0) keys(DMTEMPVIEW_18955211.TMPCOL0) 
5           #SORT3: [327, 1, 756]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
6             #PRJT2: [326, 149, 756]; exp_num(6), is_atom(FALSE) 
7               #HASH LEFT JOIN2: [326, 149, 756]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(B.CTI_ID=T.AGENT_ID)
8                 #HASH LEFT JOIN2: [236, 33, 540]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(L.SALESPERSON_ID=B.SALESPERSON_ID)
9                   #NEST LOOP LEFT JOIN2: [235, 33, 396];  partition_keys_num(0) ret_null(0)
10                    #HASH LEFT JOIN2: [12, 33, 204]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(L.SALESPERSON_ID=Y.SALEPERSONNEL_ID)
11                      #BLKUP2: [1, 1, 144]; I_SPSALESPERSONNEL_LH(L)
12                        #SSEK2: [1, 1, 144]; scan_type(ASC), I_SPSALESPERSONNEL_LH(TAB0 as L), scan_range[('1500000001','0'),('1500000001','0')]
13                      #SSEK2: [7, 52878, 60]; scan_type(ASC), IDX_ZZJ_20220302_SP_USER_TRAJECTORY_01(SP_USER_TRAJECTORY as Y), scan_range[(exp11,min),(exp11,max))
14                    #SLCT2: [1, 1, 192]; D1.DEPART_CLASS = 'SEVEN'
15                      #BLKUP2: [1, 1, 192]; INDEX33556270(D1)
16                        #SSEK2: [1, 1, 192]; scan_type(ASC), INDEX33556270(TAB2 as D1), scan_range['1500000001','1500000001']
17                  #SSEK2: [1, 48, 144]; scan_type(ASC), I_SPSALESPERSONCTI_LH2(TAB3 as B), scan_range[('1',min,min),('1',max,max))
18                #SLCT2: [87, 11556, 216]; T.CALL_TYPE = var8
19                  #SSEK2: [87, 461463, 216]; scan_type(ASC), IDX_ZZJ_20220302_TAB4_01(TAB4 as T), scan_range[(exp11,min,min,min,min,min),(exp11,max,max,max,max,max))
```

## 總結

合理的減少回表BLKUP2的計劃操作，可以減少語句的執行成本，減少記憶體消耗，加快執行時間。

達夢SQL優化-回表BLKUP2

相關文章