|
|
|
Subselects are implemented for both non-correlated and correlated queries.
A non-correlated query (select) is one where the search values for the tests in the query are either constant (for this scan-they could be supplied from user variables when the scan statement was executed, but they are constant for the duration of the statement), or are other field values in the same record (the previously supported field to field comparison).
In this case, each query (select) or subquery (subselect) is logically executed once only, and the result used as input to a higher-level query (select) or as the result of the scan.
A correlated query (select) is one where at least one field to field test is done in the expression, but the right-hand-side field is qualified by a correlation-id that is not the correlation-id of the current SELECT, but is a correlation-id of a parent (higher level) SELECT. In this case, each time the correlated subselect result is needed (it cannot be the primary select as it has no parent), the entire subselect (and possibly sub-subselects) will be reevaluated, using as test arguments the current values of the relevant fields of the currently considered parent record. A nested loop results.
For purposes of deciding on use of keys, a correlated test can use keys, as at the time the subselect is executed the supplied search values (parent field values) are constant.
The fields referenced in the scan need not be keyed. The scan processing logic will use keys wherever possible, but will automatically switch to reading records whenever a non-keyed field is referenced. The only penalty is the number of I/Os, and the elapsed time.
| Copyright © 2009 CA. All rights reserved. |
|