Description
You can use this statement to interpret the execution plan for an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.
Syntax
Obtain table or column information:
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]
Obtain SQL plan information:
{EXPLAIN | DESCRIBE | DESC}
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}]
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement}
Parameters
| 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 name of the referenced table. |
| OUTLINE | Specifies the outline information contained in the output plan. |
| EXTENDED | Specifies the extra information generated by EXPLAIN, including the input and output columns of each operator, partition information about the access table, and the active filter. If the current operator uses an index, the used indexed columns and extracted query range are displayed. |
| EXTENDED_NOADDR | Displays the brief extra information. |
| PARTITIONS | Displays the partition-related information. |
| FORMAT = {TRADITIONAL| JSON} | Specifies the output format of EXPLAIN: * TRADITIONAL: presents the output in tabular format. * JSON: presents the output in KEY:VALUE format. The output is displayed as JSON strings, including 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 the EXPLAIN statement provides information about one table. Each row contains the following columns:
| Column | Description |
|---|---|
| ID | The plan execution serial number. |
| OPERATOR | An operator used in the plan. |
| NAME | The table referenced by an operator. |
| EST.ROWS | The estimated number of rows of the output produced by the current operator. |
| COST | The CPU time consumed to execute the current operator. |