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
Parameters
| Parameter | Description |
|---|---|
| INTO table_name | Specifies the table to save the execution plan information of EXPLAIN. If not specified, the default is to query the PLAN_TABLE table. |
| SET statement_id | Specifies a string tag for the current query to facilitate subsequent queries for the execution plan information of this SQL statement. If not specified, the default is to use an empty string as the information tag. |
| PRETTY | PRETTY_COLOR | Connects the parent and child nodes of the plan tree with tree lines or colored tree lines, making it easier to read the execution plan. |
| BASIC | Specifies the basic information of the plan to be output, 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: the input and output columns of each operator, the partition information of the accessed table, the current Filter information, and if the current operator uses an index, the index columns and extracted Query Range. |
| 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. |
Examples
Explain the query plan and save the plan information to 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 /* Explain the query plan and save the plan information to 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_typeand return the execution plan of the statement.obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST | --------------------------------------- |0 |HASH JOIN | |9801000 |5933109| |1 | TABLE SCAN|t2 |10000 |6219 | |2 | TABLE SCAN|t1 |100000 |68478 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c2 = t2.c2]), other_conds(nil) 1 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0) 2 - output([t1.c2], [t1.c1]), filter(nil), access([t1.c2], [t1.c1]), partitions(p0)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\G *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST | --------------------------------------- |0 |HASH JOIN | |9801000 |5933109| |1 | TABLE SCAN|t2 |10000 |6219 | |2 | TABLE SCAN|t1 |100000 |68478 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c2 = t2.c2]), other_conds(nil) 1 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0), is_index_back=false, range_key([t2.c1]), range(4 ; MAX), range_cond([t2.c1 > 4]) 2 - output([t1.c2], [t1.c1]), filter(nil), access([t1.c2], [t1.c1]), partitions(p0), is_index_back=false, range_key([t1.__pk_increment], [t1.__pk_cluster_id], [t1.__pk_partition_id]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always trueDisplay the execution plan in
TRADITIONALformat.obclient> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST | --------------------------------------- |0 |HASH JOIN | |9801000 |5933109| |1 | TABLE SCAN|t2 |10000 |6219 | |2 | TABLE SCAN|t1 |100000 |68478 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c2 = t2.c2]), other_conds(nil) 1 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0) 2 - output([t1.c2], [t1.c1]), filter(nil), access([t1.c2], [t1.c1]), partitions(p0)Display the execution plan in
JSONformat.obclient> EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G *************************** 1. row *************************** Query Plan: { "ID":2, "OPERATOR":"JOIN", "NAME":"JOIN", "EST.ROWS":9800999, "COST":5933108, "output": [ "t1.c1", "t1.c2", "t2.c1", "t2.c2" ], "TABLE SCAN": { "ID":0, "OPERATOR":"TABLE SCAN", "NAME":"TABLE SCAN", "EST.ROWS":10000, "COST":6218, "output": [ "t2.c2", "t2.c1" ] }, "TABLE SCAN": { "ID":1, "OPERATOR":"TABLE SCAN", "NAME":"TABLE SCAN", "EST.ROWS":100000, "COST":68477, "output": [ "t1.c2", "t1.c1" ] } }