SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-15
Cost of Join Operations
The cost of a sort depends on the type of sort required. In general, cost items include:
•
I/O cost to insert data into entry-sequenced file
•
Scratch file cost
•
Compare cost
•
Temporary file creation
•
Miscellaneous message and setup costs
For an in-memory user process sort, only the compare cost is necessary.
Sort costs for an ORDER BY are based on the number of rows going into the sort. If
the ORDER BY is preceded by a hashed grouping operation, the number of rows for
the sort is based on the data reduction expected by the grouping operation.
Parallel sort estimates calculate cost based on the number of rows, divided by the
number of repartitions performing the sorting, including start costs for sort and ESP
processes as well as interprocess messages and gains from parallel execution.
Cost of Join Operations
When estimating the cost of performing a join, SQL computes the cost of accessing
each of the tables involved in the join. The cost of accessing each table is computed in
the same way as in single-table queries, except that predicates must be identified as
associated with a particular table. This approach is necessary because some join
predicates cannot be evaluated until a qualifying row for an outer table has been
retrieved.
Consider this query:
SELECT EMP_NAME, DEPT_NAME, SALARY
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPT_NUM = DEPT.DEPT_NUM
AND DEPT_NUM < 100
If the DEPT table is the outer table of the join, this predicate can be used to scan
DEPT:
DEPT_NUM < 100
Thus, the predicate is involved in the computation of the cost of accessing DEPT.
The next predicate cannot be used in the cost computation for DEPT because no row
has been retrieved for EMPLOYEE:
EMPLOYEE.DEPT_NUM = DEPT.DEPT_NUM
This predicate, however, could be used in the computation of the cost of accessing
EMPLOYEE.