EXPLAIN
描述
该语句用于解释 SQL 语句的执行计划，可以是 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 语句。
EXPLAIN 与 DESCRIBE、DESC 互为同义词。
语法
{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
参数解释
| 参数 | 描述 |
|---|---|
| INTO table_name | 表示将 EXPLAIN 的计划信息保存到指定表内。如果没有指定 INTO table_name，默认查询到 PLAN_TABLE 表内。 |
| SET statement_id | 表示当前查询使用字符串标记，以方便后续查询该 SQL 的计划信息。如果没有指定 SET statement_id，默认使用空字符串作为信息标记。 |
| PRETTY | PRETTY_COLOR | 将计划树中的父节点和子节点使用树线或彩色树线连接起来，使得执行计划展示更方便阅读。 |
| BASIC | 指定输出计划的基础信息，如算子 ID、算子名称、所引用的表名。 |
| OUTLINE | 指定输出的计划信息包含 OUTLINE 信息。 |
| EXTENDED | EXPLAIN 产生附加信息，包括：每个算子的输入列和输出列，访问表的分区信息，当前使用的 Filter 信息，如果当前算子使用了索引，显示所使用的索引列及抽取的 Query Range。 |
| EXTENDED_NOADDR | 以简约的方式展示附加信息。 |
| PARTITIONS | 显示分区相关信息。 |
| FORMAT = {TRADITIONAL| JSON} | 指定 EXPALIN 的输出格式：
|
| dml_statement | DML 语句。 |
示例
-
解释查询计划，并把计划信息保存在指定表
test内。/* 创建表 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));
Query OK, 0 rows affected (0.065 sec)
CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected (0.013 sec)
/* 解释查询计划，并把计划信息保存在指定表 test 内 */
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 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) -
省略
explain_type，返回语句的执行计划。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 |
+------------------------------------------------------------------------------------+ -
通过
EXTENDED_NOADDR关键字输出附加信息。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 |
+------------------------------------------------------------------------------------+ -
展示
TRADITIONAL格式的执行计划。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 |
+------------------------------------------------------------------------------------+ -
展示
JSON格式的执行计划。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])" |
| } |
| } |
+----------------------------------------------------------+