An execution plan describes the execution procedure of an SQL statement in OceanBase Database. Execution plans are used to analyze the execution performance of SQL statements. Understanding execution plans is the precondition of SQL optimization. You can execute the EXPLAIN statement to view the logical execution plan generated by the optimizer for a given SQL statement.
For more information about SQL execution plans, see SQL execution plans.
For more information about distributed execution plans, see Manage distributed execution plans.
Syntax of the EXPLAIN statement
OceanBase Database supports three EXPLAIN statement formats: EXPLAIN BASIC, EXPLAIN, and EXPLAIN EXTENDED. They demonstrate details of execution plans at different levels:
The
EXPLAIN BASICstatement shows the most basic framework of a plan.The
EXPLAIN EXTENDEDstatement extends a plan to its full frame with the most details and is usually used in troubleshooting.The
EXPLAINstatement shows information to the extent that helps users understand the entire execution process of a plan.
Syntax:
EXPLAIN [explain_type] dml_statement;
explain_type:
BASIC [pretty_name]
| OUTLINE [pretty_name]
| EXTENDED [pretty_name]
| EXTENDED_NOADDR [pretty_name]
| PARTITIONS [pretty_name]
| FORMAT = { TRADITIONAL | JSON }
pretty_name:
PRETTY
| PRETTY_COLOR
dml_statement:
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
The
EXPLAINstatement applies to theSELECT,DELETE,INSERT,REPLACE, andUPDATEstatements. This statement shows information about statement execution plans provided by the optimizer, including the method of processing the statement and the method and sequence of joining tables.You can execute the
EXPLAIN EXTENDEDstatement to display the scan range of the table and execute theEXPLAIN OUTLINEstatement to display the outline information.The
FORMAToption is used to specify the output format. TheTRADITIONALvalue is the default value and specifies to display information in a table. TheJSONvalue specifies to display information in aJSONfile.You can use the
EXPLAIN PARTITITIONSstatement to check queries related to partitioned tables. If you check queries for non-partitioned tables, no error is returned, but the values in thePARTIONScolumn are alwaysNULL.For complex execution plans, you can use the
PRETTYorPRETTY_COLORoption to connect the parent and child nodes in the plan tree with tree lines or colored tree lines, so that the execution plan is easier to read.
Examples
Create a sample table.
obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, KEY k1(b));
Query OK, 0 rows affected
obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT, KEY k1(b));
Query OK, 0 rows affected
Use the EXPLAIN BASIC statement to display an execution plan
obclient> EXPLAIN BASIC SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
*************************** 1. row ***************************
Query Plan:
=====================
|ID|OPERATOR |NAME|
---------------------
|0 |MERGE JOIN | |
|1 | TABLE SCAN|t1 |
|2 | TABLE SCAN|t2 |
=====================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
equal_conds([t1.a = t2.a]), other_conds(nil)
1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
access([t1.a], [t1.b], [t1.c]), partitions(p0)
2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
access([t2.a], [t2.b], [t2.c]), partitions(p0)
Use the EXPLAIN statement to display an execution plan
obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN | |100001 |119783|
|1 | TABLE SCAN|t1 |100000 |38681 |
|2 | TABLE SCAN|t2 |100000 |38681 |
======================================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
equal_conds([t1.a = t2.a]), other_conds(nil)
1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
access([t1.a], [t1.b], [t1.c]), partitions(p0)
2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
access([t2.a], [t2.b], [t2.c]), partitions(p0)
Use the EXPLAIN EXTENDED statement to display an execution plan
obclient> EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN | |100001 |119783|
|1 | TABLE SCAN|t1 |100000 |38681 |
|2 | TABLE SCAN|t2 |100000 |38681 |
======================================
Outputs & filters:
-------------------------------------
0 - output([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)], [t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), filter(nil),
equal_conds([t1.a(0x7f1c68e79310) = t2.a(0x7f1c68e795d0)(0x7f1c68e78c20)]), other_conds(nil),
merge_directions([ASC])
1 - output([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)]), filter(nil),
access([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)]), partitions(p0),
is_index_back=false,
range_key([t1.a(0x7f1c68e79310)]), range(MIN ; MAX)always true
2 - output([t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), filter(nil),
access([t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), partitions(p0),
is_index_back=false,
range_key([t2.a(0x7f1c68e795d0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
USE_MERGE(@"SEL$1" ("test.t2"@"SEL$1" ))
PQ_DISTRIBUTE(@"SEL$1" ("test.t2"@"SEL$1" ) LOCAL LOCAL)
FULL(@"SEL$1" "test.t1"@"SEL$1")
FULL(@"SEL$1" "test.t2"@"SEL$1")
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
t1:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[t1], pruned_index_name[k1]
t2:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[t2], pruned_index_name[k1]
Parameters
-------------------------------------
Use the EXPLAIN EXTENDED_NOADDR statement to display an execution plan
obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN | |100001 |119783|
|1 | TABLE SCAN|t1 |100000 |38681 |
|2 | TABLE SCAN|t2 |100000 |38681 |
======================================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
equal_conds([t1.a = t2.a]), other_conds(nil)
1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
access([t1.a], [t1.b], [t1.c]), partitions(p0),
is_index_back=false,
range_key([t1.a]), range(MIN ; MAX)always true
2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
access([t2.a], [t2.b], [t2.c]), partitions(p0),
is_index_back=false,
range_key([t2.a]), range(MIN ; MAX)always true
Use the EXPLAIN OUTLINE statement to display the outline information
obclient> EXPLAIN OUTLINE SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
*************************** 1. row ***************************
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN | |100001 |119783|
|1 | TABLE SCAN|t1 |100000 |38681 |
|2 | TABLE SCAN|t2 |100000 |38681 |
======================================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
equal_conds([t1.a = t2.a]), other_conds(nil)
1 - output([t1.a], [t1.b], [t1.c]), filter(nil),
access([t1.a], [t1.b], [t1.c]), partitions(p0)
2 - output([t2.a], [t2.b], [t2.c]), filter(nil),
access([t2.a], [t2.b], [t2.c]), partitions(p0)
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
USE_MERGE(@"SEL$1" ("test.t2"@"SEL$1" ))
PQ_DISTRIBUTE(@"SEL$1" ("test.t2"@"SEL$1" ) LOCAL LOCAL)
FULL(@"SEL$1" "test.t1"@"SEL$1")
FULL(@"SEL$1" "test.t2"@"SEL$1")
END_OUTLINE_DATA
*/