IBM @server iSeries, User Manual

Page: 248 / 294

Note: If there is no existing access path matching some of the record selection tests, Query does not
build an access path solely for purposes of selection. Query reads each record and selects those
that qualify.
If you request a particular query often, consider creating an access path with select/omit tests to match
that query. See “Select/omit access paths in Query for iSeries” on page 232 for more information on this
subject.
Existing access paths are only used for OR conditions involving the same field specified in the selection
tests.
One type of record selection is to use the % symbol with the LIKE operator as a generic search or scan
(also known as a wildcard scan). If the generic scan starts in the first position of a field (for example,
%ABC), Query cannot use any existing access paths for that portion of the record selection. However, if
the generic scan starts after the first position (for example, ABC%), Query can use any qualifying access
paths over the field specified in this type of record selection.
Select sort fields in Query for iSeries
Query, in most cases, needs an access path to sequence the selected records when sort fields are
specified. If an access path does not exist, Query creates a temporary access path at run time or uses a
sort to order the records. A sort routine is used when the optimizer determines that the sort routine
provides better performance. If a temporary access path is used, it is deleted after the query has finished
running, so each run of the query requires another build of the access path. For this reason, always
consider whether you really need sort fields for the query.
Consider creating access paths that match the sort tests for queries that you use often and for queries
where the access path build time is excessively long. Query attempts to use an existing access path if all
the sort fields from the query match the high order key fields from the access path. This way you can
avoid excessive building of access paths for queries with sort tests.
As an example, assume file Z has fields A, B, C, and D. Also assume there are six access paths built over
this file that have the following keys specified in this order:
1. Access path #1 has key field A
2. Access path #2 has key fields A and B
3. Access path #3 has key fields A and C
4. Access path #4 has key fields A, B, and C
5. Access path #5 has key fields B, A, and C
6. Access path #6 has key fields A, B, C, and D
Now if you run a query that is defined to sort on key fields A, B, and C, only access paths #4 and #6 are
considered by Query during optimization. Access paths #1, #2, and #3 are not used because it is
inefficient for Query to read the records again and sort on the additional keys. It is more efficient for Query
to build and use an access path containing all the sort and selection tests. Access path #5 is not
considered because the sorted keys are not in the correct order.
If a particular query is requested often, consider creating an access path with select/omit tests to match
that query. See “Select/omit access paths in Query for iSeries” on page 232 for more information on this
subject.
If you have sort tests that you use often, another option (besides creating access paths) is to use Query to
sort the records in a database file in the desired order. Query can then be run against this file with no sort
tests, if the queries are looking for data sorted as it appears in the file. To perform this function, select the
desired sort fields from the file, choose database as the output device (option 3 on the Select Output Type
and Output Form display), and specify the output database file name, which must be different from the
236
Query for iSeries Use V5R2