EXPLAIN|V4.2.5| docs|Distributed Database

EXPLAIN

Last Updated：2026-02-11 07:43:39 Updated

Purpose

This statement is used to explain the execution plan of an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.

EXPLAIN is synonymous with DESCRIBE and DESC.

Syntax

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

/*Obtain information about an 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 name of the table.
column_name	The name of the column in the table.
INTO table_name	Specifies to save the plan information of `EXPLAIN` to the specified table. If you do not specify `INTO table_name`, the default is to query the `PLAN_TABLE` table.
SET statement_id	Specifies a string tag for the current query to facilitate subsequent queries of the plan information of this SQL statement. If you do not specify `SET statement_id`, the default is to use an empty string as the information tag.
PRETTY \| PRETTY_COLOR	Connects the parent and child nodes in the plan tree with tree lines or colored tree lines, making the execution plan easier to read.
explain_type	The type of the plan explanation.
BASIC	Specifies to output the basic information of the plan, such as operator ID, operator name, and referenced table name.
OUTLINE	Specifies to output the plan information with outline information.
EXTENDED	`EXPLAIN` generates additional information, including the input and output columns of each operator, the partition information of the accessed table, and the filter information currently used. If the current operator uses an index, it displays the index columns and the extracted Query Range.
EXTENDED_NOADDR	Displays the additional information in a simplified manner.
PARTITIONS	Displays information about partitions.
TRADITIONAL \| JSON	The output format of `EXPALIN`. `TRADITIONAL`: Table output format. `JSON`: `KEY:VALUE` output format. `JSON` is displayed as a `JSON` string, including `EXTENDED` and `PARTITIONS` information.
dml_statement	A DML statement.

Examples

Explain the query plan and save the plan information in the specified table test.

/* 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 an error occurs when the time format is specified, run the following command to reconfigure 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 save the plan information in the specified table test. */
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 the query plan and set the identifier information for the query.

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

Get 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

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

Get information about the col2 column in 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 information for 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

Output additional information by using the EXTENDED_NOADDR keyword.

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

Display the execution plan of the INSERT statement in the 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

Display the execution plan of the SELECT statement in the 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

EXPLAIN

Purpose

Syntax

Parameters

Examples

References