EXPLAIN
Description
This statement is used to explain the execution plan of an SQL statement. It can be a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.
EXPLAIN is a synonym for DESCRIBE and DESC.
Syntax
{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
| REPLACE statement
Parameters
| Parameter | Description |
|---|---|
| INTO table_name | Specifies the table to which the EXPLAIN plan information is saved. If not specified, the default is the PLAN_TABLE. |
| SET statement_id | Specifies a string tag for the current query to facilitate subsequent queries of the SQL plan information. If not specified, the default is an empty string as the information tag. |
| PRETTY | PRETTY_COLOR | Connects parent nodes and child nodes in the plan tree with tree lines or colored tree lines to make the execution plan easier to read. |
| BASIC | Specifies the basic information of the plan, such as operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies that the plan information includes OUTLINE information. |
| EXTENDED | EXPLAIN generates additional information, including: input and output columns for each operator, partition information for accessed tables, current Filter information, and if the current operator uses an index, the index columns and query range extracted. |
| EXTENDED_NOADDR | Displays the additional information in a simplified manner. |
| PARTITIONS | Displays partition-related information. |
| FORMAT = {TRADITIONAL| JSON} | Specifies the output format of EXPALIN:
|
| dml_statement | DML statement. |
Example
- Explain the query plan and save the plan information to the
testtable.
/* Create the test table. */
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 (0.065 sec)
CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected (0.013 sec)
/* Explain the query plan and save the plan information to the test table. */
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 (0.035 sec)
SELECT DBMS_XPLAN.DISPLAY('TYPICAL',null,'test')\G
*************************** 1. row ***************************
DBMS_XPLAN.DISPLAY('TYPICAL',null,'test'):
=================================================
|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
1 row in set (0.029 sec)
- Explain the query plan and set the statement identifier.
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
SELECT DBMS_XPLAN.DISPLAY('TYPICAL','test2','test')\G
*************************** 1. row ***************************
DBMS_XPLAN.DISPLAY('TYPICAL','test2','test'): =================================================
|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
1 row in set (0.026 sec)
- Omit
explain_typeand return the execution plan of the statement.
EXPLAIN SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH JOIN | |1 |9 | |
| |1 |├─TABLE FULL SCAN|t2 |1 |4 | |
| |2 |└─TABLE FULL SCAN|t1 |2 |4 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 |
| equal_conds([t1.c2 = t2.c2]), other_conds(nil) |
| 1 - output([t2.c2], [t2.c1]), filter([t2.c1 > 4]), rowset=16 |
| access([t2.c2], [t2.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([t1.c2], [t1.c1]), filter(nil), rowset=16 |
| access([t1.c2], [t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
- Use the
EXTENDED_NOADDRkeyword to output additional information.
EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH JOIN | |1 |9 | |
| |1 |├─TABLE FULL SCAN|t2 |1 |4 | |
| |2 |└─TABLE FULL SCAN|t1 |2 |4 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 |
| equal_conds([t1.c2 = t2.c2]), other_conds(nil) |
| 1 - output([t2.c2], [t2.c1]), filter([t2.c1 > 4]), rowset=16 |
| access([t2.c2], [t2.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([t1.c2], [t1.c1]), filter(nil), rowset=16 |
| access([t1.c2], [t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("mysql"."t2"@"SEL$1" "mysql"."t1"@"SEL$1")) |
| USE_HASH(@"SEL$1" "mysql"."t1"@"SEL$1") |
| FULL(@"SEL$1" "mysql"."t2"@"SEL$1") |
| FULL(@"SEL$1" "mysql"."t1"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t2: |
| table_rows:3 |
| physical_range_rows:3 |
| logical_range_rows:3 |
| index_back_rows:0 |
| output_rows:0 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t2] |
| stats version:0 |
| dynamic sampling level:1 |
| t1: |
| table_rows:2 |
| physical_range_rows:2 |
| logical_range_rows:2 |
| index_back_rows:0 |
| output_rows:2 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:1 |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+------------------------------------------------------------------------------------+
- Display the execution plan in
TRADITIONALformat.
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH JOIN | |1 |9 | |
| |1 |├─TABLE FULL SCAN|t2 |1 |4 | |
| |2 |└─TABLE FULL SCAN|t1 |2 |4 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 |
| equal_conds([t1.c2 = t2.c2]), other_conds(nil) |
| 1 - output([t2.c2], [t2.c1]), filter([t2.c1 > 4]), rowset=16 |
| access([t2.c2], [t2.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([t1.c2], [t1.c1]), filter(nil), rowset=16 |
| access([t1.c2], [t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
- Display the execution plan in
JSONformat.
EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4;
+----------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------+
| { |
| "ID":0, |
| "OPERATOR":"HASH JOIN ", |
| "NAME":"", |
| "EST.ROWS":1, |
| "EST.TIME(us)":9, |
| "output":"output([t1.c1], [t1.c2], [t2.c1], [t2.c2])", |
| "CHILD_1": { |
| "ID":1, |
| "OPERATOR":"TABLE FULL SCAN", |
| "NAME":"t2", |
| "EST.ROWS":1, |
| "EST.TIME(us)":4, |
| "output":"output([t2.c2], [t2.c1])" |
| }, |
| "CHILD_2": { |
| "ID":2, |
| "OPERATOR":"TABLE FULL SCAN", |
| "NAME":"t1", |
| "EST.ROWS":2, |
| "EST.TIME(us)":4, |
| "output":"output([t1.c2], [t1.c1])" |
| } |
| } |
+----------------------------------------------------------+