Description
This statement explains the execution plan of an SQL statement, such as a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.
Syntax
Retrieve the information about a table or a column:
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]
Retrieve the information about an SQL plan:
{EXPLAIN}
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}]
{SELECT statement | DELETE statement | INSERT statement | UPDATE statement | MERGE statement}
Parameter description
| Parameter | Description |
|---|---|
| tbl_name | Specifies the table name. |
| col_name | Specifies the column name of the table. |
| BASIC | Specifies the basic information about the output plan, such as the operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies that the output plan information includes the outline information. |
| EXTENDED | Specifies that the EXPLAIN statement generates additional information. The additional information includes the input and output columns for each operator, the partition information about the accessed table, and the current used filter information. If the current operator uses an index, the used index column and the extracted query range appear. |
| EXTENDED_NOADDR | Displays the additional information in a simple way. |
| PARTITIONS | Displays the partition-related information. |
| FORMAT = {TRADITIONAL| JSON} | Specifies the output format of EXPLAIN: * TRADITIONAL: the table output format. * The KEY:VALUE output format. The output appears as JSON strings that contain EXTENDED and PARTITIONS information. |
Examples
- Omit explain_type
OceanBase(admin@test)>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)
- EXTENDED
OceanBase(admin@test)>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 true
- TRADITIONAL format
OceanBase(admin@test)>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)
- JSON format
OceanBase(admin@test)>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"
]
}
}
Each output row of EXPLAIN provides the information about a table. Each row contains the following columns:
| Column name | Description |
|---|---|
| ID | The execution serial number of the plan. |
| OPERATOR | The executed operator. |
| NAME | The table that is referenced by the operator. |
| EST.ROWS | The estimated number of rows that are returned by the current operator. |
| COST | The CPU time that is consumed to execute the current operator. |