View an execution plan|V3.2.4|OceanBase Database| docs|Distributed Database

View an execution plan

Last Updated：2023-10-24 09:23:03 Updated

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 the 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 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 
  | OUTLINE
  | EXTENDED
  | EXTENDED_NOADDR
  | PARTITIONS 
  | FORMAT = { TRADITIONAL | JSON }

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 PARTITIONS command to check queries related to partitions. If you check queries for non-partition tables, no error is returned. The values of the PARTITIONS column are always NULL.

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.