View an execution plan

An execution plan describes the execution procedure of an SQL statement in OceanBase Database. Execution plans are used to analyze the execution performance of SQL statements. Understanding execution plans is the precondition of SQL optimization. You can run the EXPLAIN command to view the logical execution plan generated by the optimizer for a given SQL statement.

Syntax of the EXPLAIN command

OceanBase Database supports three EXPLAIN command formats: EXPLAIN BASIC, EXPLAIN, and EXPLAIN EXTENDED. They demonstrate details of execution plans at different levels:

• The EXPLAIN BASIC command shows the most basic framework of a plan.

• The EXPLAIN EXTENDED command extends a plan to its full frame with the most details and is usually used in troubleshooting.

• The EXPLAIN command shows information to the extent that helps users understand the entire execution process of a plan.

Syntax:

EXPLAIN [explain_type] dml_statement;

explain_type:
    BASIC [pretty_name]
  | OUTLINE [pretty_name]
  | EXTENDED [pretty_name]
  | EXTENDED_NOADDR [pretty_name]
  | PARTITIONS [pretty_name]
  | FORMAT = { TRADITIONAL | JSON }

pretty_name:
  PRETTY
  | PRETTY_COLOR

dml_statement:
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement

• The EXPLAIN command applies to the SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. This command shows information about statement execution plans provided by the optimizer, including the method of processing the statement and the method and sequence of joining tables.

• You can run the EXPLAIN EXTENDED command to display the scan range of the table and run the EXPLAIN OUTLINE command to display the outline information.

• The FORMAT option is used to specify the output format. The TRADITIONAL value is the default value and specifies to display information in a table. The JSON value specifies to display information in a JSON file.

• You can use the EXPLAIN PARTITITIONS statement to check queries related to partitioned tables. If you check queries for non-partitioned tables, no error is returned, but the values in the PARTIONS column are always NULL.

• For complex execution plans, you can use the PRETTY or PRETTY_COLOR option to connect the parent and child nodes in the plan tree with tree lines or colored tree lines, so that the execution plan is easier to read.

View an execution plan in CLI

• Run the EXPLAIN BASIC command to display an execution plan.

  obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, KEY k1(b));
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT, KEY k1(b));
  Query OK, 0 rows affected
  
  obclient> EXPLAIN BASIC SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan:
  =====================
  |ID|OPERATOR   |NAME|
  ---------------------
  |0 |MERGE JOIN |    |
  |1 | TABLE SCAN|t1  |
  |2 | TABLE SCAN|t2  |
  =====================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
        access([t1.a], [t1.b], [t1.c]), partitions(p0)
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
        access([t2.a], [t2.b], [t2.c]), partitions(p0)
  

• Run the EXPLAIN command to display an execution plan.

  obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan:
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
        access([t1.a], [t1.b], [t1.c]), partitions(p0)
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
        access([t2.a], [t2.b], [t2.c]), partitions(p0)
  

• Run the EXPLAIN EXTENDED command to display an execution plan.

  obclient> EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan:
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)], [t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), filter(nil),
        equal_conds([t1.a(0x7f1c68e79310) = t2.a(0x7f1c68e795d0)(0x7f1c68e78c20)]), other_conds(nil),
        merge_directions([ASC])
    1 - output([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)]), filter(nil),
        access([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)]), partitions(p0),
        is_index_back=false,
        range_key([t1.a(0x7f1c68e79310)]), range(MIN ; MAX)always true
    2 - output([t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), filter(nil),
        access([t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), partitions(p0),
        is_index_back=false,
        range_key([t2.a(0x7f1c68e795d0)]), range(MIN ; MAX)always true
  
  Used Hint:
  -------------------------------------
    /*+
    */
  
  Outline Data:
  -------------------------------------
    /*+
        BEGIN_OUTLINE_DATA
        LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
        USE_MERGE(@"SEL$1" ("test.t2"@"SEL$1" ))
        PQ_DISTRIBUTE(@"SEL$1" ("test.t2"@"SEL$1" ) LOCAL LOCAL)
        FULL(@"SEL$1" "test.t1"@"SEL$1")
        FULL(@"SEL$1" "test.t2"@"SEL$1")
        END_OUTLINE_DATA
    */
  
  Plan Type:
  -------------------------------------
  LOCAL
  
  Optimization Info:
  -------------------------------------
  
  
  t1:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[t1], pruned_index_name[k1]
  
  t2:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[t2], pruned_index_name[k1]
  Parameters
  -------------------------------------
  

• Run the EXPLAIN EXTENDED_NOADDR command to display an execution plan.

  obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan:
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
        access([t1.a], [t1.b], [t1.c]), partitions(p0),
        is_index_back=false,
        range_key([t1.a]), range(MIN ; MAX)always true
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
        access([t2.a], [t2.b], [t2.c]), partitions(p0),
        is_index_back=false,
        range_key([t2.a]), range(MIN ; MAX)always true
  

• Run the EXPLAIN OUTLINE command to display the outline information.

  obclient> EXPLAIN OUTLINE SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan:
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
        access([t1.a], [t1.b], [t1.c]), partitions(p0)
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
        access([t2.a], [t2.b], [t2.c]), partitions(p0)
  
  Outline Data:
  -------------------------------------
    /*+
        BEGIN_OUTLINE_DATA
        LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
        USE_MERGE(@"SEL$1" ("test.t2"@"SEL$1" ))
        PQ_DISTRIBUTE(@"SEL$1" ("test.t2"@"SEL$1" ) LOCAL LOCAL)
        FULL(@"SEL$1" "test.t1"@"SEL$1")
        FULL(@"SEL$1" "test.t2"@"SEL$1")
        END_OUTLINE_DATA
    */
  

View an execution plan in ODC

• Run the EXPLAIN BASIC command to display an execution plan.

  

• Run the EXPLAIN command to display an execution plan.

  

• Run the EXPLAIN EXTENDED command to display an execution plan.

  

• Run the EXPLAIN EXTENDED_NOADDR command to display an execution plan.

  

• Run the EXPLAIN OUTLINE command to display the outline information.