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, DESCRIBE, and DESC are synonyms.
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 table name. |
| column_name | Specifies the column name of the table. |
| INTO table_name | Specifies to save the plan information of EXPLAIN to the specified table. If you do not specify INTO table_name, the default is to query the PLAN_TABLE. |
| SET STATEMENT_ID = 'string' | Specifies a string to mark the current query for easy retrieval of the plan information of this SQL statement. If you do not specify SET statement_id, the default is to use an empty string as the information marker. |
| PRETTY | PRETTY_COLOR | Connects the parent nodes 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 the basic information of the plan, such as operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies to output the plan information, including outline information. |
| EXTENDED | EXPLAIN generates additional information, including input and output columns of each operator, partition information of the accessed table, and current filter information. If the current operator uses an index, the index columns and 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
Example 1: Create a test table
-- Create a test table named test.
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)
);
-- Create a test table named t1.
CREATE TABLE t1(c1 INT);
-- Set the timestamp format.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Example 2: Save the execution plan to a table
-- Save the execution plan to the test table.
EXPLAIN INTO test SELECT COUNT(*) FROM t1;
Execution result:
=================================================
|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
Example 3: Use the STATEMENT_ID option to identify the execution plan
-- Use the STATEMENT_ID option to identify the execution plan.
EXPLAIN INTO test SET STATEMENT_ID='test2' SELECT COUNT(*) FROM t1;
-- View the execution plan of the specified statement_id.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'test', statement_id=>'test2'));
Example 4: Create a test table and view the table structure
-- Create a test table.
CREATE TABLE t101(c1 INT, c2 INT);
CREATE TABLE t102(c1 INT, c2 INT);
-- View the table structure.
EXPLAIN t101;
Execution result:
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C1 | NUMBER(38) | YES | NULL | NULL | NULL |
| C2 | NUMBER(38) | YES | NULL | NULL | NULL |
+-------+------------+------+-----+---------+-------+
Example 5: View the column information of a table
-- View the c2 column information of the t102 table.
EXPLAIN t102 c2;
Execution result:
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C2 | NUMBER(38) | YES | NULL | NULL | NULL |
+-------+------------+------+-----+---------+-------+
Example 6: Omit the explain_type option
-- Omit the explain_type option to view the execution plan of the SELECT statement.
EXPLAIN SELECT * FROM t101, t102 WHERE t101.c2 = t102.c2;
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH JOIN | |1 |8 | |
| |1 |├─TABLE FULL SCAN|T102|1 |4 | |
| |2 |└─TABLE FULL SCAN|T101|1 |4 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T101.C1], [T101.C2], [T102.C1], [T102.C2]), filter(nil), rowset=16 |
| equal_conds([T101.C2 = T102.C2]), other_conds(nil) |
| 1 - output([T102.C2], [T102.C1]), filter([T102.C1 > 4]), rowset=16 |
| access([T102.C2], [T102.C1]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T102.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([T101.C2], [T101.C1]), filter(nil), rowset=16 |
| access([T101.C2], [T101.C1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T101.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
Use the
EXTENDED_NOADDRkeyword to output additional information.obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t101,t102 WHERE t101.c2=t102.c2 AND t102.c1 > 4; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH JOIN | |1 |8 | | | |1 |├─TABLE FULL SCAN|T102|1 |4 | | | |2 |└─TABLE FULL SCAN|T101|1 |4 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T101.C1], [T101.C2], [T102.C1], [T102.C2]), filter(nil), rowset=16 | | equal_conds([T101.C2 = T102.C2]), other_conds(nil) | | 1 - output([T102.C2], [T102.C1]), filter([T102.C1 > 4]), rowset=16 | | access([T102.C2], [T102.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([T102.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T101.C2], [T101.C1]), filter(nil), rowset=16 | | access([T101.C2], [T101.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([T101.__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" ("SYS"."T102"@"SEL$1" "SYS"."T101"@"SEL$1")) | | USE_HASH(@"SEL$1" "SYS"."T101"@"SEL$1") | | FULL(@"SEL$1" "SYS"."T102"@"SEL$1") | | FULL(@"SEL$1" "SYS"."T101"@"SEL$1") | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | T102: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:0 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[T102] | | stats version:0 | | dynamic sampling level:1 | | T101: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[T101] | | stats version:0 | | dynamic sampling level:1 | | Plan Type: | | LOCAL | | Note: | | Degree of Parallelisim is 1 because of table property | +------------------------------------------------------------------------------------+View the execution plan of the
INSERTstatement in theTRADITIONALformat.obclient> EXPLAIN FORMAT=TRADITIONAL INSERT INTO T101 VALUES(1,1); +----------------------------------------------------------------------------------------------------------------------------------------+ | 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([{T101: ({T101: (T101.__pk_increment, T101.C1, T101.C2)})}]), | | column_values([T_HIDDEN_PK], [column_conv(NUMBER,PS:(-1,0),NULL,__values.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,__values.C2)]) | | 1 - output([__values.C1], [__values.C2]), filter(nil) | | values({1, 1}) | +----------------------------------------------------------------------------------------------------------------------------------------+View the execution plan of the
SELECTstatement in theJSONformat.obclient> EXPLAIN FORMAT=JSON SELECT * FROM t101,t102 WHERE t101.c2=t102.c2 AND t102.c1 > 4; +------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------+ | { | | "ID":0, | | "OPERATOR":"HASH JOIN ", | | "NAME":"", | | "EST.ROWS":1, | | "EST.TIME(us)":8, | | "output":"output([T101.C1], [T101.C2], [T102.C1], [T102.C2])", | | "CHILD_1": { | | "ID":1, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"T102", | | "EST.ROWS":1, | | "EST.TIME(us)":4, | | "output":"output([T102.C2], [T102.C1])" | | }, | | "CHILD_2": { | | "ID":2, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"T101", | | "EST.ROWS":1, | | "EST.TIME(us)":4, | | "output":"output([T101.C2], [T101.C1])" | | } | | } | +------------------------------------------------------------------+