Purpose
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.
EXPLAIN, DESCRIBE, and DESC are synonyms.
Syntax
{EXPLAIN DESCRIBE DESC} [explain_type] 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 |
|---|---|
| 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:
|
Examples
Query the statement execution plan without specifying
explain_type.obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G *************************** 1. row *************************** Query Plan: ======================================= IDOPERATOR NAMEEST. ROWSCOST --------------------------------------- 0 HASH JOIN 9801000 5933109 1 TABLE SCANt2 10000 6219 2 TABLE SCANt1 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: ======================================= IDOPERATOR NAMEEST. ROWSCOST --------------------------------------- 0 HASH JOIN 9801000 5933109 1 TABLE SCANt2 10000 6219 2 TABLE SCANt1 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 trueShow an 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: ======================================= IDOPERATOR NAMEEST. ROWSCOST --------------------------------------- 0 HASH JOIN 9801000 5933109 1 TABLE SCANt2 10000 6219 2 TABLE SCANt1 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)Show an 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" ] } }