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 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
| UPDATE statement
Parameters
| Parameter | Description |
|---|---|
| INTO table_name | Specifies the table to which the EXPLAIN plan information will be saved. If not specified, the default is the PLAN_TABLE. |
| SET statement_id | Specifies a string identifier for the current query to facilitate subsequent queries for the SQL plan information. If not specified, the default is an empty string as the information identifier. |
| PRETTY | PRETTY_COLOR | Connects parent and child nodes in the plan tree with tree lines or colored tree lines, making the execution plan easier to read. |
| BASIC | Specifies the output of basic plan information, such as operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies that the output plan information includes OUTLINE information. |
| EXTENDED | EXPLAIN generates additional information, including: input and output columns for each operator, partition information for accessing tables, current Filter information, and if the current operator uses an index, displays the used index columns and extracted Query Range. |
| EXTENDED_NOADDR | Displays additional information in a simplified manner. |
| PARTITIONS | Displays partition-related information. |
| FORMAT = {TRADITIONAL| JSON} | Specifies the output format of EXPALIN:
|
| dml_statement | DML statement. |
Examples
Explain the query plan and save the plan information in the specified table
test./* Create table test */ 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)); obclient> CREATE TABLE t1(c1 INT); /* 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 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 setExplain 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 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 setOmit
explain_typeto return the execution plan of the statement.obclient> 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.obclient> 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.obclient> 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.obclient> 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])" | | } | | } | +----------------------------------------------------------+Explain the execution plan of the
UPDATEstatement.obclient> CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50), salary INT); obclient> INSERT INTO employees VALUES (1, 'Alice', 5000), (2, 'Bob', 6000); obclient> EXPLAIN UPDATE employees SET salary = salary * 1.1 WHERE emp_id > 1;The execution result is as follows:
+--------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------+ | ======================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------- | | |0 |UPDATE | |1 |34 | | | |1 |└─TABLE RANGE SCAN|employees|1 |3 | | | ======================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | table_columns([{employees: ({employees: (employees.emp_id, employees.emp_name, employees.salary)})}]), | | update([employees.salary=column_conv(INT,PS:(11,0),NULL,cast(employees.salary * 1.1, INT(-1, 0)))]) | | 1 - output([employees.emp_id], [employees.salary], [employees.emp_name]), filter(nil), rowset=16 | | access([employees.emp_id], [employees.salary], [employees.emp_name]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([employees.emp_id]), range(1 ; MAX), | | range_cond([employees.emp_id > 1]) | +--------------------------------------------------------------------------------------------------------------+Explain the execution plan of the
INSERTstatement.obclient> EXPLAIN INSERT INTO employees VALUES (3, 'Charlie', 7000);The execution result is as follows:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | -------------------------------------------------- | | |0 |DISTRIBUTED INSERT| |1 |13 | | | |1 |└─EXPRESSION | |1 |1 | | | ================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | columns([{employees: ({employees: (employees.emp_id, employees.emp_name, employees.salary)})}]), | | column_values([column_conv(INT,PS:(11,0),NOT NULL,__values.emp_id)], [column_conv(VARCHAR,utf8mb4_general_ci,length:50,NULL,__values.emp_name)], [column_conv(INT, | | PS:(11,0),NULL,__values.salary)]) | | 1 - output([__values.emp_id], [__values.emp_name], [__values.salary]), filter(nil) | | values({3, 'Charlie', 7000}) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+