判斷分區剪裁是否失效及分析JOIN與UDF導致的原因-雲原生MaxCompute MaxCompute-阿里雲 - MaxCompute

本文為您介紹如何評估分區剪裁合理性。

背景資訊

MaxCompute分區表是指在建立表時指定分區空間，即指定表內的幾個欄位作為分區列。使用資料時，如果指定了需要訪問的分區名稱，則只會讀取相應的分區，避免全表掃描，提高處理效率，降低費用。

分區剪裁是指對分區列指定過濾條件，使得SQL執行時只用讀取表分區的部分資料，避免全表掃描引起的資料錯誤及資源浪費。但是分區失效的情況會經常發生。

本文將從以下兩方面介紹分區剪裁：

判斷分區剪裁是否生效。
分區剪裁失效的情境分析。

判斷分區剪裁是否生效

通過EXPLAIN命令查看SQL執行計畫，用於判斷SQL中的分區剪裁是否生效。

分區剪裁未生效。
```
explain
select seller_id
from xxxxx_trd_slr_ord_1d
where ds=rand();
```
從執行計畫中可見，SQL讀取了表的1344個分區，即該表的所有分區。

分區剪裁生效

explain
select seller_id
from xxxxx_trd_slr_ord_1d
where ds='20150801';

In Task M1 Stg1:
Data source:rd_slr_ord_id/dtrd_slr_ord_id/ds=20150801
TS: alias: rd_slr_ord_id/dtrd_slr_ord_id
    FIL: EQUAL(rd_slr_ord_id/dtrd_slr_ord_id.ds, '20150801')
        SEL: rd_slr_ord_id/dtrd_slr_ord_id.seller_id

從執行計畫中可見，SQL唯讀取了表的20150801分區。

分區剪裁失效的情境分析

自訂函數導致分區剪裁失效
當分區剪裁的條件中使用了使用者自訂函數（或者部分內建函數）時，分區剪裁失效。所以，對於分區值的限定，如果使用了非常規函數，建議您使用Explain命令查看執行計畫，確定分區剪裁是否生效。
```
explain
select ...
from xxxxx_base2_brd_ind_cw
where ds = concat(SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 1), SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 2))
```
說明
UDF已支援分區裁剪，詳情請參見WHERE子句（WHERE_condition）文中的說明。

使用JOIN時分區剪裁失效

在SQL語句中使用JOIN進行關聯時：

如果分區剪裁條件放在WHERE子句中，則分區剪裁會生效。
如果分區剪裁條件放在ON子句中，從表的分區剪裁會生效，主表則不會生效。

下面針對三種JOIN具體說明：

LEFT OUTER JOIN

分區剪裁條件均放在ON子句中

set odps.sql.allow.fullscan=true;
explain
select a.seller_id
    ,a.pay_ord_pbt_1d_001
from xxxxx_trd_slr_ord_1d a
left outer join
     xxxxx_seller b
on a.seller_id=b.user_id
and a.ds='20150801'
and b.ds='20150801';

執行上述 explain 語句後，任務執行計畫輸出如下，可以看到左表資料來源掃描了全部 1770 個分區，而過濾條件被下推到了 FIL 運算元中：

In Task M2_Stg1:
  Data source: xxx ller/ds=20101001, xxx seller/ds=20101002, xxx seller/ds=20101003...(total 1770)
  xxx
      RS: order: +
          optimizeOrderBy: False
        valueDestLimit: 0
        keys:
             b.user_id
        values:
             b.ds
        partitions
          b.user_id
In Task J3_1_2_Stg1:
    JOIN: a LEFT OUTER JOIN b
      filter:
          0:
          1:
      FIL: And(EQUAL(a. col96, '20150801'), EQUAL(b. col209, '20150801'))
    SEL: xxx
      FS: output: None

由上述輸出可見，左表進行了全表掃描，只有右表的分區裁剪有效果。

分區剪裁條件均放在WHERE子句中

set odps.sql.allow.fullscan=true;
explain
select a.seller_id
    ,a.pay_ord_pbt_1d_001
from xxxxx_trd_slr_ord_1d a
left outer join
    xxxxx_seller b
on a.seller_id=b.user_id
where a.ds='20150801'
and b.ds='20150801';

Task M2_Stg1:
  Data source: xxx/ds=seller/ds=20150801
  TS: alias: b
      FIL: EQUAL(b.ds, '20150801')
          RS: order: +
              optimizeOrderBy: False
              valueDestLimit: 0
              keys:
                  b.user_id
              values:
              partitions:
                  b.user_id
In Task J3_1_2_Stg1:
  JOIN: a LEFT OUTER JOIN unknown
      filter:
          0:
          1:
      SEL: a._col0, a._col20
      FS: output: None
In Task M1_Stg1:
  Data source: xxx/ds= trd slr ord 1d/ds=20150801
  TS: alias: a

由以上執行計畫可見，兩張表的分區裁剪都有效果。

RIGHT OUTER JOIN
與LEFT OUTER JOIN類似，如果分區剪裁條件放在ON子句中則只有RIGHT OUTER JOIN的左表生效。如果分區剪裁條件放在WHERE中，則兩張表都會生效。
FULL OUTER JOIN
分區剪裁條件只有都放在WHERE子句中才會生效，放在ON子句中都不會生效。

注意事項

分區剪裁如果失效影響較大，且不容易發現。因此，建議在代碼提交時關注分區剪裁失效問題。
自訂函數中使用分區剪裁時，需要修改類或者在SQL語句前設定set odps.sql.udf.ppr.deterministic = true;。詳情請參見WHERE子句（WHERE_condition）。