All Products
Search
Document Center

EXPLAIN

Last Updated: Jun 18, 2021

Description

This statement explains the execution plan of an SQL statement, such as a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.

Syntax

Retrieve the information about a table or a column:
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]

Retrieve the information about an SQL plan:
{EXPLAIN} 
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}] 
{SELECT statement | DELETE statement | INSERT statement | UPDATE statement | MERGE statement}

Parameter description

Parameter

Description

tbl_name

Specifies the table name.

col_name

Specifies the column name of the table.

BASIC

Specifies the basic information about the output plan, such as the operator ID, operator name, and referenced table name.

OUTLINE

Specifies that the output plan information includes the outline information.

EXTENDED

Specifies that the EXPLAIN statement generates additional information. The additional information includes the input and output columns for each operator, the partition information about the accessed table, and the current used filter information. If the current operator uses an index, the used index column and the extracted query range appear.

EXTENDED_NOADDR

Displays the additional information in a simple way.

PARTITIONS

Displays the partition-related information.

FORMAT = {TRADITIONAL| JSON}

Specifies the output format of EXPLAIN:

  • TRADITIONAL: the table output format.

  • The KEY:VALUE output format. The output appears as JSON strings that contain EXTENDED and PARTITIONS information.

Examples

  • Omit explain_type

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 output row of EXPLAIN provides the information about a table. Each row contains the following columns:

Column name

Description

ID

The execution serial number of the plan.

OPERATOR

The executed operator.

NAME

The table that is referenced by the operator.

EST.ROWS

The estimated number of rows that are returned by the current operator.

COST

The CPU time that is consumed to execute the current operator.