The SQL engine of OceanBase Database supports execution plan parsing and caching. You can analyze the query performance of a query statement by analyzing its execution plan.
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 run the EXPLAIN command to view the logical execution plan generated by the optimizer for a given SQL statement.
Syntax of the EXPLAIN command
OceanBase Database supports three EXPLAIN command formats: EXPLAIN BASIC, EXPLAIN, and EXPLAIN EXTENDED. They demonstrate details of execution plans at different levels:
The
EXPLAIN BASICcommand shows the most basic framework of a plan.The
EXPLAIN EXTENDEDcommand extends a plan to its full frame with the most details and is usually used in troubleshooting.The
EXPLAINcommand 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
EXPLAINcommand applies to theSELECT,DELETE,INSERT,REPLACE, andUPDATEstatements. This command 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 run the
EXPLAIN EXTENDEDcommand to display the scan range of the table and run theEXPLAIN OUTLINEcommand 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.
View an execution plan in ODC
In the SQL window of ODC, execute the EXPLAIN statement and click 
on the Result 1 tab to view the information about the execution plan.
Run the
EXPLAIN BASICcommand to display an execution plan.
Run the
EXPLAINcommand to display an execution plan.
Run the
EXPLAIN EXTENDEDcommand to display an execution plan.
Run the
EXPLAIN EXTENDED_NOADDRcommand to display an execution plan.
Run the
EXPLAIN OUTLINEcommand to display the outline information.
View an execution plan in CLI
Run the
EXPLAIN BASICcommand to display an execution plan.obclient> CREATE TABLE t511(a INT PRIMARY KEY, b INT, c INT); Query OK, 0 rows affected obclient> CREATE TABLE t512(a INT PRIMARY KEY, b INT, c INT); Query OK, 0 rows affected obclient> EXPLAIN BASIC SELECT * FROM t511,t512 WHERE t511.a = t512.a\G *************************** 1. row *************************** Query Plan: ========================== |ID|OPERATOR |NAME| -------------------------- |0 |NESTED-LOOP JOIN| | |1 | TABLE SCAN |T511| |2 | TABLE GET |T512| ========================== Outputs & filters: ------------------------------------- 0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil), conds(nil), nl_params_([T511.A]) 1 - output([T511.A], [T511.B], [T511.C]), filter(nil), access([T511.A], [T511.B], [T511.C]), partitions(p0) 2 - output([T512.A], [T512.B], [T512.C]), filter(nil), access([T512.A], [T512.B], [T512.C]), partitions(p0) 1 row in setRun the
EXPLAINcommand to display an execution plan.obclient> EXPLAIN SELECT * FROM t511,t512 WHERE t511.a = t512.a\G *************************** 1. row *************************** Query Plan: ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |NESTED-LOOP JOIN| |1 |53 | |1 | TABLE SCAN |T511|1 |46 | |2 | TABLE GET |T512|1 |7 | ========================================= Outputs & filters: ------------------------------------- 0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil), conds(nil), nl_params_([T511.A]) 1 - output([T511.A], [T511.B], [T511.C]), filter(nil), access([T511.A], [T511.B], [T511.C]), partitions(p0) 2 - output([T512.A], [T512.B], [T512.C]), filter(nil), access([T512.A], [T512.B], [T512.C]), partitions(p0) 1 row in setRun the
EXPLAIN EXTENDEDcommand to display an execution plan.obclient> EXPLAIN EXTENDED SELECT * FROM t511,t512 WHERE t511.a = t512.a\G *************************** 1. row *************************** Query Plan: ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |NESTED-LOOP JOIN| |1 |53 | |1 | TABLE SCAN |T511|1 |46 | |2 | TABLE GET |T512|1 |7 | ========================================= Outputs & filters: ------------------------------------- 0 - output([T511.A(0x7f03f423b040)], [T511.B(0x7f03f423b840)], [T511.C(0x7f03f423bb00)], [T512.A(0x7f03f423b300)], [T512.B(0x7f03f423bdc0)], [T512.C(0x7f03f423c080)]), filter(nil), conds(nil), nl_params_([T511.A(0x7f03f423b040)]), batch_join=false 1 - output([T511.A(0x7f03f423b040)], [T511.B(0x7f03f423b840)], [T511.C(0x7f03f423bb00)]), filter(nil), access([T511.A(0x7f03f423b040)], [T511.B(0x7f03f423b840)], [T511.C(0x7f03f423bb00)]), partitions(p0), is_index_back=false, range_key([T511.A(0x7f03f423b040)]), range(MIN ; MAX)always true 2 - output([T512.A(0x7f03f423b300)], [T512.B(0x7f03f423bdc0)], [T512.C(0x7f03f423c080)]), filter(nil), access([T512.A(0x7f03f423b300)], [T512.B(0x7f03f423bdc0)], [T512.C(0x7f03f423c080)]), partitions(p0), is_index_back=false, range_key([T512.A(0x7f03f423b300)]), range(MIN ; MAX) range_cond([? = T512.A(0x7f03f423b300)(0x7f03f429aee0)]) Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("SYS.T511"@"SEL$1" "SYS.T512"@"SEL$1")) USE_NL(@"SEL$1" "SYS.T512"@"SEL$1") FULL(@"SEL$1" "SYS.T511"@"SEL$1") FULL(@"SEL$1" "SYS.T512"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- T511:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[T511] T512:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters ------------------------------------- 1 row in setRun the
EXPLAIN EXTENDED_NOADDRcommand to display an execution plan.obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t511,t512 WHERE t511.a = t512.a\G *************************** 1. row *************************** Query Plan: ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |NESTED-LOOP JOIN| |1 |53 | |1 | TABLE SCAN |T511|1 |46 | |2 | TABLE GET |T512|1 |7 | ========================================= Outputs & filters: ------------------------------------- 0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil), conds(nil), nl_params_([T511.A]), batch_join=false 1 - output([T511.A], [T511.B], [T511.C]), filter(nil), access([T511.A], [T511.B], [T511.C]), partitions(p0), is_index_back=false, range_key([T511.A]), range(MIN ; MAX)always true 2 - output([T512.A], [T512.B], [T512.C]), filter(nil), access([T512.A], [T512.B], [T512.C]), partitions(p0), is_index_back=false, range_key([T512.A]), range(MIN ; MAX), range_cond([? = T512.A]) 1 row in setRun the
EXPLAIN OUTLINEcommand to display the outline information.obclient> EXPLAIN OUTLINE SELECT * FROM t511,t512 WHERE t511.a = t512.a\G *************************** 1. row *************************** Query Plan: ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |NESTED-LOOP JOIN| |1 |53 | |1 | TABLE SCAN |T511|1 |46 | |2 | TABLE GET |T512|1 |7 | ========================================= Outputs & filters: ------------------------------------- 0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil), conds(nil), nl_params_([T511.A]) 1 - output([T511.A], [T511.B], [T511.C]), filter(nil), access([T511.A], [T511.B], [T511.C]), partitions(p0) 2 - output([T512.A], [T512.B], [T512.C]), filter(nil), access([T512.A], [T512.B], [T512.C]), partitions(p0) Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("SYS.T511"@"SEL$1" "SYS.T512"@"SEL$1")) USE_NL(@"SEL$1" "SYS.T512"@"SEL$1") FULL(@"SEL$1" "SYS.T511"@"SEL$1") FULL(@"SEL$1" "SYS.T512"@"SEL$1") END_OUTLINE_DATA */ 1 row in set