Understand an execution plan|V4.0.0|OceanBase Database| docs|Distributed Database

The display modes of execution plans vary with the database system. Execution plans in OceanBase Database are displayed in the form of trees. The key to understanding an execution plan is to understand its operators. This topic describes details about common execution plans that contain operators for table access by normal index primary key (TABLE SCAN operator), table access by global index primary key (TABLE LOOKUP operator), or join algorithms (JOIN operator).

Table access by normal index primary key

In OceanBase Database, the table access logic for normal indexes is encapsulated in the TABLE SCAN operator. For global indexes, however, the logic of table access by index primary key is completed by the TABLE LOOKUP operator.

Examples

The following sample code shows an execution plan that contains the TABLE SCAN operator:

Create the ts_ny table.

obclient> CREATE TABLE ts_ny(c1 NUMBER PRIMARY KEY,c2 NUMBER,c3 NUMBER,c4 NUMBER,INDEX K5(c2));
Query OK, 0 rows affected

View the detailed execution plan.

obclient> EXPLAIN EXTENDED SELECT * FROM ts_ny WHERE c1 = 1\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------
|0 |TABLE GET|TS_NY|1        |46  |
===================================

Outputs & filters:
-------------------------------------
  0 - output([TS_NY.C1(0x7f9d9ffe0020)], [TS_NY.C2(0x7f9d9ffe03d0)], [TS_NY.C3(0x7f9d9ffe06c0)], [TS_NY.C4(0x7f9d9ffe09b0)]), filter(nil),
      access([TS_NY.C1(0x7f9d9ffe0020)], [TS_NY.C2(0x7f9d9ffe03d0)], [TS_NY.C3(0x7f9d9ffe06c0)], [TS_NY.C4(0x7f9d9ffe09b0)]), partitions(p0),
      is_index_back=false,
      range_key([TS_NY.C1(0x7f9d9ffe0020)]), range[1 ; 1],
      range_cond([TS_NY.C1(0x7f9d9ffe0020) = 1(0x7f9d9ffdf900)])

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "SYS.TS_NY"@"SEL$1")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

TS_NY:table_rows:100000, 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 set

Parameters

In the preceding sample code, the outputs & filters section in the execution plan shows in detail the output information of the TABLE SCAN operator. The following table describes the parameters in the output information.

Information	Description
operator	`TABLE SCAN` and `TABLE GET` are the two forms of the `TABLE SCAN` `operator`. `TABLE SCAN` performs a range scan and returns zero or multiple rows of data. `TABLE GET` locates a row by using a primary key and returns zero or one row of data.
name	The index selected for accessing data. The name of the selected index follows the table name. The absence of the index name means that the primary table is scanned. In OceanBase Database, the primary table has the same structure as the index, and the primary table is an index.
output	The output columns of the operator.
filter	The filter predicates of the operator.
partitions	The partitions to be scanned in the query.
is_index_back	Indicates whether table access is required by the operator.
filter_before_indexback	Corresponds to each `filter` and indicates whether the `filter` directly applies to the index or after the TABLE ACCESS BY INDEX PRIMARY KEY operation.
range_key/range/range_cond	`range_key`: the `rowkey` columns of the index. `range`: indicates the start and end positions in an index scan. You can verify whether it is a full table scan based on the value of `range`. For example, in a scenario with three `rowkey` columns, `range(MIN, MIN, MIN; MAX, MAX, MAX)` represents a full table scan. `range_cond`: predicates that determine the start and end positions of an index scan.

Table access by global index primary key

The TABLE LOOKUP operator represents the logic of table access by global index primary key.

The following sample code describes an execution plan that contains the TABLE LOOKUP operator.

Examples

The following sample code describes an execution plan that contains the TABLE LOOKUP operator.

Create the tl_ny table.

obclient>  CREATE TABLE tl_ny(c1 NUMBER PRIMARY KEY, c2 NUMBER, c3 NUMBER) PARTITION BY
           HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected

Create the global index tin.

obclient> CREATE INDEX tin ON tl_ny(c2) GLOBAL;
Query OK, 0 rows affected

View the execution plan.

obclient> EXPLAIN SELECT * FROM tl_ny WHERE c2 = 1\G
*************************** 1. row ***************************
Query Plan: =======================================================
|ID|OPERATOR               |NAME      |EST. ROWS|COST |
-------------------------------------------------------
|0 |TABLE LOOKUP           |TL_NY     |3960     |38876|
|1 | DISTRIBUTED TABLE SCAN|TL_NY(TIN)|3960     |1843 |
=======================================================

Outputs & filters:
-------------------------------------
  0 - output([TL_NY.C1], [TL_NY.C2], [TL_NY.C3]), filter(nil),
      partitions(p[0-3])
  1 - output([TL_NY.C1]), filter(nil),
      access([TL_NY.C1]), partitions(p0)

1 row in set

Parameters

In the preceding sample code, operator 1 scans the global index tin, and operator 0 specifies to retrieve columns that are not in the global index from the primary table. The outputs & filters section in the execution plan shows in detail the output information of the TABLE LOOKUP operator. The following table describes the parameters in the output information.

Information	Description
output	The output columns of the operator.
filter	The filter predicates of the operator. In this example, `filter` is set to `nil` because no filter condition is configured for the `TABLE LOOKUP` operator.
partitions	The partitions to be scanned in the query.

Join order

JOIN operators join data in two tables based on specified conditions. ``Join operations are classified into three types: inner join, outer join, and semi/anti join.

OceanBase Database supports the following JOIN operators: NESTED LOOP JOIN (NLJ), MERGE JOIN (MJ), and HASH JOIN (HJ).

NLJ

In the following example, examples 1 and 2 use NLJ based on the hint. Operator 0 is an NLJ operator and has two subnodes: operators 1 and 2. Its execution logic is shown in the following list:

Run operator 1 to read a row.
Run operator 2 to read all rows.
Join the result sets of operators 1 and 2 and apply the filter condition to export the results.
Repeat step 1 until operator 1 stops iteration.

Example 1

Create the tnlj1 table.

obclient> CREATE TABLE tnlj1 (c1 NUMBER, c2 NUMBER);
Query OK, 0 rows affected

Create the tnlj2 table.

obclient>  CREATE TABLE tnlj2 (d1 NUMBER, d2 NUMBER, PRIMARY KEY (d1));
Query OK, 0 rows affected

View the execution plan.

obclient> EXPLAIN SELECT /*+USE_NL(tnlj1, tnlj2)*/ tnlj1.c2 + tnlj2.d2
          FROM tnlj1, tnlj2 WHERE c2 = d2\G
*************************** 1. row ***************************
Query Plan: ================================================
|ID|OPERATOR        |NAME |EST. ROWS|COST      |
------------------------------------------------
|0 |NESTED-LOOP JOIN|     |98010000 |1738383995|
|1 | TABLE SCAN     |TNLJ1|100000   |38681     |
|2 | MATERIAL       |     |100000   |39721     |
|3 |  TABLE SCAN    |TNLJ2|100000   |38681     |
================================================

Outputs & filters:
-------------------------------------
  0 - output([TNLJ1.C2 + TNLJ2.D2]), filter(nil),
      conds([TNLJ1.C2 = TNLJ2.D2]), nl_params_(nil)
  1 - output([TNLJ1.C2]), filter(nil),
      access([TNLJ1.C2]), partitions(p0)
  2 - output([TNLJ2.D2]), filter(nil)
  3 - output([TNLJ2.D2]), filter(nil),
      access([TNLJ2.D2]), partitions(p0)

1 row in set

Note

The MATERIAL operator materializes the data output of subsequent operators.

Example 2

obclient> EXPLAIN SELECT /*+USE_NL(tnlj1, tnlj2)*/ tnlj1.c2 + tnlj2.d2 FROM tnlj1, tnlj2
          WHERE c1 = d1\G
*************************** 1. row ***************************
Query Plan: =============================================
|ID|OPERATOR        |NAME |EST. ROWS|COST   |
---------------------------------------------
|0 |NESTED-LOOP JOIN|     |99000    |4117615|
|1 | TABLE SCAN     |TNLJ1|100000   |38681  |
|2 | TABLE GET      |TNLJ2|1        |40     |
=============================================

Outputs & filters:
-------------------------------------
  0 - output([TNLJ1.C2 + TNLJ2.D2]), filter(nil),
      conds(nil), nl_params_([TNLJ1.C1])
  1 - output([TNLJ1.C1], [TNLJ1.C2]), filter(nil),
      access([TNLJ1.C1], [TNLJ1.C2]), partitions(p0)
  2 - output([TNLJ2.D2]), filter(nil),
      access([TNLJ2.D2]), partitions(p0)

1 row in set

In the preceding sample code, the outputs & filters section in the execution plan shows in detail the output information of the NESTED LOOP JOIN operator. The following table describes the parameters in the output information.

Information	Description
output	The output expression of the operator.
filter	The filter conditions of the operator. In this example, `filter` is set to `nil` because no filter condition is configured for the NLJ operator.
conds	The join conditions. For example, the join condition in example 1 is `t1.c2 = t2.d2`.
nl_params_	The pushdown parameters generated based on the data of the table on the left of the NLJ operator. For example, the pushdown parameter in example 2 is `t1.c1`. For the iteration of each row of the table on the left, NLJ creates a parameter based on `nl_params` and, based on this parameter and the original join condition `c1= d1`, creates a filter condition applicable to the table on the right: `d1 = ?`. The filter condition is pushed down to the table on the right, extracting the query range of the index. The query range is the range of data to be scanned. In query Q2, the No. 2 operator is a `TABLE GET` operator because of the pushdown condition `d1=?`.

Example 3

In the following sample code, no join condition is specified in the query, and operator 0 is NESTED-LOOP JOIN CARTESIAN, which is logically an NLJ operator that contains no join conditions.

obclient> EXPLAIN SELECT tnlj1.c2 + tnlj2.d2 FROM tnlj1,tnlj2\G
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR                  |NAME |EST. ROWS  |COST      |
------------------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN|     |10000000000|3658304897|
|1 | TABLE SCAN               |TNLJ1|100000     |38681     |
|2 | MATERIAL                 |     |100000     |39721     |
|3 |  TABLE SCAN              |TNLJ2|100000     |38681     |
============================================================

Outputs & filters:
-------------------------------------
  0 - output([TNLJ1.C2 + TNLJ2.D2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([TNLJ1.C2]), filter(nil),
      access([TNLJ1.C2]), partitions(p0)
  2 - output([TNLJ2.D2]), filter(nil)
  3 - output([TNLJ2.D2]), filter(nil),
      access([TNLJ2.D2]), partitions(p0)

1 row in set

MERGE JOIN (MJ)

In the following sample code, the query uses MJ based on the USE_MERGE hint. Operator 0 is an MJ operator and has two subnodes: operators 1 and 3. The operator merges data of the left and right subnodes, and therefore requires that the data of the two subnodes be ordered in relation to the JOIN column.

In example 4, the join condition is tnlj1.c2 = tnlj2.d2, which indicates to sort data of the tnlj1 table by column c2 and data of the tnlj2 table by column d2. In example 4, the output of operator 2 is unordered, and the output of operator 4 is sorted by d2. Both of them do not meet the requirement of MERGE JOIN. Therefore, operators 1 and 3 are assigned for sorting.

obclient> EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ tnlj1.c2 + tnlj2.d2 FROM tnlj1,tnlj2
          WHERE c2 = d2 AND c1 + d1 > 10\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR   |NAME |EST. ROWS|COST    |
-----------------------------------------
|0 |HASH JOIN  |     |32670000 |11332939|
|1 | TABLE SCAN|TNLJ2|100000   |38681   |
|2 | TABLE SCAN|TNLJ1|100000   |38681   |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([TNLJ1.C2 + TNLJ2.D2]), filter(nil),
      equal_conds([TNLJ1.C2 = TNLJ2.D2]), other_conds([TNLJ1.C1 + TNLJ2.D1 > 10])
  1 - output([TNLJ2.D2], [TNLJ2.D1]), filter(nil),
      access([TNLJ2.D2], [TNLJ2.D1]), partitions(p0)
  2 - output([TNLJ1.C2], [TNLJ1.C1]), filter(nil),
      access([TNLJ1.C2], [TNLJ1.C1]), partitions(p0)

1 row in set

Example 5

In the following sample code, the join condition in the query is tnlj1.c1 = tnlj2.d1, which indicates to sort data of the tnlj1 table by column c1 and sort data of the tnlj2 table by column d1. In this execution plan, the primary table scan is performed for t2, and the results are sorted by d1. Therefore, it is unnecessary to assign an additional SORT operator. Ideally, proper indexes are selected for tables on the left and right of the join, and the data order specified by the indexes can meet the requirements of MJ. In this case, no SORT operator is needed.

obclient> EXPLAIN SELECT /*+USE_MERGE(tnlj1, tnlj2)*/ tnlj1.c2 + tnlj2.d2 FROM tnlj1, tnlj2
          WHERE c1 = d1\G
*************************** 1. row ***************************
Query Plan: ========================================
|ID|OPERATOR    |NAME |EST. ROWS|COST  |
----------------------------------------
|0 |MERGE JOIN  |     |99000    |397523|
|1 | SORT       |     |100000   |330349|
|2 |  TABLE SCAN|TNLJ1|100000   |38681 |
|3 | TABLE SCAN |TNLJ2|100000   |38681 |
========================================

Outputs & filters:
-------------------------------------
  0 - output([TNLJ1.C2 + TNLJ2.D2]), filter(nil),
      equal_conds([TNLJ1.C1 = TNLJ2.D1]), other_conds(nil)
  1 - output([TNLJ1.C2], [TNLJ1.C1]), filter(nil), sort_keys([TNLJ1.C1, ASC])
  2 - output([TNLJ1.C1], [TNLJ1.C2]), filter(nil),
      access([TNLJ1.C1], [TNLJ1.C2]), partitions(p0)
  3 - output([TNLJ2.D1], [TNLJ2.D2]), filter(nil),
      access([TNLJ2.D1], [TNLJ2.D2]), partitions(p0)

1 row in set

In the preceding sample code, the outputs & filters section in the execution plan shows in detail the output information of the MERGE JOIN operator. The following table describes the parameters in the output information.

Information	Description
output	The output expression of the operator.
filter	The filter conditions of the operator. In this example, `filter` is set to `nil` because no filter condition is configured for the MJ operator.
equal_conds	Equivalent join conditions for MJ. The result sets of the subnodes on the left and right must be ordered in relation to the JOIN column.
other_conds	Other JOIN conditions. For example, `tnlj.c1 + tnlj2.d1 > 10` in example 4.

HASH JOIN (HJ)

In the following sample code, the query uses HJ based on the USE_HASH hint. Operator 0 is an HJ operator and has two subnodes: operators 1 and 2. Execution logic of this operator:

Read data from the subnode on the left to generate a hash value based on the JOIN column, such astnlj1.c1, and then create a hash table.
Read data from the subnode on the right to generate a hash value based on the JOIN column, such astnlj2.d1, and then try to join the read data with the data in the corresponding hash table tnlj1.

obclient> EXPLAIN SELECT /*+USE_HASH(tnlj1, tnlj2)*/ tnlj1.c2 + tnlj2.d2 FROM tnlj1, tnlj2
          WHERE c1 = d1 AND c2 + d2 > 1\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME |EST. ROWS|COST  |
---------------------------------------
|0 |HASH JOIN  |     |33000    |179344|
|1 | TABLE SCAN|TNLJ2|100000   |38681 |
|2 | TABLE SCAN|TNLJ1|100000   |38681 |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([TNLJ1.C2 + TNLJ2.D2]), filter(nil),
      equal_conds([TNLJ1.C1 = TNLJ2.D1]), other_conds([TNLJ1.C2 + TNLJ2.D2 > 1])
  1 - output([TNLJ2.D1], [TNLJ2.D2]), filter(nil),
      access([TNLJ2.D1], [TNLJ2.D2]), partitions(p0)
  2 - output([TNLJ1.C1], [TNLJ1.C2]), filter(nil),
      access([TNLJ1.C1], [TNLJ1.C2]), partitions(p0)

1 row in set

In the preceding sample code, the outputs & filters section in the execution plan shows in detail the output information of the HASH JOIN operator. The following table describes the parameters in the output information.

Information	Description
output	The output expression of the operator.
filter	The filter conditions of the operator. In this example, `filter` is set to `nil` because no filter condition is configured for the HJ operator.
equal_conds	The equivalent join. The JOIN columns on the left and right sides are used to calculate the hash value.
other_conds	Other JOIN conditions. For example, in example 6, the query has an additional join condition: `tnlj1.c2 + tnlj2.d2 > 1`.

For more information about execution plans, see the Optimize SQL statements topic in the Performance Tuning chapter of OceanBase Database User Guide.