In most cases, you need to analyze query statements or table schemas to find performance bottlenecks during development. MaxCompute SQL provides the EXPLAIN statement to help you analyze query statements. This topic describes the features and syntax of the EXPLAIN statement. This topic also provides examples on using the EXPLAIN statement.

Description

The EXPLAIN statement can display the execution plan structure of a DML statement in MaxCompute SQL. This statement helps you understand how an SQL statement is executed and provides guidance for optimizing SQL statements. One query statement corresponds to multiple jobs, and one job corresponds to multiple tasks.

Note If a query statement is complex, the number of rows in the output results of the EXPLAIN statement may exceed the threshold specified by the API of the upper-layer application. As a result, the output results cannot be completely displayed. To address this issue, you can split the query statement into multiple subqueries and execute the EXPLAIN statement on each subquery to obtain the structure of the job.

Syntax

explain <dml query>;

dml query: required. The SELECT statement. For more information, see SELECT syntax.

Output

The output results of the EXPLAIN statement includes the following information:
  • Dependencies between jobs

    For example, job0 is a root job. If a query requires only job0, only one row of data is displayed.

  • Dependencies between tasks 
    In Job job0:
root Tasks: M1_Stg1, M2_Stg1
J3_1_2_Stg1 depends on: M1_Stg1, M2_Stg1

    job0 contains three tasks, M1_Stg1, M2_Stg1, and J3_1_2_Stg1. MaxCompute runs the J3_1_2_Stg1 task after the M1_Stg1 and M2_Stg1 tasks are run.

    Naming conventions of tasks:
    • MaxCompute provides four task types: map, reduce, join, and local work. The first letter in a task name indicates the type of the task. For example, M2Stg1 is a map task.
    • The digit that follows the first letter indicates the task ID. This ID is unique among all tasks that correspond to a specific query.
    • Digits separated by underscores (_) represent the direct dependency of a task. For example, J3_1_2_Stg1 indicates that the task with the ID of 3 depends on the M1_Stg1 and M2_Stg1 tasks.
  • Dependencies between all operators in a task
    The operator string describes the execution semantics of a task. Operator string structure:
    In Task M2:
    Data source: mf_mc_bj.sale_detail_jt/sale_date=2013/region=china # Data source describes the input of the task. 
    TS: mf_mc_bj.sale_detail_jt/sale_date=2013/region=china           # TableScanOperator
        FIL: ISNOTNULL(customer_id)                                   # FilterOperator
            RS: order: +                                              # ReduceSinkOperator
                nullDirection: *
                optimizeOrderBy: False
                valueDestLimit: 0
                dist: HASH
                keys:
                      customer_id
                values:
                      customer_id (string)
                      total_price (double)
                partitions:
                      customer_id


In Task J3_1_2:
    JOIN:                                                           # JoinOperator
         StreamLineRead1 INNERJOIN StreamLineRead2
         keys:
             0:customer_id
             1:customer_id

        AGGREGATE: group by:customer_id                            # GroupByOperator
         UDAF: SUM(total_price) (__agg_0_sum)[Complete],SUM(total_price) (__agg_1_sum)[Complete]
            RS: order: +
                nullDirection: *
                optimizeOrderBy: True
                valueDestLimit: 10
                dist: HASH
                keys:
                      customer_id
                values:
                      customer_id (string)
                      __agg_0 (double)
                      __agg_1 (double)
                partitions:


In Task R4_3:
    SEL: customer_id,__agg_0,__agg_1                               # SelectOperator
        LIM:limit 10                                               # LimitOperator
            FS: output: Screen                                     # FileSinkOperator
                schema:
                  customer_id (string) AS ashop
                  __agg_0 (double) AS ap
                  __agg_1 (double) AS bp
    Operator descriptions:
    • TableScanOperator (TS): describes the logic of FROM statement blocks in a query statement. The alias of the input table is displayed in the output results of the EXPLAIN statement.
    • SelectOperator (SEL): describes the logic of SELECT statement blocks in a query statement. The columns that are passed to the next operator are displayed in the execution results of the EXPLAIN statement. Multiple columns are separated by commas (,).
      • If a column is specified, the value is displayed in the <alias>.<column_name> format.
      • If an expression is specified, the value is displayed as a list of functions, such as func1(arg1_1, arg1_2, func2(arg2_1, arg2_2)).
      • If a constant is specified, the constant value is displayed.
    • FilterOperator (FIL): describes the logic of WHERE statement blocks in a query statement. The output results of the EXPLAIN statement include a WHERE expression, which is in a form that is similar to that of SelectOperator.
    • JoinOperator (JOIN): describes the logic of JOIN statement blocks in a query statement. The output results of the EXPLAIN statement show which tables are joined in which way.
    • GroupByOperator (AGGREGATE): describes the logic of aggregate operations. This operator is displayed if an aggregate function is used in a query statement. The content of the aggregate function is displayed in the execution results of the EXPLAIN statement.
    • ReduceSinkOperator (RS): describes the logic of data distribution between tasks. If the result of a task is transferred to another task, ReduceSinkOperator must be used to distribute data at the last stage of the task. The result sorting method, distributed keys, distributed values, and columns that are used to calculate the hash value are displayed in the output results of the EXPLAIN statement.
    • FileSinkOperator (FS): describes the logic of storage operations on final data records. If INSERT statement blocks are included in a query statement, the name of the table into which you want to insert data is displayed in the output results of the EXPLAIN statement.
    • LimitOperator (LIM): describes the logic of LIMIT statement blocks in a query statement. The number of returned rows that are specified in a LIMIT statement block is displayed in the execution results of the EXPLAIN statement.
    • MapjoinOperator (HASHJOIN): describes JOIN operations on large tables. This operator is similar to JoinOperator.

Sample data

The following sample source data is provided to help you understand the examples in this topic. The following statements show how to create the sale_detail and sale_detail_jt tables and insert data into the tables.
-- Create two partitioned tables named sale_detail and sale_detail_jt. 
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);
create table if not exists sale_detail_jt
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

-- Add partitions to the sale_detail and sale_detail_jt tables. 
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');
alter table sale_detail_jt add partition (sale_date='2013', region='china');

-- Insert data into the sale_detail and sale_detail_jt tables. 
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
insert into sale_detail_jt partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

Query data from the sale_detail and sale_detail_jt tables. Sample statements:
select * from sale_detail;
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+
select * from sale_detail_jt;
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

-- Create a table that you want to join. 
create table shop as select shop_name, customer_id, total_price from sale_detail;

Examples

Execute the following statements based on the sample data: 
-- Execute the query statement. 
select a.customer_id as ashop, sum(a.total_price) as ap,count(b.total_price) as bp 
from (select * from sale_detail_jt where sale_date='2013' and region='china') a 
inner join (select * from sale_detail where sale_date='2013' and region='china') b 
on a.customer_id=b.customer_id 
group by a.customer_id 
order by a.customer_id 
limit 10;
-- Obtain the execution plan of the query statement. 
explain 
select a.customer_id as ashop, sum(a.total_price) as ap,count(b.total_price) as bp 
from (select * from sale_detail_jt where sale_date='2013' and region='china') a 
inner join (select * from sale_detail where sale_date='2013' and region='china') b 
on a.customer_id=b.customer_id 
group by a.customer_id 
order by a.customer_id 
limit 10;
The following result is returned: 
job0 is root job

In Job job0:
root Tasks: M1

In Task M1_U0:
    TS: doc_test_dev.sale_detail_jt/sale_date=2013/region=china
        FIL: ISNOTNULL(customer_id)
            HASHJOIN:
                     Filter1 INNERJOIN Filter2
                     keys:
                         0:customer_id
                         1:customer_id
                     non-equals:
                         0:
                         1:
                     bigTable: Filter1

                LocalSortBy: order: +
                             nullDirection: *
                             keys:customer_id
                    AGGREGATE: group by:customer_id
                     UDAF: SUM(total_price) (__agg_0_sum)[Complete],COUNT(total_price) (__agg_1_count)[Complete]
                        LIM:limit 10
                            FS: output: Screen
                                schema:
                                  customer_id (string) AS ashop
                                  __agg_0 (double) AS ap
                                  __agg_1 (bigint) AS bp


In Task M1_U1:
    TS: doc_test_dev.sale_detail/sale_date=2013/region=china
        FIL: ISNOTNULL(customer_id)
            HASHJOIN:
                     Filter1 INNERJOIN Filter2
                     keys:
                         0:customer_id
                         1:customer_id
                     non-equals:
                         0:
                         1:
                     bigTable: Filter1

                LocalSortBy: order: +
                             nullDirection: *
                             keys:customer_id
                    AGGREGATE: group by:customer_id
                     UDAF: SUM(total_price) (__agg_0_sum)[Complete],COUNT(total_price) (__agg_1_count)[Complete]
                        LIM:limit 10
                            FS: output: Screen
                                schema:
                                  customer_id (string) AS ashop
                                  __agg_0 (double) AS ap
                                  __agg_1 (bigint) AS bp
Execute the following statements based on the sample data: 
-- Execute the query statement. 
select /*+ mapjoin(a) */
       a.customer_id as ashop, sum(a.total_price) as ap,count(b.total_price) as bp 
 from (select * from sale_detail_jt 
where sale_date='2013' and region='china') a 
inner join (select * from sale_detail where sale_date='2013' and region='china') b 
on a.total_price<b.total_price 
group by a.customer_id 
order by a.customer_id 
limit 10;
-- Obtain the execution plan of the query statement. 
explain 
select /*+ mapjoin(a) */
       a.customer_id as ashop, sum(a.total_price) as ap,count(b.total_price) as bp 
 from (select * from sale_detail_jt 
where sale_date='2013' and region='china') a 
inner join (select * from sale_detail where sale_date='2013' and region='china') b 
on a.total_price<b.total_price 
group by a.customer_id 
order by a.customer_id 
limit 10;
The following result is returned: 
job0 is root job

In Job job0:
root Tasks: M1

In Task M1_U0:
    TS: doc_test_dev.sale_detail_jt/sale_date=2013/region=china
        HASHJOIN:
                 TableScan1 INNERJOIN TableScan2
                 keys:
                     0:
                     1:
                 non-equals:
                     0:
                     1:
                 bigTable: TableScan2

            FIL: LT(total_price,total_price)
                LocalSortBy: order: +
                             nullDirection: *
                             keys:customer_id
                    AGGREGATE: group by:customer_id
                     UDAF: SUM(total_price) (__agg_0_sum)[Complete],COUNT(total_price) (__agg_1_count)[Complete]
                        LIM:limit 10
                            FS: output: Screen
                                schema:
                                  customer_id (string) AS ashop
                                  __agg_0 (double) AS ap
                                  __agg_1 (bigint) AS bp


In Task M1_U1:
    TS: doc_test_dev.sale_detail/sale_date=2013/region=china
        HASHJOIN:
                 TableScan1 INNERJOIN TableScan2
                 keys:
                     0:
                     1:
                 non-equals:
                     0:
                     1:
                 bigTable: TableScan2

            FIL: LT(total_price,total_price)
                LocalSortBy: order: +
                             nullDirection: *
                             keys:customer_id
                    AGGREGATE: group by:customer_id
                     UDAF: SUM(total_price) (__agg_0_sum)[Complete],COUNT(total_price) (__agg_1_count)[Complete]
                        LIM:limit 10
                            FS: output: Screen
                                schema:
                                  customer_id (string) AS ashop
                                  __agg_0 (double) AS ap
                                  __agg_1 (bigint) AS bp