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
{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
Parameters |
Description |
|---|---|
| INTO table_name | Indicates that the plan information of EXPLAIN is stored in the specified table. If INTO table_name is not specified, the result is queried from the PLAN_TABLE table by default. |
| SET statement_id | Indicates that the current query uses a string tag to facilitate subsequent querying of the SQL plan information. If not specified, SET statement_id By default, an empty string is used as the information marker. |
| PRETTY \ | PRETTY_COLOR | Parent nodes and child nodes in the plan tree are connected by tree lines or colored tree lines to make the execution plan easier to read. |
| BASIC | Specifies the basic information of the output plan, such as operator ID, operator name, and referenced table name. |
| OUTLINE | Specify the output plan information to include OUTLINE Information. |
| EXTENDED | EXPLAIN Additional information is generated, including: the input and output columns of each operator, partition information of the accessed table, currently used filter information. If an index is used by the current operator, the index columns and the extracted query range are displayed. |
| EXTENDED_NOADDR | Display additional information in a concise manner. |
| PARTITIONS | Displays partition information. |
| FORMAT = {TRADITIONAL\ | JSON} | Specifies the output format of EXPLAIN as follows:
|
| dml_statement | DML statements. |
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 setExplanes query plans and sets identifiers for queries.
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 | +------------------------------------------------------------------------------------+Outputs additional information using the
EXTENDED_NOADDRkeyword.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 | +------------------------------------------------------------------------------------+Displays execution plans 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 | +------------------------------------------------------------------------------------+Displays execution plans 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 an
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 an
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}) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+