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 how to understand 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 logic of table access by normal index primary key is encapsulated in the TABLE SCAN operator, while the logic of table access by global index primary key is completed by the TABLE LOOKUP operator.
The following sample code shows an execution plan that contains the TABLE SCAN operator:
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT, INDEX k1(c2,c3));
Query OK, 0 rows affected
Q1:
obclient> EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1 = 1\G
*************************** 1. row ***************************
Query Plan:
| ==================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------
|0 |TABLE GET|t1 |1 |53 |
==================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1(0x7f22fbe69340)], [t1.c2(0x7f22fbe695c0)], [t1.c3(0x7f22fbe69840)], [t1.c4(0x7f22fbe69ac0)]), filter(nil),
access([t1.c1(0x7f22fbe69340)], [t1.c2(0x7f22fbe695c0)], [t1.c3(0x7f22fbe69840)], [t1.c4(0x7f22fbe69ac0)]), partitions(p0),
is_index_back=false,
range_key([t1.c1(0x7f22fbe69340)]), range[1 ; 1],
range_cond([t1.c1(0x7f22fbe69340) = 1(0x7f22fbe68cf0)])
Q2:
obclient> EXPLAIN EXTENDED SELECT * FROM t1 WHERE c2 < 1 AND c3 < 1 AND c4 < 1\G
*************************** 1. row ***************************
Query Plan:
| ======================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------
|0 |TABLE SCAN|t1(k1)|100 |12422|
======================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1(0x7f22fbd1e220)], [t1.c2(0x7f227decec40)], [t1.c3(0x7f227decf9b0)], [t1.c4(0x7f22fbd1dfa0)]), filter([t1.c3(0x7f227decf9b0) < 1(0x7f227decf360)], [t1.c4(0x7f22fbd1dfa0) < 1(0x7f22fbd1d950)]),
access([t1.c2(0x7f227decec40)], [t1.c3(0x7f227decf9b0)], [t1.c4(0x7f22fbd1dfa0)], [t1.c1(0x7f22fbd1e220)]), partitions(p0),
is_index_back=true, filter_before_indexback[true,false],
range_key([t1.c2(0x7f227decec40)], [t1.c3(0x7f227decf9b0)], [t1.c1(0x7f22fbd1e220)]),
range(NULL,MAX,MAX ; 1,MIN,MIN),
range_cond([t1.c2(0x7f227decec40) < 1(0x7f227dece5f0)])
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 lists the output information.
| Information | Description |
|---|---|
| operator | TABLE SCAN and TABLE GET are two forms of the TABLE SCAN operator.
|
| 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. In the sample code, filter in Q1 is set to nil because no filter condition is configured for the TABLE GET operator. |
| partitions | The partitions to be scanned in the query. |
| is_index_back | Indicates whether table access is required by the operator. In Q1, the primary table is selected. Therefore, table access is not required. In Q2, the indexed columns are c2, c3, and c1. The query needs to return column c4. Therefore, table access is required. |
| filter_before_indexback | Corresponds to each filter and indicates whether table access is required before the corresponding filter operation is performed based on the index. For example, in Q2, if the filter condition is c3 < 1, the filter operation can be performed based on the index without table access. This reduces the number of table access operations. If the filter condition is c4 < 1, the filter operation is performed only after c4 is obtained through table access. |
| range_key/range/range_cond |
|
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 the execution plan that contains the TABLE LOOKUP operator.
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT) PARTITION BY
HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> CREATE INDEX i1 ON t1(c2) GLOBAL;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t1 WHERE c2 = 1\G
*************************** 1. row ***************************
Query Plan:
| ========================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
----------------------------------------
|0 |TABLE LOOKUP|t1 |3960 |31065|
|1 | TABLE SCAN |t1(i1)|3960 |956 |
========================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil),
partitions(p[0-3])
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
In the preceding sample code, operator 1 executes the scanning of the global index i1, 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 lists the output information.
| Information | Description |
|---|---|
| output | The output columns of the operator. |
| filter | The filter predicates of the operator. In the sample code, 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 sequence
The JOIN operator joins data of two tables based on specified conditions. Join operations are classified into three types: inner join, outer join, and semi/anti join.
JOIN operators supported in OceanBase Database include NESTED LOOP JOIN (NLJ), MERGE JOIN (MJ), and HASH JOIN (HJ).
NLJ
In the following sample code, Q1 and Q2 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.
obclient> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2 (d1 INT, d2 INT, PRIMARY KEY (d1));
Query OK, 0 rows affected
Q1:
obclient> EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c2 = d2\G
*************************** 1. row ***************************
Query Plan:
===========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------------
|0 |NESTED-LOOP JOIN| |9782 |411238|
|1 | TABLE SCAN |T1 |999 |647 |
|2 | MATERIAL | |999 |1519 |
|3 | TABLE SCAN |T2 |999 |647 |
===========================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
conds([T1.C2 = T2.D2]), nl_params_(nil)
1 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
2 - output([T2.D2]), filter(nil)
3 - output([T2.D2]), filter(nil),
access([T2.D2]), partitions(p0)
The MATERIAL operator materializes the data output of subsequent operators.
Q2:
obclient> EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c1 = d1\G
*************************** 1. row ***************************
Query Plan:
| ==========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
------------------------------------------
|0 |NESTED-LOOP JOIN| |990 |37346|
|1 | TABLE SCAN |T1 |999 |669 |
|2 | TABLE GET |T2 |1 |36 |
==========================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
conds(nil), nl_params_([T1.C1])
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
2 - output([T2.D2]), filter(nil),
access([T2.D2]), partitions(p0)
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 lists the output information.
| Information | Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. In the sample code, 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 Q1 is t1.c2 = t2.d2. |
| nl_params_ | The pushdown parameters based on the data of the table on the left of the NLJ operator. For example, the pushdown parameter in Q2 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 Q2, operator 2 is TABLE GET because of the pushdown condition d1 = ?. |
In the following sample code, no join condition is specified in Q3, and operator 0 is NESTED-LOOP JOIN CARTESIAN, which is logically an NLJ operator that contains no join conditions.
Q3:
obclient> EXPLAIN SELECT t1.c2 + t2.d2 FROM t1, t2\G
*************************** 1. row ***************************
Query Plan:
| =====================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-----------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN| |998001 |747480|
|1 | TABLE SCAN |T1 |999 |647 |
|2 | MATERIAL | |999 |1519 |
|3 | TABLE SCAN |T2 |999 |647 |
=====================================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
conds(nil), nl_params_(nil)
1 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
2 - output([T2.D2]), filter(nil)
3 - output([T2.D2]), filter(nil),
access([T2.D2]), partitions(p0)
MJ
In the following sample code, Q4 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 is ordered in relation to the JOIN column.
In Q4, the join condition is t1.c2 = t2.d2, which means sorting data of the t1 table by c2 and data of the t2 table by d2. In Q4, 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.
Q4:
obclient>EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2
WHERE c2 = d2 AND c1 + d1 > 10\G
*************************** 1. row ***************************
Query Plan:
| ======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN | |3261 |14199|
|1 | SORT | |999 |4505 |
|2 | TABLE SCAN|T1 |999 |669 |
|3 | SORT | |999 |4483 |
|4 | TABLE SCAN|T2 |999 |647 |
======================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
equal_conds([T1.C2 = T2.D2]), other_conds([T1.C1 + T2.D1 > 10])
1 - output([T1.C2], [T1.C1]), filter(nil), sort_keys([T1.C2, ASC])
2 - output([T1.C2], [T1.C1]), filter(nil),
access([T1.C2], [T1.C1]), partitions(p0)
3 - output([T2.D2], [T2.D1]), filter(nil), sort_keys([T2.D2, ASC])
4 - output([T2.D2], [T2.D1]), filter(nil),
access([T2.D2], [T2.D1]), partitions(p0)
In the following sample code, the join condition in Q5 is t1.c1 = t2.d1, which means sorting data of the t1 table by c1 and data of the t2 table by d1. In this execution plan, a primary table scan is selected for t2, and the results are sorted by d1. In this case, no SORT operator is needed. 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.
Q5:
obclient>EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c1 = d1\G
*************************** 1. row ***************************
Query Plan:
| =====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-------------------------------------
|0 |MERGE JOIN | |990 |6096|
|1 | SORT | |999 |4505|
|2 | TABLE SCAN|T1 |999 |669 |
|3 | TABLE SCAN |T2 |999 |647 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
equal_conds([T1.C1 = T2.D1]), other_conds(nil)
1 - output([T1.C2], [T1.C1]), filter(nil), sort_keys([T1.C1, ASC])
2 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
3 - output([T2.D1], [T2.D2]), filter(nil),
access([T2.D1], [T2.D2]), partitions(p0)
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 lists the output information.
| Information | Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. In the sample code, filter is set to nil because no filter condition is configured for the MJ operator. |
| equal_conds | The 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, Q4 has an additional condition: t1.c1 + t2.d1 > 10. |
HJ
In the following sample code, Q6 uses HJ based on the USE_HASH hint. Operator 0 is an HJ operator and has two subnodes: operators 1 and 2. Its execution logic is shown in the following list:
Read data from the subnode on the left to generate a hash value based on the JOIN column, such as
t1.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 as
t2.d1, and then try to join the read data with the data in the corresponding hash tablet1.
Q6:
obclient> EXPLAIN SELECT /*+USE_HASH(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2
WHERE c1 = d1 AND c2 + d2 > 1\G
*************************** 1. row ***************************
Query Plan:
| ====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |330 |4850|
|1 | TABLE SCAN|T1 |999 |669 |
|2 | TABLE SCAN|T2 |999 |647 |
====================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
equal_conds([T1.C1 = T2.D1]), other_conds([T1.C2 + T2.D2 > 1])
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
2 - output([T2.D1], [T2.D2]), filter(nil),
access([T2.D1], [T2.D2]), partitions(p0)
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 lists the output information.
| Information | Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. In the sample code, 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, Q6 has an addition join condition: t1.c2 + t2.d2 > 1. |
For more information about the execution plans, see Optimize SQL statements in OceanBase Database Performance Optimization.