本文为您介绍如何评估分区剪裁合理性。

背景信息

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';
    分区剪裁

    从执行计划中可见,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';
        **

        由上图可见,左表进行了全表扫描,只有右表的分区裁剪有效果。

      • 分区剪裁条件均放在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';
        &&

        由上图可见,两张表的分区裁剪都有效果。

    • 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)