Database Reference
In-Depth Information
This is a huge reduction from the 17-min estimate for program A but, unfortu-
nately, the message is the same as the one we emphasized in program A—the
benefit of using an ideal index is minimized because of the huge number of TRs
to the inner table index.
ProgramC:OuterTableChosenbytheOptimizer
With the ideal indexes, the estimates we derived for programs A and B show
that if a nested-loop join is chosen by the optimizer, the INVOICE table should
be the outer table. Therefore, the required ideal indexes should be those shown
in Figure 8.8. The response time will be a little less than that for program B
because of the reduction in FETCH calls, although the reduction, as we have
seen, is minimal.
SQL Joins Compared to Program Joins It is often said that a join is more
efficient than a program with multiple cursors. Is this true?
The critical factor is the access path chosen, namely the join method and the
table access order . Sometimes one might choose a better access path than the
optimizer; sometimes vice versa. Ultimately, the access paths should be identical,
as with programs B and C above. The number of disk I/Os will then be the same;
the only difference will be the number of SQL calls. Using a join SELECT is
better in this respect as the figures shown above clearly indicate.
Because of the high number of random I/Os in programs A and B, the CPU
time is small compared to the overall elapsed time. There are cases, however,
in which the CPU time is the largest component of the local response time; a
program with a join cursor may then be noticeably faster than a program with
several single-table SELECTs.
Conclusion: Ideal Indexes
Even with three-star indexes, although reducing the worst input local response
time from 35 to 3.5 min, this is still far too high. The next step must be to
consider changing the program so that it builds only one screen, say 20 result
rows, per transaction. Please note that we have so far confined our attention to
the nested-loop join. Our conclusion so far may well be affected by what is yet to
be discussed .
Ideal Indexes with One Screen per Transaction
Materialized
ProgramB
:MaterializeOneScreenperTransaction
It is quite easy to modify program B so that it only issues 20 FETCH calls. We
simply need to
ž Ensure that the access path does not need to perform a sort.
+
Search WWH ::




Custom Search