SQL Guide : solidDB® SQL statements : EXPLAIN PLAN FOR
  
EXPLAIN PLAN FOR
EXPLAIN PLAN FOR sql_statement
Usage
The EXPLAIN PLAN FOR statement shows the execution plan that the SQL optimizer has selected for a given SQL statement.
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 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 may 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 may contain, for example, index usage, the database table name and constraints used in solidDB® to select rows. Note that the constraints listed here may not match those constraints given in the SQL statement.
Example
EXPLAIN PLAN FOR select * from tables;
See also
solidDB® SQL statements