4. The Explain Plan#
This chapter describes the explain plan which displays the access path taken by the Altibase server to execute an optimized query.
EXPLAIN PLAN Overview#
The ALTER SESSION SET EXPLAIN PLAN statement makes it possible to examine the access path taken by the Altibase server to process an optimized query. If EXPLAIN PLAN is set to ON or ONLY and a SQL statement is executed, the execution plan (plan tree) determined by the optimizer for the execution of the statement is returned. The user can use this data to enhance query performance by tuning the query for the optimizer to select a more effective execution plan.
Understanding the Plan Tree#
Various optimization methods (table joining methods, join orders, access paths, etc.) can be used to obtain the same result set when executing a SQL statement that retrieves data from multiple tables. Altibase determines the appropriate method on the basis of the following factors.
-
The indexes available for use
-
The order of tables and rows within the SQL statement
-
The optimization method
It is possible to view the plan tree for a SQL statement by setting the EXPLAIN PLAN property appropriately. Users can check the plan tree to clearly understand how Altibase is executing a SQL statement.
Displaying the Plan Tree#
A plan tree can only be viewed in an iSQL session, and only provides information for SELECT statements. To view a plan tree, the following command must be executed in iSQL, before the SELECT statement is executed.
ALTER SESSION SET EXPLAIN PLAN = option;
The option can be set to ON, OFF or ONLY; the default setting is OFF.
-
ON
Plan tree information along with result record after executing SELECT statement is displayed. The plan tree displays the number of record accesses, the amount of memory occupied by the tuple, and the cost.
-
ONLY
After preparing the SELECT statement, information of the explain plan for the SQL statement is displayed without actually executing the statement. This option is used when the user wants to check only the plan tree for a SELECT statement to which one or more host variables are bound or a query that takes a long time to execute. Since EXPLAIN PLAN = ONLY generates a plan tree without executing the query, items whose values are determined after the actual execution of the statement (e.g., ACCESS) are displayed as question marks("??").
-
OFF
After the SELECT statement is executed, the resultant records are displayed, but no information on the plan tree is provided.
Use the following command to obtain further information (e.g., how the conditions in a WHERE clause written by a user are processed and etc.).
ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 1;
The above command sets the TRCLOG_DETAIL_PREDICATE property to 1 and displays detailed information of how the conditions in the WHERE clause are processed (whether fixed key range processing, variable key range processing, filter processing, etc. is used.). This allows the user to check which predicates are executed using an index scan for queries that have complicated WHERE clauses. Note, however, that this information might not be output if the query has been changed for optimization.
Consider for example the output of the query statement shown below.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 1;
Alter success.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
Alter success.
iSQL> SELECT * FROM t1 WHERE i1 = 1;
T1.I1
--------------
1
1 row selected.
If TRCLOG_DETAIL_PREDICATE = 1 and EXPLAIN PLAN = ON
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 4, COST: 0.00 )
SCAN ( TABLE: T1, INDEX: IDX1, RANGE SCAN, ACCESS: 1, COST: 0.00 )
[ FIXED KEY ]
AND
OR
I1 = 1
If TRCLOG_DETAIL_PREDICATE = 0 and EXPLAIN PLAN = ON
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 4, COST: 0.00 )
SCAN ( TABLE: T1, INDEX: IDX1, RANGE SCAN, ACCESS: 1, COST: 0.00 )
If TRCLOG_DETAIL_PREDICATE = 0 and EXPLAIN PLAN = ONLY
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 4, COST: 0.00 )
SCAN ( TABLE: T1, INDEX: IDX1, RANGE SCAN, ACCESS: ??, COST: 0.00 )
Example#
<Example 1> Set the plan tree to be output.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
iSQL> SELECT e_firstname, e_lastname
FROM employees
WHERE emp_job = 'programmer';
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
Ryu Momoi
Elizabeth Bae
2 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 44, COST: 0.15 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 )
------------------------------------------------------------
<Example 2> Set the plan tree not to be output.
iSQL> ALTER SESSION SET EXPLAIN PLAN = OFF;
Alter success.
iSQL> SELECT e_firstname, e_lastname
FROM employees
WHERE emp_job = 'programmer';
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
Ryu Momoi
Elizabeth Bae
2 rows selected.
<Example 3> Set the plan tree to be output without executing the query statement.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ONLY;
Alter success.
iSQL> SELECT e_firstname, e_lastname
FROM employees
WHERE emp_job = 'programmer';
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
No rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 44, COST: 0.15 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: ??, COST: 0.14 )
------------------------------------------------------------
Reading Plan Trees#
A plan tree is made up of multiple plan nodes with relationships defined therebetween. To depict this relationship, a child node is indented one space further than a parent node in the EXPLAIN PLAN result. In addition, the subquery is output between ::SUB-QUERY BEGIN and ::SUB-QUERY END.
iSQL> SELECT c.c_lastname
FROM customers c
WHERE c.cno IN
(SELECT /*+ no_unnest */ o.cno
FROM orders o
WHERE o.ono = 12310001);
C_LASTNAME
------------------------
Fedorov
1 row selected.
-----------------------------------------------
4 PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 22, COST: 0.20 )
3 SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 20,
COST: 0.19 )
::SUB-QUERY BEGIN
2 PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 3, COST: 0.04 )
1 SCAN ( TABLE: ORDERS O, INDEX: ODR_IDX2,
RANGE SCAN, ACCESS: 29, COST: 0.04 )
::SUB-QUERY END
-----------------------------------------------
-
The order number (ono) in the orders table is scanned using an index. The number of times that records in the orders table were accessed by an index is 20. To scan the orders table on the basis of a column for which an index was not defined, the entire orders table ("full scan") would need to be scanned in order to find records matching the condition. That is, the user can choose whether to perform a full scan or an index scan by comparing cost information of the plan nodes.
-
The cno column is selected from the orders table to create a new relation1, which has one column.
-
The entire customer table is scanned to retrieve rows that satisfy the c.cno=o.cno condition. The access count is equal to the number of records (20) in the customer table.
-
The cname column is selected from the customers table to create a new relation2.
-
relation2 is output.
Interpreting Plan Trees#
This section provides an example and briefly explains how to interpret a plan tree. The explain plan is determined by following the entire plan tree wherein plan nodes are connected in tree form.
Each individual node in the execution plan tree is displayed as one row. The further the node is indented from the left (the further it is located towards the right), the lower the node is and it is executed faster than other nodes.
In the above example, the PROJECT node is the root node and the SCANS of tables T1 and T2 are the lowest nodes. For nodes that are indented as much (e.g., the T1 and T2 scan nodes), the node that appears first is handled as the left node of the higher node.
The request to fetch records is handled in a top-down manner and records are returned in a bottom-up manner.
In the above example, the node that accesses the database first is the SCAN node of the T1 table, followed by the T2 SCAN and T3 SCAN. The numbers next to each node indicate the node execution order.
The following figure shows the execution plan described above in tree diagram form.
Using Plan Trees#
If the EXPLAIN PLAN property is appropriately set, the plan tree can be viewed without directly executing the SQL statement. Thus, the performance of a SQL statement can be enhanced by viewing the plan tree and comparing it with other plan trees.
The following information can be obtained from plan trees.
- The execution plan created by the optimizer
- Object properties (e.g. tables and indexes)
- Any indexes that are used
- The joining methods that are used
- The optimized join order
The performance of an optimized SQL statement can be verified as below.
- Execute a new SQL statement and compare the results with the execution results of the previous SQL statement.
- Create a new plan tree and compare it with the previous plan tree.
- Double-check the accuracy of object properties (e.g. tables and indexes).
Plan Nodes#
This section explores the functionality of the plan nodes comprising the plan tree, their output format by EXPLAIN PLAN, and query examples.
AGGREGATION#
Format#
AGGREGATION ( ITEM_SIZE: item_size, GROUP_COUNT: group_count, COST: cost )
[Table 4-1] Information of the AGGREGATION Node
| Item | Description |
|---|---|
| ITEM_SIZE | The record size for one group |
| GROUP_COUNT | The number of groups created by the plan node |
| COST | The estimated cost |
Description#
The AGGREGATION node is a physical entity that performs aggregate operations according to the relational model. This node has one child node and does not use additional space to store intermediate results. This node performs aggregate operations on records in the same group.
This node is used to execute the following queries.
-
Aggregate operations using the sort order
-
Aggregate operations on clauses that contain the DISTINCT keyword
Aggregate Operations Using the Sort Order#
When the AGGREGATION node is used to perform an aggregate operation with the sort order, execution plan information as shown below is output. It can be seen that the AGGREGATION node uses data classified by the GROUPING node to perform the SUM(i2) operation. These records are grouped using the SUM(i2) and GROUP BY i3 expressions. There are five groups containing one or more 16-byte records.
Aggregate Operations on Clauses Containing the DISTINCT Keyword#
The AGGREGATION node requires additional space to remove duplicates if a DISTINCT clause is contained in an aggregate function. The example below shows the use of an AGGREGATION node to process the SUM(DISTINCT i2) clause.
Example#
Output the total number of departments and the average salary of all employees.
iSQL> SELECT COUNT(DISTINCT dno),
AVG(salary)
FROM employees;
COUNT(DISTINCT DNO) AVG(SALARY)
------------------------------------
8 1836.64706
1 row selected.
-----------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 31, COST: 0.03 )
AGGREGATION ( ITEM_SIZE: 72, GROUP_COUNT: 1, COST: 0.02 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.01 )
-----------------------------------------------
ANTI-OUTER-JOIN#
Format#
ANTI-OUTER-JOIN ( METHOD: method, COST: cost )
[Table 4-2] Information of the ANTI-OUTER-JOIN Node
| Item | Description |
|---|---|
| METHOD | The joining method |
| COST | The estimated cost |
Description#
The ANTI-OUTER-JOIN node is the physical entity that performs ANTI OUTER JOIN operations according to the relational model. This node has two child nodes, does not generate intermediate results, and controls the execution flow of child nodes.
The ANTI-OUTER-JOIN node is used to process only FULL OUTER JOINs. As shown below, this node is used when indexes have been defined for all of the columns referenced in an ON join condition.
As shown above, when processing a FULL OUTER JOIN, an ANTI-OUTER-JOIN node and a LOJN execution node always have the CONC execution node as their parent node. The join condition in the ON clause is processed by both the LEFT-OUTER-JOIN and ANTI-OUTERJOIN nodes.
Example#
Output the department number, department name, goods number of the department and goods that are in the same location.
iSQL> CREATE INDEX dep_idx2 ON departments(dep_location);
Create success.
iSQL> CREATE INDEX gds_idx1 ON goods(goods_location);
Create success.
iSQL> SELECT d.dno
, d.dname
, g.gno
FROM departments d FULL OUTER JOIN goods g
ON d.dep_location = g.goods_location;
DNO DNAME GNO
-----------------------------------------------------------
.
.
.
38 rows selected.
-----------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 46, COST: 0.29 )
CONCATENATION
LEFT-OUTER-JOIN ( METHOD: INDEX_NL, COST: 0.01 )
SCAN ( TABLE: DEPARTMENTS D, FULL SCAN, ACCESS: 38, COST: 0.00 )
SCAN ( TABLE: GOODS G, INDEX: GDS_IDX1, RANGE SCAN, ACCESS: 38, COST: 0.02 )
ANTI-OUTER-JOIN( METHOD: ANTI, COST: 0.01 )
SCAN ( TABLE: GOODS G, FULL SCAN, ACCESS: 38, COST: 0.02 )
SCAN ( TABLE: DEPARTMENTS D, INDEX: DEP_IDX2, RANGE SCAN, ACCESS: 38, COST: 0.00 )
-----------------------------------------------------------
iSQL> DROP INDEX dep_idx2;
Drop success.
iSQL> DROP INDEX gds_idx1;
Drop success.
BAG-UNION#
Format#
BAG-UNION
Description#
The BAG-UNION node is the physical entity that performs UNION ALL operations according to the relational model. This node has two or more child nodes, does not generate intermediate results, and controls the execution flow of child nodes.
This node is executed as shown in the following example.
In the above example, the BAG-UNION node processes the UNION ALL clause by combining the results of both queries.
Example#
Output the employee name and salary of all employees whose job is a salesperson and whose salary is higher than 2000000.
iSQL> SELECT e_firstname
, e_lastname
, emp_job
, salary
FROM employees
WHERE emp_job = 'SALES REP'
UNION ALL
SELECT e_firstname
, e_lastname
, emp_job
, salary
FROM employees
WHERE salary > 2000;
E_FIRSTNAME E_LASTNAME EMP_JOB SALARY
------------------------------------------------------------------------------
Farhad Ghorbani PL 2500
Elizabeth Bae programmer 4000
Zhen Liu webmaster 2750
Yuu Miura PM 2003
Wei-Wei Chen manager 2300
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 70, COST: 0.58 )
VIEW ( ACCESS: 5, COST: 0.43 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 70, COST: 0.18 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 )
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 70, COST: 0.26 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 )
------------------------------------------------------------
CONCATENATION#
Format#
CONCATENATION
Description#
The CONCATENATION plan node performs concatenation operations according to the relational model. This has two child nodes, does not generate intermediate results and controls the execution flow of child nodes.
This node is used to process FULL OUTER JOINs and DNFs. A description of how it is used with FULL OUTER JOINs is provided in the example for the ANTI-OUTER-JOIN node. The following example describes how the CONCATENATION node is used to execute by DNF.
In the above example, the (i1 = 1000) condition is executed by the left SCAN plan node and the (i2 = 100) condition is executed by the right SCAN plan node. By doing so, both the IDX1 and IDX2 indexes can be used and the CONCATENATION plan node is used to concatenate the above results. The FILTER node is used to remove values that overlap with the right SCAN.
Example#
Please refer to the example for the ANTI-OUTER-JOIN node.
CONNECT BY#
Format#
When an Index Does Not Exist#
CONNECT BY ( ACCESS: acc_num, COST: cost )
When Using an Index#
CONNECT BY (INDEX: index_name, ACCESS: acc_num, COST: cost )
[Table 4-3] Information of the CONNECT BY Node
| Item | Description |
|---|---|
| INDEX | The name of the index being used |
| ACCESS | The number of times the records were accessed |
| COST | The estimated cost |
Description#
The CONNECT BY node performs a unique operation that does not exist in the relational model; it executes hierarchical queries. This node has one child node and the child node is always a materialization node.
In the following example, the CONNECT BY node is used to execute a hierarchical query.
Example#
The following hierarchical query specifies rows with the ID column value 0 as root rows for the hierarchy and retrieves hierarchical data.
CREATE TABLE hier_order(id INTEGER, parent INTEGER);
INSERT INTO hier_order VALUES(0, NULL);
INSERT INTO hier_order VALUES(1, 0);
INSERT INTO hier_order VALUES(2, 1);
INSERT INTO hier_order VALUES(3, 1);
INSERT INTO hier_order VALUES(4, 1);
INSERT INTO hier_order VALUES(5, 0);
INSERT INTO hier_order VALUES(6, 0);
INSERT INTO hier_order VALUES(7, 6);
INSERT INTO hier_order VALUES(8, 7);
INSERT INTO hier_order VALUES(9, 7);
INSERT INTO hier_order VALUES(10, 6);
iSQL> SELECT id
, parent
, level
FROM hier_order START WITH id = 0 CONNECT BY PRIOR id = parent
ORDER BY level;
ID PARENT LEVEL
-------------------------------------------------
0 1
1 0 2
5 0 2
6 0 2
2 1 3
3 1 3
4 1 3
7 6 3
10 6 3
8 7 4
9 7 4
11 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 16, COST: BLOCKED )
SORT ( ITEM_SIZE: BLOCKED, ITEM_COUNT: 11, ACCESS: 11, COST: BLOCKED )
CONNECT BY ( ACCESS: 23, COST: BLOCKED )
SCAN ( TABLE: SYS.HIER_ORDER, FULL SCAN, ACCESS: 22, COST: BLOCKED )
------------------------------------------------------------
COUNT#
Format#
When an index is used:#
COUNT (TABLE: tbl_name, INDEX: index_name, ACCESS: acc_num, DISK_PAGE_COUNT: num, COST: cost )
When an index is not used:#
COUNT (TABLE: tbl_name, FULL SCAN, ACCESS: acc_num, DISK_PAGE_COUNT: num, COST: cost )
[Table 4-5] Information of the COUNT Node
| Item | Description |
|---|---|
| TABLE | The name of the table that was accessed |
| INDEX | The name of the index being used |
| ACCESS | The number of times the records were accessed |
| DISK_PAGE_COUNT | The number of disk pages in the table. This information is unavailable for memory tables. |
| COST | The estimated cost |
Description#
The COUNT node executes COUNT(*) operations that do not have a GROUP BY clause in the relational model.
The following is an example of the COUNT node. It shows that the index is used to obtain the COUNT(*) value without the actual data being accessed.
Example#
Output the total number of employees.
iSQL> SELECT COUNT(*) rec_count
FROM employees;
REC_COUNT
-----------------------
20
1 row selected.
-----------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 8, COST: 0.02 )
COUNT ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 1, COST: 0.02 )
-----------------------------------------------
DISTINCT#
Format#
When intermediate results are cached in memory#
DISTINCT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, BUCKET_COUNT: bucket_count, ACCESS: acc_num, COST: cost )
When intermediate results are stored on disk#
DISTINCT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-6] Information of the DISTINCT Node
| Item | Description |
|---|---|
| ITEM_SIZE | The size of duplicate records that were removed |
| ITEM_COUNT | The number of duplicate records that were removed |
| BUCKET_COUNT | The number of hash buckets |
| DISK_PAGE_COUNT | The number of disk pages in a temporary stored table |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The DISTINCT node removes duplicates in the hashing method in the relational model. It has one child node and uses a temporary table to store intermediate results.
The DISTINCT plan node is used for many different purposes. The plan trees of this node being used for various purposes are shown below.
Used for DISTINCT#
The DISTINCT node can be used to perform DISTINCT operations.
The following example shows the DISTINCT node used to perform a DISTINCT operation.
Used for UNION#
The DISTINCT node can be used to perform UNION operations.
In the following example, the DISTINCT node is used to remove duplicates to perform a UNION operation.
Used for Subquery Key Range#
The DISTINCT node can be used to perform subquery key range operations. This node is used to remove duplicates to prevent an overlapping value being searched for with an index.
The following example shows the DISTINCT plan node used to remove duplicates to perform a subquery key range operation. The DISTINCT plan node is used to remove the duplicate values of T2.i4.
Example#
Output the name of the customer who ordered C111100001.
iSQL> SELECT DISTINCT customers.c_firstname||customers.c_lastname cname
FROM customers
WHERE customers.cno IN (SELECT orders.cno
FROM orders
WHERE orders.gno = 'C111100001 ');
CNAME
--------------------------------------------
Estevan Sanchez
Pierre Martin
Phil Dureault
Fyodor Fedorov
4 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 42, COST: 0.10 )
DISTINCT ( ITEM_SIZE: 64, ITEM_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 4, COST: 0.07 )
SEMI-MERGE-JOIN ( METHOD: MERGE, COST: 0.03 )
SCAN ( TABLE: CUSTOMERS, INDEX: __SYS_IDX_ID_153, RANGE SCAN, ACCESS: 17, COST: 0.01 )
SORT ( ITEM_SIZE: 16, ITEM_COUNT: 4, ACCESS: 4, COST: 0.03 )
SCAN ( TABLE: ORDERS $$1_$VIEW1_$ORDERS, INDEX: ODR_IDX3, RANGE SCAN, ACCESS: 4, COST: 0.00 )
------------------------------------------------------------
FILTER#
Format#
FILTER
Description#
The FILTER node is a physical object that performs a SELECT operation in the relational model. It has one child node and searches for a given condition without directly accessing the table.
The FILTER node only provides the node name. Set the TRCLOG_DETAIL_PREDICATE property to 1 to output information of the conditions processed by the filter.
Outputting Information of the FILTER Node#
In the following example, the FILTER node outputs only the node name. The FILTER node is used to process the (having i2 < 2) condition in the example below.
The user can view this information by setting the TRCLOG_DETAIL_PREDICATE property to 1. The FILTER node has been used to process the (i2 < 2) condition as shown below.
Example#
Output the goods number and order quantity for the goods that received two or more orders.
iSQL> SELECT gno
, COUNT(*)
FROM orders
GROUP BY gno
HAVING COUNT(*) > 2;
GNO COUNT
------------------------------------
A111100002 3
C111100001 4
D111100008 3
E111100012 3
4 rows selected.
-----------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 24, COST: 0.08 )
FILTER
AGGREGATION ( ITEM_SIZE: 16, GROUP_COUNT: 16, COST: 0.02 )
GROUPING
SCAN ( TABLE: ORDERS, INDEX: ODR_IDX3, FULL SCAN, ACCESS: 30, COST: 0.02 )
-----------------------------------------------
FULL-OUTER-JOIN#
Format#
FULL-OUTER-JOIN ( METHOD: method, COST: cost )
[Table 4-7] Information of the FULL-OUTER-JOIN node
| Item | Description |
|---|---|
| METHOD | The joining method |
| COST | The estimated cost |
Description#
The FULL-OUTER-JOIN node performs a FULL OUTER JOIN operation in the relational model. It has two child nodes, does not generate intermediate results and controls the execution flow of child nodes.
Like normal joins, the FULL-OUTER-JOIN execution node is used for most joining methods (please refer to the example for the JOIN node). The following example shows a simple explain plan tree using the FULL-OUTER-JOIN execution node.
The FULL-OUTER-JOIN node generates a node on the right for storage, and the ON condition in the above example is executed by the SORT node.
Example#
Output the department number, department name and goods number of the departments and goods with the same location.
iSQL> INSERT INTO departments VALUES(6002, 'headquarters', 'CE0002', 100);
1 row inserted.
iSQL> SELECT d.dno, d.dname, g.gno
FROM departments d FULL OUTER JOIN goods g
ON d.dep_location = g.goods_LOCATION;
DNO DNAME GNO
------------------------------------------------------------
A111100001
A111100002
B111100001
C111100001
C111100002
D111100001
D111100002
D111100003
D111100004
D111100005
D111100006
D111100007
D111100008
D111100009
D111100010
D111100011
E111100001
E111100002
E111100003
E111100004
6002 headquarters E111100005
E111100006
E111100007
E111100008
E111100009
E111100010
E111100011
E111100012
E111100013
F111100001
1001 RESEARCH DEVELOPMENT DEPT 1
1002 RESEARCH DEVELOPMENT DEPT 2
1003 SOLUTION DEVELOPMENT DEPT
2001 QUALITY ASSURANCE DEPT
3001 CUSTOMERS SUPPORT DEPT
3002 PRESALES DEPT
4001 MARKETING DEPT
4002 BUSINESS DEPT
38 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 46, COST: 0.60 )
FULL-OUTER-JOIN ( METHOD: HASH, COST: 0.29 )
SCAN ( TABLE: GOODS G, FULL SCAN, ACCESS: 38, COST: 0.02 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 9, BUCKET_COUNT: 1024, ACCESS: 38, COST: 0.29 )
SCAN ( TABLE: DEPARTMENTS D, FULL SCAN, ACCESS: 38, COST: 0.01 )
------------------------------------------------------------
GROUP-AGGREGATION#
Format#
When intermediate results are cached in memory#
GROUP-AGGREGATION ( ITEM_SIZE: item_size, GROUP_COUNT: group_count, BUCKET_COUNT: bucket_count, ACCESS: acc_num, COST: cost )
When intermediate results are stored on disk#
GROUP-AGGREGATION ( ITEM_SIZE: item_size, GROUP_COUNT: group_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-8] Information of the GROUP-AGGREGATION Node
| Item | Description |
|---|---|
| ITEM_SIZE | The size of each record hashed for a group operation |
| GROUP_COUNT | The number of groups |
| BUCKET_COUNT | The number of hash buckets |
| DISK_PAGE_COUNT | The number of disk pages in a temporary table |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The GROUP-AGGREGATION node performs group and aggregate operations in the hashing method in the relational model. It has one child node and uses temporary tables to store intermediate results.
In the following example, the GROUP-AGGREGATION node is used to perform group and aggregate operations in the hashing method. The GROUP-AGGREGATION node is used to execute GROUP BY i4, AVG(i1) and SUM(i2).
Example#
Output the total amount of wages for each position in every department (use the GROUP BY clause for multiple columns).
iSQL> SELECT dno
, emp_job
, COUNT(emp_job) num_emp
, SUM(salary) sum_sal
FROM employees
GROUP BY dno, emp_job;
DNO EMP_JOB NUM_EMP SUM_SAL
-----------------------------------------------
.
.
.
16 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 55, COST: 0.80 )
GROUP-AGGREGATION ( ITEM_SIZE: 56, GROUP_COUNT: 16, BUCKET_COUNT: 1024, ACCESS: 16, COST: 0.13 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.01 )
------------------------------------------------------------
GROUPING#
Format#
GROUPING
Description#
The GROUPING node is the physical entity that checks for duplicates by sort order in the relational model. This node has one child node and alternately checks the record returned from the child node against the previously returned record.
This node is used to execute the following queries.
- Determining whether or not the group is the same using sort order.
- Removing duplicates using sort order.
- Executing a DISTINCT aggregation using sort order.
Grouping Using Sort Order#
When the GROUPING node is used to determine whether or not the group is the same by sort order, the explain plan is output as shown in the following example. As shown below, the GROUPING node has been used to process the (GROUP BY I3) clause without using any additional storage space. An explanation thereof is provided in the description of processing GROUP BY by sort order.
Removing duplicates using sort order#
When the GROUPING node is used to eliminate duplicates by sort order, the explain plan is output as shown below. In the following execution plan, the GROUPING node is used to execute the (DISTINCT i3) and a storage space has not been used for DISTINCT. For further information, please refer to DISTINCT optimization by sort order.
Executing a DISTINCT Aggregation by Sort Order#
When the GROUPING node is used to execute a DISTINCT aggregation by sort order, the execution plan is output as shown below. In the following example, the GROUPING node is used to remove duplicates in the (DISTINCT i2) clause while executing (COUNT(DISTINCT i2)). For further information, please refer to DISTINCT aggregation optimization by sorting the order.
Example#
Output the number of customers each employee is responsible for and the number of goods sold to each customer.
iSQL> SELECT eno
, COUNT(DISTINCT cno)
, SUM(qty)
FROM orders
GROUP BY eno;
ENO COUNT(DISTINCT CNO) SUM(QTY)
----------------------------------------------------------
12 8 17870
19 6 25350
20 8 13210
3 rows selected.
-----------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 24, COST: 0.77 )
AGGREGATION ( ITEM_SIZE: 32, GROUP_COUNT: 3, COST: 0.02 )
GROUPING
SCAN ( TABLE: ORDERS, INDEX: ODR_IDX1, FULL SCAN, ACCESS: 30, COST: 0.02 )
-----------------------------------------------
HASH#
Format#
When intermediate results are cached in memory#
HASH ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, BUCKET_COUNT: bucket_count, ACCESS: acc_num, COST: cost )
When intermediate results are stored on disk#
HASH ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-9] Information of the HASH Node
| Item | Description |
|---|---|
| ITEM_SIZE | The record size for hashing |
| ITEM_COUNT | The number of records included in hashing |
| BUCKET_COUNT | The number of hash buckets |
| DISK_PAGE_COUNT | The number of disk pages in the temporary table |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The HASH node performs hashing operations in the relational model. It has one child node and uses temporary tables to store intermediate results.
The HASH node is used for various purposes. The execution plan tree for each use is described below.
Using with Joins#
The HASH node can be used to perform a join operation.
In the following example, a HASH node has been created to perform a join operation. A HASH node is created and a hash-based join is performed to check the T1.i1 = T2.i1 join condition.
Used for Subquery Search#
The HASH node can be used to perform a comparison operation with a subquery.
In the following example, the HASH execution node is used to process i4 in (select i4 from t2). The HASH node hashes the t2.i4 value and checks whether a value corresponding to each t2.i4 exists in the HASH.
Example#
Output the names and department names of all department managers.
iSQL> ALTER SESSION SET EXPLAIN PLAN = OFF;
Alter success.
iSQL> CREATE TABLE dept2 TABLESPACE sys_tbs_disk_data
AS SELECT * FROM department;
Create success.
iSQL> CREATE TABLE manager
( eno INTEGER PRIMARY KEY,
mgr_no INTEGER,
mname VARCHAR(20),
address VARCHAR(60)
)
TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> INSERT INTO manager VALUES(2, 1, 'HJNO', '11 Inyoung Bldg. Nonhyun-dong Kangnam-guSeoul, Korea');
1 row inserted.
iSQL> INSERT INTO manager VALUES(7, 2, 'HJMIN', '44-25 Youido-dong Youngdungpo-gu Seoul, Korea');
1 row inserted.
iSQL> INSERT INTO manager VALUES(8, 7, 'JDLEE', '3101 N. Wabash Ave. Brooklyn, NY');
1 row inserted.
iSQL> INSERT INTO manager VALUES(12, 7, 'MYLEE', '130 Gongpyeongno Jung-gu Daegu, Korea');
1 row inserted.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
Alter success.
iSQL> SELECT m.mname, d.dname
FROM dept2 d, manager m
WHERE d.mgr_no = m.mgr_no;
MNAME DNAME
---------------------------------------------------------
JDLEE BUSINESS DEPT
MYLEE BUSINESS DEPT
2 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 54, COST: 28.11 )
JOIN ( METHOD: HASH, COST: 28.09 )
SCAN ( TABLE: DEPT2 D, FULL SCAN, ACCESS: 9, DISK_PAGE_COUNT: 64, COST: 14.01 )
HASH ( ITEM_SIZE: 32, ITEM_COUNT: 4, DISK_PAGE_COUNT: 64, ACCESS: 2, COST: 28.09 )
SCAN ( TABLE: MANAGER M, FULL SCAN, ACCESS: 4, DISK_PAGE_COUNT: 64, COST: 14.00 )
------------------------------------------------------------
JOIN#
Format#
JOIN ( METHOD: method, COST: cost )
[Table 4-10] Information of the JOIN Node
| Item | Description |
|---|---|
| METHOD | The joining method |
| COST | The estimated cost |
Description#
The JOIN node performs join operations in the relational model. This node has two child nodes, does not create intermediate results and controls the execution flow of the child nodes.
The JOIN node is used for almost all normal join operations.
In which form the plan tree is output for the following various joining methods is described below.
-
Full nested loop join
-
Full store nested loop join
-
Index nested loop join
-
Inverse index nested loop join
-
One-pass sort join
-
Two-pass sort join
-
Inverse sort join
-
One-pass hash join
-
Two-pass hash join
-
Inverse hash join
The following example processes the same query in different joining methods and shows the execution plan tree for each method.
Part of the plan tree of the joining method is featured as a figure on the left, and the actual execution plan is shown on the right.
The Execution Plan Tree for a Full Nested Loop Join#
In the above execution plan, the join condition is processed by the SCAN node on the right with a repetitive full search on the T2 table.
The Execution Plan Tree for a Full Store Nested Loop Join#
In the above execution plan, the join condition is processed by the FILTER node above JOIN. The T2 table is searched once and after storing the results, a repetitive full search is performed.
The Execution Plan of an Index Nested Loop Join#
In the above execution plan, the join condition is processed by the SCAN node on the right using the index.
The Execution Plan of an Inverse Index Nested Loop Join#
The execution Plan of a One-pass Sort Join#
In the above execution plan, the join condition is processed using data sorted in order by the SORT node on the right.
The Execution Plan of a Two-pass Sort Join#
In the above execution plan, the join condition is processed using data sorted in order by the SORT node on the right; a SORT node is generated on the left also.
The Execution Plan of an Inverse Sort Join#
SCAN The Execution Plan of a One-pass Hash Join#
In the above execution plan, the join condition is processed using the data hashed by the HASH node on the right.
The Execution Plan of a Two-pass Hash Join#
In the above execution plan, the join condition is processed using the data sorted in order by the HASH node on the right; nevertheless a HASH node is generated on the left as well.
The Execution Plan of an Inverse Hash Join#
Example#
Output the employee number, order number, goods number, order quantity of the employee whose last name is 'Marquez'.
iSQL> SELECT e.eno, ono, cno, gno, qty
FROM employees e, orders o
WHERE e.eno = o.eno
AND e.e_lastname = 'Marquez';
ENO ONO CNO GNO QTY
---------------------------------------------------------------------------
19 11290100 11 E11110000 1500
19 12100277 5 D111100008 2500
19 12300001 1 D111100004 1000
19 12300005 4 D111100008 4000
19 12300010 16 D111100010 2000
19 12310004 5 E111100010 5000
19 12310008 1 D111100003 100
19 12310011 15 E111100012 10000
19 12310012 1 C111100001 250
9 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 5, TUPLE_SIZE: 40, COST: 0.30 )
JOIN ( METHOD: INDEX_NL, COST: 0.15 )
SCAN ( TABLE: EMPLOYEES E, FULL SCAN, ACCESS: 20, COST: 0.14 )
SCAN ( TABLE: ORDERS O, INDEX: ODR_IDX1, RANGE SCAN, ACCESS: 9, COST: 0.02 )
------------------------------------------------------------
LEFT-OUTER-JOIN#
Format#
LEFT-OUTER-JOIN ( METHOD: method, SKIP RIGHT COUNT: count, COST: cost )
| Item | Description |
|---|---|
| METHOD | The joining method |
| SKIP RIGHT COUNT | The number of skip right count - Shown when TRCLOG_DETAIL_INFORMATION = 1 - Not shown when skip count is 0 |
| COST | The estimated cost |
Description#
The LEFT-OUTER-JOIN node performs a LEFT OUTER JOIN operation in the relational model. It has two child nodes, does not generate intermediate results and controls the execution flow of child nodes.
Like normal joins, the LEFT-OUTER-JOIN node is used for most joining methods (please refer to the example for the JOIN node). A simple execution plan tree in which the LEFT-OUTERJOIN node is used is provided below.
Example#
Output the department numbers of all departments and the employee names of all employees (output also the department number 5001 which has no employees).
iSQL> INSERT INTO departments VALUES(5001, 'Quality Assurance', 'Mokpo', 22);
1 row inserted.
iSQL> SELECT d.dno
, e.e_firstname
, e.e_lastname
FROM departments d LEFT OUTER JOIN employees e ON d.dno = e.dno
ORDER BY d.dno;
DNO E_FIRSTNAME E_LASTNAME
------------------------------------------------------------
1001 Ken Kobain
1001 Wei-Wei Chen
1002 Ryu Momoi
1002 Mitch Jones
1003 Elizabeth Bae
1003 Zhen Liu
1003 Yuu Miura
1003 Jason Davenport
2001 Takahiro Fubuki
3001 Aaron Foster
3002 Chan-seung Moon
3002 Farhad Ghorbani
4001 Xiong Wang
4001 Curtis Diaz
4001 John Huxley
4002 Gottlieb Fleischer
4002 Sandra Hammond
4002 Alvar Marquez
4002 William Blake
5001
20 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 46, COST: 0.20 )
LEFT-OUTER-JOIN ( METHOD: INDEX_NL, COST: 0.01 )
SCAN ( TABLE: DEPARTMENTS D, INDEX: __SYS_IDX_ID_170, FULL SCAN, ACCESS: 9, COST: 0.01 )
SCAN ( TABLE: EMPLOYEES E, INDEX: EMP_IDX1, RANGE SCAN, ACCESS: 20, COST: 0.01 )
------------------------------------------------------------
LIMIT-SORT#
Format#
LIMIT-SORT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, STORE_COUNT: store_count, ACCESS: acc_num, COST: cost )
| Item | Description |
|---|---|
| ITEM_SIZE | The size of the stored record to be sorted |
| ITEM_COUNT | The number of used records |
| STORE_COUNT | The number of stored records |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The LIMIT-SORT node performs limited sort operations in the relational model. It has one child node, and uses temporary tables to store intermediate results.
The LIMIT-SORT node is used for various purposes. The execution plan tree for each purpose is described below.
Used for ORDER BY#
The LIMIT-SORT node can be used in an ORDER BY clause that includes a LIMIT.
In the following example, the LIMIT-SORT node is used for ORDER BY. Information of the plan tree shows that only 3 storage spaces have been used to sort 16384 records.
Used for Subquery Search#
The LIMIT-SORT node can be used for subquery search.
In the following example, the LIMIT-SORT node performs a subquery search by sorting in order some of the records and storing them. The LIMIT-SORT node stores the values necessary to execute the query from t2.i4 and consequently minimizes the cost of a comparison operation.
Example#
Mark the names, department numbers and salaries of all employees, sort them by department number and salary (in ascending order by department number and in descending order by salary) and output the top 10 (the sort order is defined by the ORDER BY list).
iSQL> SELECT e_firstname
, e_lastname
, dno
, salary
FROM employees
ORDER BY dno, salary DESC LIMIT 10;
E_FIRSTNAME E_LASTNAME DNO SALARY
-------------------------------------------------------------------------
Wei-Wei Chen 1001 2300
Ken Kobain 1001 2000
Ryu Momoi 1002 1700
Mitch Jones 1002 980
Elizabeth Bae 1003 4000
Zhen Liu 1003 2750
Yuu Miura 1003 2003
Jason Davenport 1003 1000
Takahiro Fubuki 2001 1400
Aaron Foster 3001 1800
10 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 55, COST: 0.31 )
LIMIT-SORT ( ITEM_SIZE: 16, ITEM_COUNT: 20, STORE_COUNT: 10, ACCESS: 10, COST: 0.14 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.01 )
------------------------------------------------------------
MATERIALIZATION#
Format#
MATERIALIZATION ( ACCESS: acc_num, COST: cost )
| Item | Description |
|---|---|
| ACCESS | The number of times the stored record is accessed |
| COST | The estimated cost |
Description#
The MATERIALIZATION node generates a temporary table on a view. It has one child node and uses temporary tables to store intermediate results.
Example#
Output the name, department number, salary of the employee whose salary is higher than the average of his or her department but lower than the average of the department with the highest average salary.
iSQL> CREATE VIEW v1 AS
(SELECT dno, AVG(salary) avg_sal
FROM employees GROUP BY dno);
Create success.
iSQL> SELECT e_firstname
, e_lastname
, e.dno
, e.salary
FROM employees e
, v1
WHERE e.dno = v1.dno
AND e.salary > v1.avg_sal
AND e.salary < (SELECT MAX(avg_sal) FROM v1);
E_FIRSTNAME E_LASTNAME DNO SALARY
-------------------------------------------------------------------------
Wei-Wei Chen 1001 2300
Ryu Momoi 1002 1700
John Huxley 4001 1900
Sandra Hammond 4002 1890
Alvar Marquez 4002 1800
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 55, COST: 0.32 )
JOIN ( METHOD: INDEX_NL, COST: 0.28 )
VIEW-SCAN ( VIEW: V1, ACCESS: 9, COST: 0.01 )
MATERIALIZATION ( ITEM_SIZE: 40, ITEM_COUNT: 9, COST: 0.00 )
VIEW ( ACCESS: 9, COST: 0.00 )
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 25, COST: 0.21 )
AGGREGATION ( ITEM_SIZE: 72, GROUP_COUNT: 9, COST: 0.01 )
GROUPING
SCAN ( TABLE: EMPLOYEES, INDEX: EMP_IDX1, FULL SCAN, ACCESS: 20, COST: 0.01 )
SCAN ( TABLE: EMPLOYEES E, INDEX: EMP_IDX1, RANGE SCAN, ACCESS: 19, COST: 0.27 )
::SUB-QUERY BEGIN
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 23, COST: 0.03 )
STORE ( ITEM_SIZE: 32, ITEM_COUNT: 1, ACCESS: 7, COST: 0.03 )
VIEW ( ACCESS: 1, COST: 0.00 )
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 23, COST: 0.03 )
GROUP-AGGREGATION ( ITEM_SIZE: 40, GROUP_COUNT: 1, BUCKET_COUNT: 1, ACCESS: 1, COST: 0.02 )
VIEW-SCAN ( VIEW: V1, ACCESS: 9, COST: 0.01 )
::SUB-QUERY END
------------------------------------------------------------
MERGE-JOIN#
Format#
MERGE-JOIN ( METHOD: method, COST: cost )
| Item | Description |
|---|---|
| METHOD | The joining method |
| COST | The estimated cost |
Description#
The MERGE-JOIN node performs a merge join operation in the relational model. It has two child nodes, does not generate intermediate results and controls the execution flow of child nodes. The MERGE-JOIN node takes one of the following as its child node: SCAN, SORT, MERGE-JOIN.
The MERGE-JOIN node is used to perform a normal join operation and sorts both left and right child nodes or processes a join using sort order.
Depending on the child node type, nine different merge-joins are possible; the plan trees of two of them are described below.
MERGE-JOIN Using an Index#
The join condition in the above explain plan is processed by the MERGE-JOIN node and uses column indexes included in the join condition (regardless of whether it is an inner or outer table).
MERGE-JOIN Using SORT#
The join condition in the above explain plan is processed by the MERGE-JOIN node, is sorted by the column included in the join condition and then used.
Example#
Output the employee number, name, department number and department name of all employees who joined the company before January 1st, 2010 (Both tables must have an index on the column related to the join predicate. Because an index scan is performed, records are returned sorted by the dno value from the left and right SCAN nodes of this node. When record values start to differ, the user should move the cursor of the two tables until records with identical values appear, instead of searching the records.).
iSQL> SELECT /*+ USE_MERGE(employees,departments) */ e.eno
, e_lastname
, d.dno
, dname
FROM employees e
, departments d
WHERE e.dno = d.dno
AND TO_CHAR(join_date, 'YYYY-MM-DD HH:MI:SS') < '2010-01-01 00:00:00';
ENO E_LASTNAME DNO DNAME
---------------------------------------------------------------------------
5 Ghorbani 3002 PRESALES DEPT
8 Wang 4001 MARKETING DEPT
18 Huxley 4001 MARKETING DEPT
7 Fleischer 4002 BUSINESS DEPT
12 Hammond 4002 BUSINESS DEPT
20 Blake 4002 BUSINESS DEPT
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 60, COST: 0.34 )
MERGE-JOIN ( METHOD: MERGE, COST: 0.24 )
SCAN ( TABLE: EMPLOYEES E, INDEX: EMP_IDX1, RANGE SCAN, ACCESS: 19, COST: 0.21 )
SCAN ( TABLE: DEPARTMENTS D, INDEX: __SYS_IDX_ID_153, RANGE SCAN, ACCESS: 15, COST: 0.01 )
------------------------------------------------------------
PARALLEL-QUEUE#
Format#
PARALLEL-QUEUE ( TID: tid )
[Table 4-13] Information on PARALLEL_QUEUE Node
| Item | Description |
|---|---|
| TID | Identification number of the thread running on the node |
Description#
The PARALLEL-QUEUE executes parallel queries. This node creates new threads to execute the subnodes, and passes the results to a higher node.
The following example illustrates the use of the PARALLEL_QUEUE node: In the example below, two PARALLEL-QUEUE nodes are running four SCAN nodes. The identification number (TID) assigned to each PARALLEL-QUEUE node confirms that the first PARALLEL-QUEUE node scanned partitions P1, P3, and P4, and the second PARALLEL-QUEUE node scanned partition P2.
Example1#
iSQL> SELECT /*+ PARALLEL(t1 2) */ COUNT(i1) FROM t1;
COUNT(I1)
-----------------------
500
1 row selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 8, COST: 0.56 )
GROUP-AGGREGATION ( ITEM_SIZE: 24, GROUP_COUNT: 1, BUCKET_COUNT: 1, ACCESS: 1, COST: 0.56 )
PARTITION-COORDINATOR ( TABLE: T1, PARALLEL, PARTITION: 4/4, ACCESS: 500, COST: 0.28 )
PARALLEL-QUEUE ( TID: 1 )
PARALLEL-QUEUE ( TID: 2 )
SCAN ( PARTITION: P4, FULL SCAN, ACCESS: 201, TID: 1, COST: 0.11 )
SCAN ( PARTITION: P3, FULL SCAN, ACCESS: 100, TID: 1, COST: 0.06 )
SCAN ( PARTITION: P2, FULL SCAN, ACCESS: 100, TID: 2, COST: 0.06 )
SCAN ( PARTITION: P1, FULL SCAN, ACCESS: 99, TID: 1, COST: 0.06 )
------------------------------------------------------------
Example2#
iSQL> SELECT l_returnflag
, l_linestatus
, SUM(l_quantity) AS sum_qty
, SUM(l_extendedprice) AS sum_base_price
, SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price
, SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge
, AVG(l_quantity) AS avg_qty
, AVG(l_extendedprice) AS avg_price
, AVG(l_discount) AS avg_disc
, COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= DATE'01-DEC-1998' - INTERVAL'90'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;
L_RETURNFLAG L_LINESTATUS SUM_QTY SUM_BASE_PRICE SUM_DISC_PRICE SUM_CHARGE AVG_QTY AVG_PRICE AVG_DISC COUNT_ORDER
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A F 37474 56250004.9 53399385.5912999 55528109.501472 25.3545331529093 38058.1900405954 0.050866035 1478
N F 1041 1614635.42 1549641.9636 1608284.73514 27.3947368421053 42490.4057894737 0.042894737 38
N O 75067 112774708 107204872.3929 111467221.663635 25.5590738849166 38397.9257643854 0.049673136 2937
R F 36470 54082045.8 51384368.3297 53497960.192278 25.0480769230769 37144.2622115385 0.050006868 1456
4 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 10, TUPLE_SIZE: 120, COST: 97.21 )
SORT ( ITEM_SIZE: 24, ITEM_COUNT: 4, ACCESS: 4, COST: 95.85 )
GROUP-AGGREGATION ( ITEM_SIZE: 192, GROUP_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 4, COST: 95.83 )
PARALLEL-QUEUE ( TID: 1 )
SCAN ( TABLE: LINEITEM, FULL SCAN, ACCESS: 6000, TID: 1, COST: 60.33 )
------------------------------------------------------------
PARALLEL-SCAN-COORDINATOR#
Format#
PARALLE-SCAN-COORDINATOR( TABLE: table_name, ACCESS: acc_num )
| Item | Description |
|---|---|
| TABLE | The name of the table being accessed |
| ACCESS | The number of times a record was accessed |
Description#
PARALLEL-SCAN-COORDINATOR node is used to execute parallel query. After executing several SCAN nodes in parallel, the result is collected and transferred to the upper node.
The following example illustrates the use of the PARALLEL-SCAN-COORDINATOR node: Run the child nodes in parallel, collect the results, and pass them on to the parent node.
Example#
iSQL> SELECT /*+ PARALLEL(LINEITEM, 4) */
l_returnflag
, l_linestatus
, SUM(l_quantity) AS sum_qty
FROM lineitem
WHERE l_shipdate <= DATE'01-DEC-1998' - INTERVAL'90'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;
L_RETURNFLAG L_LINESTATUS SUM_QTY
--------------------------------------------
A F 37474
N F 1041
N O 75067
R F 36470
4 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 30, COST: 96.02 )
SORT ( ITEM_SIZE: 24, ITEM_COUNT: 4, ACCESS: 4, COST: 95.90 )
GROUP-AGGREGATION ( ITEM_SIZE: 48, GROUP_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 4, COST: 95.83 )
PARALLEL-SCAN-COORDINATOR ( TABLE: SYS.LINEITEM, ACCESS: 5909 )
PARALLEL-QUEUE ( TID: 1 )
SCAN ( TABLE: SYS.LINEITEM, FULL SCAN, ACCESS: 1569, TID: 1, COST: 60.33 )
PARALLEL-QUEUE ( TID: 2 )
SCAN ( TABLE: SYS.LINEITEM, FULL SCAN, ACCESS: 1413, TID: 2, COST: 60.33 )
PARALLEL-QUEUE ( TID: 3 )
SCAN ( TABLE: SYS.LINEITEM, FULL SCAN, ACCESS: 1570, TID: 3, COST: 60.33 )
PARALLEL-QUEUE ( TID: 4 )
SCAN ( TABLE: SYS.LINEITEM, FULL SCAN, ACCESS: 1448, TID: 4, COST: 60.33 )
------------------------------------------------------------
PARTITION-COORDINATOR#
Format#
PARTITION-COORDINATOR( TABLE: table_name, PARALLEL, PARTITION: partition_acc_cnt, ACCESS: acc_num, COST: cost )
[Table 4-15] Information of the PARTITION-COORDINATOR Node
| Item | Description |
|---|---|
| TABLE | The name of the table to be accessed |
| PARALLEL | Whether or not to execute parallel queries. Only output in parallel query |
| PARTITION | The number of partitions to be accessed |
| ACCESS | The number of times that records were accessed |
| COST | The estimated cost |
Description#
The PARTITION-COORDINATOR node manages the scanning of each partition in a partitioned table. It has many child nodes and performs partition filtering.
The following example shows how this node is executed. The scan results of a partition (its child node) is passed to the higher node.
The following example shows the execution of a PCRD node through a parallel query. The PARALLEL item can be seen on the PCRD node. The PRLQ node passes the execution results of the child node scanning the partition to a higher (parent) node.
Example 1#
iSQL> CREATE TABLE t1 ( i1 INTEGER )
PARTITION BY RANGE ( i1 )
(
PARTITION p1 VALUES LESS THAN ( 100 ),
PARTITION p2 VALUES LESS THAN ( 200 ),
PARTITION p3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> INSERT INTO t1 VALUES ( 50 );
1 row inserted.
iSQL> INSERT INTO t1 VALUES ( 60 );
1 row inserted.
iSQL> INSERT INTO t1 VALUES ( 150 );
1 row inserted.
iSQL> INSERT INTO t1 VALUES ( 160 );
1 row inserted.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
Alter success.
iSQL> SELECT COUNT(*) FROM t1 WHERE i1 < 100;
COUNT
-----------------------
2
1 row selected.
--------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 8, COST: 14.02 )
GROUP-AGGREGATION ( ITEM_SIZE: 24, GROUP_COUNT: 1, BUCKET_COUNT: 1, ACCESS: 1, COST: 14.02 )
PARTITION-COORDINATOR ( TABLE: T1, PARTITION: 1/3, ACCESS: 2, COST: 14.01 )
SCAN ( PARTITION: P1, FULL SCAN, ACCESS: 2, DISK_PAGE_COUNT: 64, COST: 14.01 )
--------------------------------------------------------
Example 2#
iSQL> SELECT /*+ PARALLEL(t1 3) */ COUNT(*) FROM t1 WHERE i1 < 160;
COUNT
-----------------------
3
1 row selected.
--------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 8, COST: 14.02 )
GROUP-AGGREGATION ( ITEM_SIZE: 24, GROUP_COUNT: 1, BUCKET_COUNT: 1, ACCESS: 1, COST: 14.02 )
PARTITION-COORDINATOR ( TABLE: T1, PARALLEL, PARTITION: 2/3, ACCESS: 2, COST: 14.01 )
PARALLEL-QUEUE ( TID: 1 )
PARALLEL-QUEUE ( TID: 2 )
SCAN ( PARTITION: P1, FULL SCAN, ACCESS: 2, TID: 1, DISK_PAGE_COUNT: 64, COST: 14.01 )
SCAN ( PARTITION: P2, FULL SCAN, ACCESS: 2, TID: 2, DISK_PAGE_COUNT: 64, COST: 14.01 )
--------------------------------------------------------
PROJECT#
Format#
PROJECT ( COLUMN_COUNT: col_count, TUPLE_SIZE: tuple_size, COST: cost )
| Item | Description |
|---|---|
| COLUMN_COUNT | The number of projection columns |
| TUPLE_SIZE | The size of the record extracted in a projection |
| COST | The estimated cost |
Description#
The PROJECT node is the physical entity that performs project operations in the relational model. It has one child node and extracts only the necessary columns from the result set returned by the child node
Outputting the PROJECT Node#
The PROJECT node comprises the final result of a query. Information of the PROJECT node can be viewed within the plan tree as shown below. The query result is composed of two columns and the size of each returned record is 8 bytes.
Example#
Output the name and salary of all employees when a 10% raise is given to all employees.
iSQL> SELECT e_firstname, e_lastname, salary * 1.1 FROM employees;
E_FIRSTNAME E_LASTNAME SALARY * 1.1
-------------------------------------------------------------
.
.
.
20 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 67, COST: 0.41 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.01 )
------------------------------------------------------------
SCAN#
Format#
When intermediate results are cached in memory#
SCAN ( TABLE: table_name, FULL SCAN, ACCESS: acc_num, TID: tid, COST: cost )
SCAN ( TABLE: table_name, INDEX: index_name, ACCESS: acc_num, TID: tid, COST: cost )
When intermediate results are stored on disk#
SCAN ( TABLE: table_name, FULL SCAN, ACCESS: acc_num, DISK_PAGE_COUNT: page_count, TID: tid, COST: cost )
SCAN ( TABLE: table_name, INDEX: index_name, ACCESS: acc_num, DISK_PAGE_COUNT: page_count, TID: tid, COST: cost )
[Table 4-17] Information of the SCAN Node
| Item | Description |
|---|---|
| TABLE | The name of the table being accessed |
| INDEX | The name of the index being used |
| SCAN Method | FULL SCAN, RANGE SCAN [DESC] or RID SCAN |
| ACCESS | The number of times the records were accessed |
| DISK_PAGE_COUNT | The number of disk pages in a table |
| TID | Thread identification number. Is only output for parallel queries. |
| COST | The estimated cost |
Description#
The SCAN node is the physical entity that performs SELECT operations in the relational model. It does not have a child node and fetches records by directly accessing the table.
Results fetched from the SCAN node are processed in the following five methods.
-
Fixed key range
-
Variable key range
-
Constant filter
-
Filter
-
Subquery filter
Memory Tables and Disk Tables#
Memory tables and disk tables output slightly different information. Only disk tables output the number of disk pages it has.
The output information of the SCAN node of memory and disk tables are compared below.
In the following example, the user default tablespace is stored as SYS_TBS_MEMORY and information of the SCAN node in the memory table is displayed.
In the following example, information of the SCAN node in the table created in the SYS_TBS_DATA disk tablespace is output. As shown below, disk tables display the number of disk pages a table possesses.
Table Name#
As shown in the following example, the name of the table being accessed by the SCAN node and the alias also (if the alias is specified in the query) is output
The Access Method and Number of Times the Records were Accessed#
The most important information in query tuning is whether a full scan is performed or an index is used, along with the estimate of how many records will be accessed. The greater the number of records that are accessed, the lower the performance, so the estimate of the number of records that will be accessed is critical.
The example below shows the case where a query is processed using a full scan. The example shows the number of records accessed in order to perform the comparison in the WHERE clause.
The next example shows the execution plan information that is displayed when an index is used to access the same table. The example shows that when IDX1 is used, only one record is accessed in order to evaluate the condition in the WHERE clause.
It can be seen that the same execution plan is created to evaluate this condition even when a condition pertaining to another column is added.
If an index is added to the t1.i2 column and a query is then executed using that index, the execution plan shown below will result. It can be seen that using the index for the T1.i2 column degrades efficiency, even though the query is the same.
When no hints are given, as in the example shown below, the optimizer chooses the best index to use on the basis of a cost estimation
As illustrated above, it is necessary for the user to view the SCAN node information to check whether a suitable access method was chosen, and, if necessary, to create an index that is appropriate for the query when no such index exists.
TRCLOG_DETAIL_PREDICATE#
If the TRCLOG_DETAIL_PREDICATE property is set to 1, information about how the conditions are processed by the SCAN node is output. This property is useful when checking whether a condition is using an index.
In the following example, the method that is used to process each condition is shown. The (i1 = 1000) condition is processed as a fixed key range that uses an index, and the (i2 = 0) condition is processed as a filter.
In the following example, a different index is used to process the same query. It can be seen that when the IDX2 condition is used, the condition that is processed using the index and the condition that is processed without using an index are switched.
As seen above, determining whether a condition in a WHERE clause is processed using an index is of great help when tuning queries. However, this information might not be output if the query is changed by the optimizer during the course of optimization.
Example#
Example1) Output the name, department number, birthday of all employees born before January 1st, 1980.
iSQL> SELECT e_firstname
, e_lastname
, dno
, birth
FROM employees
WHERE birth > '800101';
E_FIRSTNAME E_LASTNAME DNO BIRTH
---------------------------------------------------------------------
Aaron Foster 3001 820730
Gottlieb Fleischer 4002 840417
Xiong Wang 4001 810726
Sandra Hammond 4002 810211
Mitch Jones 1002 801102
Jason Davenport 1003 901212
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 54, COST: 0.26 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 )
------------------------------------------------------------
Example2) Output the name, department number, birthday of all employees born before January 1st, 1980 (Use the index).
iSQL> CREATE INDEX emp_idx2 ON employees(birth);
Create success.
iSQL> SELECT e_firstname
, e_lastname
, dno
, birth
FROM employees
WHERE birth > '800101';
E_FIRSTNAME E_LASTNAME DNO BIRTH
---------------------------------------------------------------------
Mitch Jones 1002 801102
Sandra Hammond 4002 810211
Xiong Wang 4001 810726
Aaron Foster 3001 820730
Gottlieb Fleischer 4002 840417
Jason Davenport 1003 901212
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 54, COST: 0.11 )
SCAN ( TABLE: EMPLOYEES, INDEX: EMP_IDX2, RANGE SCAN, ACCESS: 6, COST: 0.00 )
------------------------------------------------------------
VIEW#
Format#
VIEW ( view_name, ACCESS: acc_num, COST: cost )
[Table 4-18] Information of the VIEW Node
| Item | Description |
|---|---|
| View Name | The view name. This is output when it has a name. |
| ACCESS | The number of times the view records were accessed |
| COST | The estimated cost |
Description#
VIEW nodes are used to display virtual tables according to the relational model. This node is used to display a user-defined view or make a result set created by a set operation appear as a single table.
When a user-defined view is queried, the output for the VIEW node appears as shown below. The subnodes of the VIEW node illustrate the execution plan for the SELECT statement that forms the basis of the user-defined view.
VIEW nodes are also used for queries that use set operations and an example is provided below. A VIEW node is created in order to manage the results of an INTERSECT operation as a single table. In this case, the view does not have its own name.
Example#
Output the name, salary, department number and the average salary of that department for all employees who receive a higher salary than the average salary of their department.
iSQL> SELECT e.e_firstname
, e.e_lastname
, e.salary
, e.dno
, v1.salavg
FROM employees e
, (SELECT dno
, AVG(salary) salavg
FROM employees
GROUP BY dno) v1
WHERE e.dno = v1.dno
AND e.salary > v1.salavg;
E_FIRSTNAME E_LASTNAME SALARY DNO SALAVG
---------------------------------------------------------------------------
Wei-Wei Chen 2300 1001 2150
Ryu Momoi 1700 1002 1340
Elizabeth Bae 4000 1003 2438.25
Zhen Liu 2750 1003 2438.25
John Huxley 1900 4001 1550
Sandra Hammond 1890 4002 1396.66667
Alvar Marquez 1800 4002 1396.66667
7 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 5, TUPLE_SIZE: 79, COST: 0.40 )
JOIN ( METHOD: INDEX_NL, COST: 0.23 )
VIEW ( ACCESS: 9, COST: 0.22 )
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 25, COST: 0.21 )
AGGREGATION ( ITEM_SIZE: 72, GROUP_COUNT: 9, COST: 0.01 )
GROUPING
SCAN ( TABLE: EMPLOYEES, INDEX: EMP_IDX1, FULL SCAN, ACCESS: 20, COST: 0.01 )
SCAN ( TABLE: EMPLOYEES E, INDEX: EMP_IDX1, RANGE SCAN, ACCESS: 19, COST: 0.01 )
------------------------------------------------------------
VIEW-SCAN#
Format#
VIEW-SCAN ( VIEW: view_name, ACCESS: acc_num, COST: cost )
[Table 4-19] Information of the VIEW-SCAN Node
| Item | Description |
|---|---|
| VIEW | The view name |
| ACCESS | The number of times the view records were accessed |
| COST | The estimated cost |
Description#
The VIEW-SCAN node is the physical entity that performs SELECT operations on temporarily stored views according to the relational model. If it has a child node, it is always a materialization node.
This node is created during query optimization when it has been determined that it would be more efficient to store the contents of a view for processing.
The following is an example of a VIEW-SCAN node. In the example below, the same view is accessed by both the main query and the subquery. The optimizer concluded that it would be more efficient to temporarily store the contents of the view, and thus they were stored using the materialization node. The execution plan shows that the VIEW-SCAN node accesses the contents of the view.
In the above execution plan, the VIEW-SCAN node for the (V1 X) view does not seem to have any child nodes. However, this VIEW-SCAN node has a materialized node (displayed as "MATERIALIZATION") as its child node. Part of the above execution plan is shown diagrammatically below (VSCN: VIEW-SCAN node, VMTR: MATERIALIZATION node).
As shown in the above figure, the VSCN (V1 X) and VSCN (V1 Y) execution nodes have the same child nodes.
Example#
Please refer to the example for the MATERIALIZATION node.
SET-DIFFERENCE#
Format#
When intermediate results are cached in memory#
SET-DIFFERENCE ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, BUCKET_COUNT: bucket_count, ACCESS: acc_num, COST: cost )
When intermediate results are stored on disk#
SET-DIFFERENCE ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-20] Information of the SET-DIFFERENCE Node
| Item | Description |
|---|---|
| ITEM_SIZE | The size of the stored record for the difference set |
| ITEM_COUNT | The number of stored records |
| BUCKET_COUNT | The number of hash buckets |
| DISK_PAGE_COUNT | The number of disk pages that the temporary table contains |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The SET-DIFFERENCE node performs MINUS operations according to the relational model. This node has two child nodes, and stores, and processes intermediate results in order to obtain a difference set.
The example below shows the use of an SDIF node to process a MINUS clause. After eliminating duplicate records from the results returned by the query on the left, the node searches for records that do not intersect with the data returned by the query on the right.
Example#
Output the goods numbers of the goods that have no orders.
iSQL> SELECT gno FROM goods
MINUS
SELECT gno FROM orders;
GNO
--------------
A111100001
B111100001
C111100002
E111100011
D111100001
D111100005
D111100006
D111100007
D111100009
E111100003
E111100004
E111100005
E111100006
E111100008
14 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 12, COST: 0.16 )
VIEW ( ACCESS: 14, COST: 0.13 )
SET-DIFFERENCE ( ITEM_SIZE: 32, ITEM_COUNT: 30, BUCKET_COUNT: 1024, ACCESS: 14, COST: 0.13 )
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 12, COST: 0.07 )
SCAN ( TABLE: GOODS, FULL SCAN, ACCESS: 30, COST: 0.02 )
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 12, COST: 0.07 )
SCAN ( TABLE: ORDERS, FULL SCAN, ACCESS: 30, COST: 0.02 )
------------------------------------------------------------
SET-INTERSECT#
Format#
When intermediate results are cached in memory#
SET-INTERSECT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, BUCKET_COUNT: bucket_count, ACCESS: acc_num, COST: cost )
When intermediate results are stored on disk#
SET-INTERSECT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-21] Information of the SET-INTERSECT Node
| Item | Description |
|---|---|
| ITEM_SIZE | The size of the stored records for the intersection set |
| ITEM_COUNT | The number of stored records |
| BUCKET_COUNT | The number of hash buckets |
| DISK_PAGE_COUNT | The number of disk pages the temporary table contains |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The SET-INTERSECT node performs intersect operations according to the relational model. This is a binary node and stores and processes intermediate results in order to obtain an intersection set.
In the following example, the SET-INTERSECT node is used to execute an INTERSECT operation. The data returned from the query on the left is eliminated of duplicate values and stored; using the data returned from the query on the right, data for the intersection set is searched for.
Example#
Output the list of items that have been ordered at least once from the goods table.
iSQL> SELECT gno FROM goods INTERSECT SELECT gno FROM orders;
GNO
--------------
A111100002
E111100001
D111100008
D111100004
C111100001
E111100002
D111100002
D111100011
D111100003
D111100010
E111100012
F111100001
E111100009
E111100010
E111100007
E111100013
16 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 12, COST: 0.16 )
VIEW ( ACCESS: 16, COST: 0.13 )
SET-INTERSECT ( ITEM_SIZE: 32, ITEM_COUNT: 30, BUCKET_COUNT: 1024, ACCESS: 16, COST: 0.13 )
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 12, COST: 0.07 )
SCAN ( TABLE: GOODS, FULL SCAN, ACCESS: 30, COST: 0.02 )
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 12, COST: 0.07 )
SCAN ( TABLE: ORDERS, FULL SCAN, ACCESS: 30, COST: 0.02 )
------------------------------------------------------------
SORT#
Format#
When intermediate results are cached in memory#
SORT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, ACCESS: acc_num, COST: cost )
When intermediate results are stored on disk#
SORT ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-23] Information of the SORT Node
| Item | Description |
|---|---|
| ITEM_SIZE | The size of the records for sorting |
| ITEM_COUNT | The number of records to be sorted |
| DISK_PAGE_COUNT | The number of disk pages that the temporary table contains |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The SORT node is a physical entity that performs SORT operations according to the relational model. This is a unary node and uses temporary space to store intermediate results.
The SORT node is used for various purposes. A description of its plan trees for different purposes is given below.
Used for the ORDER BY Clause#
The SORT node is used when an ORDER BY clause exists and sorting is necessary. In the following example, the SORT node is used to process the ORDER BY clause.
Used for GROUP BY#
The SORT node can be created to sort the same groups of the GROUP BY clause. In the following example, the SORT node is created to process GROUP BY i4
Used for the DISTINCT Clause#
The SORT node can be used to eliminate duplicate values by performing a SORT operation to execute DISTINCT. In the following example, the SORT node has been created to process DISTINCT i4.
Used for Joins#
The SORT node can be used to perform the join operations.
In the following example, the SORT node has been created to perform a join operation. The SORT node is created to perform a sort-based join to check the T1.i1 = T2.i1 join condition.
Example#
Order by salary the name, job, entry date and salary of employees whose salary is $1500 USD or less.
iSQL> SELECT e_firstname
, e_lastname
, emp_job
, salary
FROM employees
WHERE salary < 1500
ORDER BY 4 DESC;
E_FIRSTNAME E_LASTNAME EMP_JOB SALARY
----------------------------------------------------------------------------
Takahiro Fubuki PM 1400
Curtis Diaz planner 1200
Jason Davenport webmaster 1000
Mitch Jones PM 980
Gottlieb Fleischer manager 500
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 70, COST: 0.33 )
SORT ( ITEM_SIZE: 16, ITEM_COUNT: 5, ACCESS: 5, COST: 0.22 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 )
------------------------------------------------------------
STORE#
Format#
STORE ( ITEM_SIZE: item_size, ITEM_COUNT: item_count, DISK_PAGE_COUNT: page_count, ACCESS: acc_num, COST: cost )
[Table 4-23] Information of the STORE Node
| Item | Description |
|---|---|
| ITEM_SIZE | The size of the stored records |
| ITEM_COUNT | The number of stored records |
| DISK_PAGE_COUNT | The number of disk pages that the temporary table contains. No information is provided for memory tables. |
| ACCESS | The number of times the stored records were accessed |
| COST | The estimated cost |
Description#
The STORE node temporarily stores some of the query results. This is a unary node and uses a temporary table to save intermediate results.
STORE nodes are used for various purposes. The following is a description of the execution plan tree for each purpose.
Used for Joins#
STORE nodes can be used to process joins.
A STORE node is usually used in cases involving Cartesian products in which there are no join conditions. If this node is used to process joins, it does not itself process the join conditions.
The example below shows the use of a STORE node for a Cartesian product. The node stores the returned results in the t1 table to prevent the redundant use of the index.
