EXPLAIN|V4.2.0|OceanBase Database| docs|Distributed Database

Purpose

You can use this statement to obtain the execution plan of an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE or UPDATE statement.

EXPLAIN, DESCRIBE, and DESC are synonyms.

Syntax

/*Obtain table or column information.*/
{EXPLAIN | DESCRIBE | DESC} table_name [column_name];

/*Obtain the SQL plan.*/
{EXPLAIN [INTO table_name ] [SET statement_id = string]  
 | DESCRIBE
 | DESC} [explain_type] [PRETTY | PRETTY_COLOR] dml_statement;

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

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

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name of the table.
INTO table_name	The table where the execution plan obtained by `EXPLAIN` is to be stored. If you do not specify `INTO table_name`, the execution plan is stored in the `PLAN_TABLE` table by default.
SET statement_id	The statement ID of the explained SQL statement, which can be used to query the execution plan of the statement later. If you do not specify `SET statement_id`, an empty string is used as the statement ID by default.
PRETTY \| PRETTY_COLOR	Specifies to connect the parent and child nodes in the plan tree with tree lines or colored tree lines to make the execution plan easier to read.
BASIC	The basic information about the output plan, such as the operator ID, operator name, and name of the referenced table.
explain_type	The explanation type.
BASIC	Outputs basic information about the execution plan, such as the operator ID, operator name, and name of the referenced table.
OUTLINE	Outputs execution plan information, with the outline included.
EXTENDED	The extra information generated by `EXPLAIN`, including the input and output columns of each operator, partition information about the access table, and the active filter. If the current operator uses an index, the used index columns and extracted query range are displayed. If the current operator references an index, the referenced index and the extracted query range are displayed in the information.
EXTENDED_NOADDR	Specifies to display the brief extra information.
PARTITIONS	Specifies to display the partition-related information.
TRADITIONAL \| JSON	The output format of `EXPLAIN`. Valid values: `TRADITIONAL`: presents the output in tabular format. `JSON`: presents the output in `KEY:VALUE` format. The output is displayed as `JSON` strings, including the `EXTENDED` and `PARTITIONS` information.
dml_statement	The DML statement.

Examples

Explain the query plan and store the plan information in the test table.

/* Create the `test` table. */
obclient> CREATE TABLE test (statement_id VARCHAR(30), plan_id INT, gmt_create TIMESTAMP,
   remarks VARCHAR(4000), operator VARCHAR(255), options VARCHAR(255), object_node VARCHAR(40),
   object_owner VARCHAR(128), object_name VARCHAR(128), object_alias VARCHAR(261), object_instance INT,
   object_type VARCHAR(30),optimizer VARCHAR(4000), search_columns INT, id INT, parent_id INT, depth INT,
   position INT,is_last_child INT, cost INT, cardinality INT, bytes INT, rowset INT, other_tag VARCHAR(4000),
   partition_start VARCHAR(4000), partition_stop VARCHAR(4000), partition_id INT, other VARCHAR(4000),
   distribution VARCHAR(64), cpu_cost INT, io_cost INT, temp_space INT, access_predicates VARCHAR(4000),
   filter_predicates VARCHAR(4000),startup_predicates VARCHAR(4000), projection VARCHAR(4000),
   special_predicates VARCHAR(4000), time INT, qblock_name VARCHAR(128), other_xml VARCHAR(4000));
Query OK, 0 rows affected

obclient> CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected

/* If the time format is incorrect, run the following command to reset the TIMESTAMP format. */
obclient> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected

/* Explain the query plan and store the plan information in the `test` table. */
obclient> EXPLAIN INTO test SELECT COUNT(*) FROM t1;
+--------------------------------------------------------------------------+
| Query Plan                                                               |
+--------------------------------------------------------------------------+
| =================================================                        |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                        |
| -------------------------------------------------                        |
| |0 |SCALAR GROUP BY  |    |1       |2           |                        |
| |1 |└─TABLE FULL SCAN|T1  |1       |2           |                        |
| =================================================                        |
| Outputs & filters:                                                       |
| -------------------------------------                                    |
|   0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 |
|       group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))])            |
|   1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                  |
|       access(nil), partitions(p0)                                        |
|       is_index_back=false, is_global_index=false,                        |
|       range_key([T1.__pk_increment]), range(MIN ; MAX)always true        |
+--------------------------------------------------------------------------+
14 rows in set

obclient> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'test'));
+--------------------------------------------------------------------------+
| COLUMN_VALUE                                                             |
+--------------------------------------------------------------------------+
| =================================================                        |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                        |
| -------------------------------------------------                        |
| |0 |SCALAR GROUP BY  |    |1       |2           |                        |
| |1 |└─TABLE FULL SCAN|T1  |1       |2           |                        |
| =================================================                        |
| Outputs & filters:                                                       |
| -------------------------------------                                    |
|   0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 |
|       group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))])            |
|   1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                  |
|       access(nil), partitions(p0)                                        |
|       is_index_back=false, is_global_index=false,                        |
|       range_key([T1.__pk_increment]), range(MIN ; MAX)always true        |
+--------------------------------------------------------------------------+
14 rows in set

Explain an SQL query statement and set the statement ID.

obclient> EXPLAIN INTO test SET statement_id='test2' SELECT COUNT(*) FROM t1;
+--------------------------------------------------------------------------+
| Query Plan                                                               |
+--------------------------------------------------------------------------+
| =================================================                        |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                        |
| -------------------------------------------------                        |
| |0 |SCALAR GROUP BY  |    |1       |2           |                        |
| |1 |└─TABLE FULL SCAN|T1  |1       |2           |                        |
| =================================================                        |
| Outputs & filters:                                                       |
| -------------------------------------                                    |
|   0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 |
|       group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))])            |
|   1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                  |
|       access(nil), partitions(p0)                                        |
|       is_index_back=false, is_global_index=false,                        |
|       range_key([T1.__pk_increment]), range(MIN ; MAX)always true        |
+--------------------------------------------------------------------------+
14 rows in set

obclient> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'test',statement_id=>'test2'));
+--------------------------------------------------------------------------+
| COLUMN_VALUE                                                             |
+--------------------------------------------------------------------------+
| =================================================                        |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                        |
| -------------------------------------------------                        |
| |0 |SCALAR GROUP BY  |    |1       |2           |                        |
| |1 |└─TABLE FULL SCAN|T1  |1       |2           |                        |
| =================================================                        |
| Outputs & filters:                                                       |
| -------------------------------------                                    |
|   0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 |
|       group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))])            |
|   1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                  |
|       access(nil), partitions(p0)                                        |
|       is_index_back=false, is_global_index=false,                        |
|       range_key([T1.__pk_increment]), range(MIN ; MAX)always true        |
+--------------------------------------------------------------------------+
14 rows in set

Obtain the information about the tbl1 table.

obclient> CREATE TABLE tbl1(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 INT,col2 INT);
Query OK, 0 rows affected

```sql
obclient> EXPLAIN tbl1;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| COL1  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
| COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
2 rows in set

Obtain the information about the col2 column of the tbl2 table.

obclient> EXPLAIN tbl2 col2;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
1 row in set

Omit explain_type and return the execution plan of the SELECT statement.

obclient> EXPLAIN SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
*************************** 1. row ***************************
Query Plan:
=======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |2442404|
|1 | TABLE SCAN|TBL2|10000    |40790  |
|2 | TABLE SCAN|TBL1|100000   |38681  |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([TBL1.COL1], [TBL1.COL2], [TBL2.COL1], [TBL2.COL2]), filter(nil),
      equal_conds([TBL1.COL2 = TBL2.COL2]), other_conds(nil)
  1 - output([TBL2.COL2], [TBL2.COL1]), filter([TBL2.COL1 > 4]),
      access([TBL2.COL2], [TBL2.COL1]), partitions(p0)
  2 - output([TBL1.COL2], [TBL1.COL1]), filter(nil),
      access([TBL1.COL2], [TBL1.COL1]), partitions(p0)

1 row in set

Use the EXTENDED_NOADDR keyword to output additional information.

obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
*************************** 1. row ***************************
Query Plan:
=======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |2442404|
|1 | TABLE SCAN|TBL2|10000    |40790  |
|2 | TABLE SCAN|TBL1|100000   |38681  |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([TBL1.COL1], [TBL1.COL2], [TBL2.COL1], [TBL2.COL2]), filter(nil),
      equal_conds([TBL1.COL2 = TBL2.COL2]), other_conds(nil)
  1 - output([TBL2.COL2], [TBL2.COL1]), filter([TBL2.COL1 > 4]),
      access([TBL2.COL2], [TBL2.COL1]), partitions(p0),
      is_index_back=false, filter_before_indexback[false],
      range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true
  2 - output([TBL1.COL2], [TBL1.COL1]), filter(nil),
      access([TBL1.COL2], [TBL1.COL1]), partitions(p0),
      is_index_back=false,
      range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true

1 row in set

Show the execution plan of the INSERT statement in TRADITIONAL format.

obclient> EXPLAIN FORMAT=TRADITIONAL INSERT INTO TBL1 VALUES(1,1)\G
*************************** 1. row ***************************
Query Plan:
====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST|
------------------------------------
|0 |INSERT     |    |1        |1   |
|1 | EXPRESSION|    |1        |1   |
====================================

Outputs & filters:
-------------------------------------
  0 - output([column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1))], [column_conv(NUMBER,PS:(38,0),NULL,__values.COL1)], [column_conv(NUMBER,PS:(38,0),NULL,__values.COL2)]), filter(nil),
      columns([{TBL1: ({TBL1: (TBL1.__pk_increment, TBL1.COL1, TBL1.COL2)})}]), partitions(p0)
  1 - output([__values.COL1], [__values.COL2]), filter(nil)
      values({1, 1})

1 row in set

Show the execution plan of the SELECT statement in JSON format.

obclient> EXPLAIN FORMAT=JSON SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
*************************** 1. row ***************************
Query Plan: {
  "ID":2,
  "OPERATOR":"JOIN",
  "NAME":"JOIN",
  "EST.ROWS":9800999,
  "COST":2442403,
  "output": [
    "TBL1.COL1",
    "TBL1.COL2",
    "TBL2.COL1",
    "TBL2.COL2"
  ],
  "CHILD_1": {
    "ID":0
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":10000,
    "COST":40789,
    "output": [
      "TBL2.COL2",
      "TBL2.COL1"
    ]
  },
  "CHILD_2": {
    "ID":1,
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":100000,
    "COST":38680,
    "output": [
      "TBL1.COL2",
      "TBL1.COL1"
    ]
  }
}
1 row in set

OceanBase

Customer Stories

Documentation

EXPLAIN

Purpose

Syntax

Parameters

Examples