Database Reference
In-Depth Information
determine the LRT, however, the F parameter may be significant, depending on
the number of FETCH calls.
One of the activities that may have to take place for an accepted row is that
of sorting; the overall cost of this will usually be proportional to the number of
FETCHed rows. In most cases, this will be very small compared to the other
processing that takes place for the accepted rows and so will be “hidden” within
the F cost. There are occasions, however, where this will not be true, as we will
see in Chapter 8. Apart from these exceptions, we will assume the sort cost to
be included in the F parameter.
To avoid any confusion that might arise with regard to counting the number
of FETCH calls, please note that in our examples we will ignore the one that
causes “no more qualified rows” to be returned; this is purely to simplify the
process, so, for example, a filter factor of 1% on a table containing 10,000 rows
would expect to retrieve 100 rows—we would assume F
=
100, not 101.
QUBE Examples for the Main Access Types
Example5.1:PrimaryKeyIndexAccess
Index Columns
We have seen how important index matching and index screening are with regard to
performance. We have also discussed the use of an index to avoid the necessity of
a sort. In order to fully appreciate how index columns are being used in these three
ways in this chapter, ensure that you identify most carefully:
Matching columns
Screening columns
Sort avoidance columns
The local response times will be shown for each SQL example to enable compar-
isons to be easily made.
Although this is the simplest of examples, Figure 5.3 shows very clearly the
touches taking place to both the index and the table. The arrows on both sides
of the index show the index slice that has to be scanned (in this case a single
index row).
SQL 5.1
SELECT
CNO, LNAME, FNAME
FROM
CUST
WHERE
CNO = :CNO
Search WWH ::




Custom Search