IBM @server iSeries, User Manual

Page: 251 / 294

Option 2—Matched records with primary file
A record from the primary file is selected regardless of whether there is a match with any of the
secondary files. The selection of primary records is dependent on the select/omit criteria specified
on the Select Records display. Only primary records that meet those criteria are selected.
Select/omit criteria specified against secondary files may cause a record from the primary file not
to be selected.
Option 3—Unmatched records with primary file
A record from the primary file is selected only if there are no-matches with all of the secondary
files.
For option 2 and option 3 joins, which file is listed as the primary and which as the secondary is important
to the end result produced by the query, since ordering these files differently can produce different results.
However, the order is not important for option 1 joins since the same result occurs regardless of the order
in which files are placed.
This difference is important when considering how Query performs a join. Since the order of the files in an
option 1 join is not important, running this type of query may result in Query choosing a different ordering
of the files to gain better performance at run time. For example, if a query defines file A as the primary and
file B as the secondary, at run time Query may actually decide to use file B as the primary if it provides
better overall performance for the query. In options 2 and 3, however, Query cannot rearrange the order of
the files since this can produce different results, so the primary and secondary files always remain as
listed in the query definition.
Note: Although Query may choose to rearrange the order of the files at run time for an option 1 join,
Query never alters the actual query definition.
Performance tips for join operations in Query for iSeries
For all join operations, Query requires the use of an access path over each of the secondary files in the
join. If no usable access paths exist, Query builds them as needed. For this reason, if a particular join
query is run often or if several join queries use the same sort or join selection tests, consider building
access paths that match these values so Query does not have to build them each time you run the
queries.
Note: Query does not require an access path on the primary file unless there are sort fields selected from
this file.
It is important to build access paths to match join selection tests you use often. The access path or paths
should match the fields selected from the secondary files. For example, if the join selection test is
T01.A
EQ T02.A
, an access path is required over
T02.A
. For an option 1 join, Query may decide to switch the
order of the files and also internally switch the order of the join selection to match the new order. In this
case, the previously created access path may not be used. For an option 2 or 3 join, however, Query does
not switch the order of the files, so existing access paths that match the join selection tests on the
secondary file should be usable for the join.
Use as many record selection and join selection tests as possible on all files to be joined to narrow down
the number of records that will result from the join operation. This significantly reduces the amount of I/O
required to run the query.
If possible, limit using *ALL on the Specify How to Join Files display. If *ALL is used, the number of joined
records produced could be large. For example, if you use *ALL to join a file containing 2000 records with a
file of 3000 records, the end result would be 6000000 joined records. A large amount of I/O would be
required, resulting in a long response time and some degradation in overall system performance.
Appendix C. Query for iSeries performance tips and techniques
239