solidDB Help : Programming : Diagnostics and troubleshooting SQL : Observing performance : Execution plans
  
Execution plans
An execution plan is a series of primitive operations, and an ordering of these operations, that solidDB performs to execute the statement. Each operation in the execution plan is called a unit.
 
Unit
Description
JOIN UNIT*
Join unit joins two or more tables. The join can be done by using loop join or merge join.
TABLE UNIT
The table unit is used to fetch the data rows from a table or index.
ORDER UNIT
Order unit is used to order rows for grouping or to satisfy ORDER BY. The ordering can be done in memory or using an external disk sorter.
GROUP UNIT
Group unit is used to do grouping and aggregate calculation (SUM, MIN, and so on).
UNION UNIT*
Union unit performs the UNION operation. The unit can be done by using loop join or merge join.
INTERSECT UNIT*
Intersect unit performs the INTERSECT operation. The unit can be done by using loop join or merge join.
EXCEPT UNIT*
Except unit performs the EXCEPT operation. The unit can be done by using loop join or merge join.
*This unit is generated also for queries that reference only a single table. In that case no join is executed in the unit; it simply passes the rows without manipulating them.
The EXPLAIN PLAN FOR statement is used to show the execution plan that the SQL optimizer has selected for a given SQL statement, see EXPLAIN PLAN FOR.
The table returned by the EXPLAIN PLAN FOR statement contains the following columns.
 
Column name
Description
ID
The output row number, used only to guarantee that the rows are unique.
UNIT_ID
This is the internal unit id in the SQL interpreter. Each unit has a different id. The unit id is a sparse sequence of numbers, because the SQL interpreter generates unit ids also for those units that are removed during the optimization phase. If more than one row has the same unit id it means that those rows belong to the same unit. For formatting reasons the info from one unit can be divided into several different rows.
PAR_ID
Parent unit id for the unit. The parent id number refers to the id in the UNIT_ID column.
JOIN_PATH
For join, union, intersect, and except units there is a join path which specifies which tables are joined in the unit and the join order for tables. The join path number refers to the unit id in the UNIT_ID column. It means that the input to the unit comes from that unit. The order in which the tables are joined is the order in which the join path is listed. The first listed table is the outermost table in a loop join.
UNIT_TYPE
Unit type is the execution graph unit type.
INFO
Info column is reserved for additional information. It can contain, for example, index usage, the database table name and constraints used in solidDB to select rows. Note that the constraints listed here might not match those constraints given in the SQL statement.
The following texts can exist in the INFO column for different types of units.
 
Unit type
Text in Info column
Description
TABLE UNIT
tablename
The table unit refers to table tablename.
TABLE UNIT
constraints
The constraints that are passed to the database engine are listed. If for example in joins the constraint value is not known in advance, the constraint value is displayed as NULL.
TABLE UNIT
SCAN TABLE
Full table scan is used to search for rows.
TABLE UNIT
SCAN indexname
Index indexname is used to search for rows. If all selected columns are found from an index, sometimes it is faster to scan the index instead of the entire table because the index has fewer disk blocks.
TABLE UNIT
PRIMARY KEY
The primary key is used to search rows. This differs from SCAN in that the whole table is not scanned because there is a limiting constraint to the primary key attributes.
TABLE UNIT
INDEX indexname
Index indexname is used to search for rows. For every matching index row, the actual data row is fetched separately.
TABLE UNIT
INDEX ONLY indexname
Index indexname is used to search for rows. All selected columns are in the index, so the actual data rows are not fetched separately by reading from the table.
JOIN UNIT
MERGE JOIN
Merge join is used to join the tables.
JOIN UNIT
3-MERGE JOIN
A 3-merge join is used to merge the tables.
JOIN UNIT
LOOP JOIN
Loop join is used to join the tables.
ORDER UNIT
NO ORDERING REQUIRED
No ordering is required, the rows are retrieved in correct order from solidDB.
ORDER UNIT
EXTERNAL SORT
External sorter is used to sort the rows.
ORDER UNIT
FIELD n USED AS PARTIAL ORDER
For distinct result sets, an internal sorter (in-memory sorter) is used for sorting and the rows retrieved from solidDB are partially sorted with column number n. The partial ordering helps the internal sorter avoid multiple passes over the data.
ORDER UNIT
n FIELDS USED FOR PARTIAL SORT
An internal sorter (in-memory sorter) is used for sorting and the rows retrieved from solidDB are partially sorted with n fields. The partial ordering helps the internal sorter to avoid multiple passes over the data.
ORDER UNIT
NO PARTIAL SORT
Internal sorter is used for sorting. The rows are retrieved in random order from solidDB to the sorter.
UNION UNIT
MERGE JOIN
Merge join is used to join the tables.
UNION UNIT
3-MERGE JOIN
A 3-merge join is used to merge the tables.
UNION UNIT
LOOP JOIN
Loop join is used to join the tables.
INTERSECT UNIT
MERGE JOIN
Merge join is used to join the tables.
INTERSECT UNIT
3-MERGE JOIN
A 3-merge join is used to merge the tables.
INTERSECT UNIT
LOOP JOIN
Loop join is used to join the tables.
EXCEPT UNIT
MERGE JOIN
Merge join is used to join the tables.
EXCEPT UNIT
3-MERGE JOIN
A 3-merge join is used to merge the tables.
EXCEPT UNIT
LOOP JOIN
Loop join is used to join the tables.
Example 1
EXPLAIN PLAN FOR SELECT * FROM TENKTUP1 WHERE UNIQUE2_NI BETWEEN 0 AND 99;
ID
UNIT_ID
PAR_ID
JOIN_PATH
UNIT_TYPE
INFO
1
2
1
3
JOIN UNIT
 
2
3
2
0
TABLE UNIT
TENKTUP1
3
3
2
0
 
FULL SCAN
4
3
2
0
 
UNIQUE2_NI <= 99
5
3
2
0
 
UNIQUE2_NI >= 0
6
3
2
0
 
 
Execution graph
JOIN UNIT 2 gets input from TABLE UNIT 3
TABLE UNIT 3 for table TENKTUP1 does a full table scan with constraints UNIQUE2_NI <= 99 and UNIQUE2_NI >= 0
Diagram to illustrate execution graph
Example 2
EXPLAIN PLAN FOR SELECT * FROM TENKTUP1, TENKTUP2
WHERE TENKTUP1.UNIQUE2 > 4000 AND TENKTUP1.UNIQUE2 < 4500
AND TENKTUP1.UNIQUE2 = TENKTUP2.UNIQUE2;
 
ID
UNIT_ID
PAR_ID
JOIN_PATH
UNIT_TYPE
INFO
1
6
1
9
JOIN UNIT
MERGE JOIN
2
6
1
10
 
 
3
9
6
0
ORDER UNIT
NO ORDERING REQUIRED
4
8
9
0
TABLE UNIT
TENKTUP2
5
8
9
0
 
PRIMARY KEY
6
8
9
0
 
UNIQUE2 < 4500
7
8
9
0
 
UNIQUE2 > 4000
8
8
9
0
 
 
9
10
6
0
ORDER UNIT
NO ORDERING REQUIRED
10
7
10
0
TABLE UNIT
TENKTUP1
11
7
10
0
 
PRIMARY KEY
12
7
10
0
 
UNIQUE2 < 4500
13
7
10
0
 
UNIQUE2 > 4000
14
7
10
0
 
 
Execution graph
JOIN UNIT 6: the input from order units 9 and 10 are joined using merge join algorithm.
ORDER UNIT 9: orders the input from TABLE UNIT 8. Since the data is retrieved in correct order, no real ordering is needed.
ORDER UNIT 10: orders the input from TABLE UNIT 7. Since the data is retrieved in correct order, no real ordering is needed.
TABLE UNIT 8: rows are fetched from table TENKTUP2 using primary key. Constraints UNIQUE2 < 4500 and UNIQUE2 > 4000 are used to select the rows.
TABLE UNIT 7: rows are fetched from table TENKTUP1 using primary key. Constraints UNIQUE2 < 4500 and UNIQUE2 > 4000 are used to select the rows.
The diagram is described in the surrounding text
Go up to
Observing performance