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 | Specifies the name of the table. |
| column_name | Specifies the name of the column in the table. |
| INTO table_name | Specifies that the plan information generated by EXPLAIN is stored in the specified table. If you do not specify INTO table_name, the information is stored in the PLAN_TABLE table by default. |
| SET statement_id | Specifies a string to mark the current query, which facilitates subsequent queries for the plan information of this SQL statement. If you do not specify SET statement_id, an empty string is used as the information mark by default. |
| PRETTY | PRETTY_COLOR | Connects the parent and child nodes in the plan tree with tree lines or colored tree lines, making it easier to read the execution plan. |
| explain_type | Specifies the type of explanation. |
| BASIC | Specifies to output basic plan information, such as operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies to output plan information that includes 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 in use. If the current operator uses an index, the index columns and the extracted query range are displayed. |
| EXTENDED_NOADDR | Displays the additional information in a simplified manner. |
| PARTITIONS | Displays information about partitions. |
| TRADITIONAL | JSON | Specifies the output format of EXPALIN.
|
| 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 during the timestamp formatting, 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
tbl1table.
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
col2column in thetbl2table.
obclient> EXPLAIN tbl2 col2;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| COL2 | NUMBER(38) | YES | NULL | NULL | NULL |
+-------+------------+------+-----+---------+-------+
1 row in set
- Omit the
explain_typeparameter and return the execution plan information for theSELECTstatement.
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_NOADDRkeyword.
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
INSERTstatement in theTRADITIONALformat.
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
SELECTstatement in theJSONformat.
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