All Products
Search
Document Center

EXPLAIN

Last Updated: Jun 18, 2021

Description

You can execute the EXPLAIN statement to query the execution plans for SQL statements. EXPLAIN supports the SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

Syntax

Query a table or a column:
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]

Query the execution plan for an SQL statement:
{EXPLAIN | DESCRIBE | DESC} 
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}] 
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement}

Parameters

Parameter

Description

tbl_name

The name of the table.

col_name

The name of the table column.

BASIC

Query the basic information about the execution plan, such as the operator IDs, the operator names, and the names of the tables that are referenced.

OUTLINE

Query the information about the execution plan, including the outline.

EXTENDED

Query the additional information generated by the EXPLAIN statement. The information includes the input and output columns of each operator, the partitions of the referenced tables, and the specified filter conditions. If the current operator uses an index, the system returns the index columns and query range.

EXTENDED_NOADDR

Display the additional information in a clear way.

PARTITIONS

Query the partition information.

FORMAT = {TRADITIONAL| JSON}

The output format of the EXPLAIN statement. Valid values:

  • TRADITIONAL: displays the output in a tabular format.

  • JSON: displays the output in a JSON string that contains key-value pairs. The output information includes the additional information and partition information.

Examples

  • Leave the parameters that specify the output type empty.
OceanBase(admin@test)>explain select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), 
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil), 
      access([t2.c2], [t2.c1]), partitions(p0)
  2 - output([t1.c2], [t1.c1]), filter(nil), 
      access([t1.c2], [t1.c1]), partitions(p0)
  • EXTENDED
OceanBase(admin@test)>explain extended_noaddr select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), 
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil), 
      access([t2.c2], [t2.c1]), partitions(p0), 
      is_index_back=false, 
      range_key([t2.c1]), range(4 ; MAX), 
      range_cond([t2.c1 > 4])
  2 - output([t1.c2], [t1.c1]), filter(nil), 
      access([t1.c2], [t1.c1]), partitions(p0), 
      is_index_back=false, 
      range_key([t1.__pk_increment], [t1.__pk_cluster_id], [t1.__pk_partition_id]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
  • TRADITIONAL format
OceanBase(admin@test)>explain format=TRADITIONAL select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), 
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil), 
      access([t2.c2], [t2.c1]), partitions(p0)
  2 - output([t1.c2], [t1.c1]), filter(nil), 
      access([t1.c2], [t1.c1]), partitions(p0)
  • JSON format
OceanBase(admin@test)>explain format=JSON select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: {
  "ID":2,
  "OPERATOR":"JOIN",
  "NAME":"JOIN",
  "EST.ROWS":9800999,
  "COST":5933108,
  "output": [
    "t1.c1",
    "t1.c2",
    "t2.c1",
    "t2.c2"
  ],
  "TABLE SCAN": {
    "ID":0,
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":10000,
    "COST":6218,
    "output": [
      "t2.c2",
      "t2.c1"
    ]
  },
  "TABLE SCAN": {
    "ID":1,
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":100000,
    "COST":68477,
    "output": [
      "t1.c2",
      "t1.c1"
    ]
  }
}

Each row in the output of the EXPLAIN statement contains the following columns.

Column

Description

ID

The sequential number of the execution plan.

OPERATOR

The operator in the execution plan.

NAME

The table referenced by the operator.

EST.ROWS

The estimated number of rows in the output of the operator.

COST

The CPU time consumed by the operator.