SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-14
Cost of Subqueries
Cost of Subqueries
The cost of a subquery is estimated as if it were a query in its own right. The cost
equals the cost of the subquery, multiplied by the number of times it must be executed.
It is computed as the cost of index’, where index’ is the index chosen to execute the
subquery.
Cost also depends on whether a subquery is correlated or noncorrelated, as described
in Section 1, Retrieving Data: How to Write Queries. These paragraphs discuss costs
for both types of subqueries.
Correlated Subqueries
If a subquery cannot be executed independent from the outer query, the subquery is
executed for every qualifying row of the outer query. This query selects information on
items that cost more than the average price of the items produced by the same
producer:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY OUTER
WHERE RETAIL_PRICE > SELECT AVG(RETAIL_PRICE)
FROM INVNTRY
WHERE PRODUCER = OUTER.PRODUCER
The subquery in this example is dependent on the outer SELECT because it
references the PRODUCER column of a row retrieved for the outer SELECT.
This correlation forces an evaluation of the subquery for every row retrieved from the
outer SELECT. The overall query is more expensive to evaluate because of the
repeated evaluation of the subquery. If the INVNTRY table contains 100 rows, the cost
of evaluating the query is
COST(outer SELECT) + (100 x COST(inner SELECT) )
Noncorrelated Subqueries
A noncorrelated subquery can be evaluated before the outer query is executed.
Because a noncorrelated subquery is only evaluated once, the cost of evaluating the
original query is the sum of the cost of evaluating the individual SELECT statements.
The practical limit for nesting is the amount of compile-time and run time resources
(stack space, extended segment space).
The cost of a noncorrelated query is simply added to the cost of the overall plan.
Cost of Sorts
The cost of a sort is the estimated cost of sorting specified rows in a particular order.
(The sort might be initiated by an ORDER BY, DISTINCT, UNION, or GROUP BY
request or by the use of a sort merge join.) SQL supports three types of sorts:
in-memory sort, a sort to a key-sequenced file, and the FastSort process.