SQL execution plan

An execution plan describes the process of executing an SQL statement in a database.

You can run the EXPLAIN command to view the logical execution plan generated by the optimizer for a given SQL statement. To analyze the performance of an SQL statement, you need to first check the SQL execution plan to see if any error exists. Therefore, understanding the execution plan is the first step for SQL optimization, and knowledge about operators of an execution plan is key to understanding the EXPLAIN command.

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 BASIC command shows the most basic framework of a plan.

• The EXPLAIN EXTENDED command extends a plan to its full frame with most details and is usually used in troubleshooting.

• The EXPLAIN command shows information to the extent that helps users understand the entire execution process of a plan.

Use the following command syntax:

EXPLAIN [BASIC | EXTENDED | PARTITIONS | FORMAT = format_name] explainable_stmt
format_name: { TRADITIONAL | JSON }
explainable_stmt: { SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement }

Shape and operators of an execution plan

The following example shows an execution plan in OceanBase Database:

|ID|OPERATOR              |NAME       |EST. ROWS|COST |
-------------------------------------------------------
|0 |LIMIT                 |           |100      |81141|
|1 | TOP-N SORT           |           |100      |81127|
|2 |  HASH GROUP BY       |           |2924     |68551|
|3 |   HASH JOIN          |           |2924     |65004|
|4 |    SUBPLAN SCAN      |VIEW1      |2953     |19070|
|5 |     HASH GROUP BY    |           |2953     |18662|
|6 |      NESTED-LOOP JOIN|           |2953     |15080|
|7 |       TABLE SCAN     |ITEM       |19       |11841|
|8 |       TABLE SCAN     |STORE_SALES|161      |73   |
|9 |    TABLE SCAN        |DT         |6088     |29401|
=======================================================

You may notice from the example that a plan in OceanBase Database is similar to that in Oracle Database. The following table describes columns of an execution plan in OceanBase Database.

Note

In a table operation, the NAME field displays names (alias) of tables involved in the operation. In the case of index access, the name of the index is displayed in parentheses after the table name. For example, t1(t1_c2) indicates that index t1_c2 is used. In the case of reverse scanning, the keyword RESERVE is added after the index name, with the index name and the keyword RESERVE separated with a comma (,). For example, t1(t1_c2,RESERVE).

In OceanBase Database, the first part of the output of the EXPLAIN command is the tree structure of the execution plan. The hierarchy of operations in the tree is represented by the indentation of the operators. Operators at the deepest level are executed first. Operators at the same level are executed in the specified execution order.

The following figure shows the tree structure of the execution plan described in the preceding example:

In OceanBase Database, the second part of the output of the EXPLAIN command contains the details of each operator, including the output expression, filter conditions, partition information, and unique information of each operator, such as the sort keys, join keys, and pushdown conditions, as shown in the following example:

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), prefix_pos(1)
  1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
      equal_conds([t1.c1 = t2.c2]), other_conds(nil)
  2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC])
  3 - output([t2.c2], [t2.c1]), filter(nil),
      access([t2.c2], [t2.c1]), partitions(p0)
  4 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0)