Database Reference
In-Depth Information
As usual, when creating a new program, we should check whether the current
indexes are adequate, even with the worst input. If they aren't, we should design the
best affordable index, probably starting by designing and evaluating the ideal index.
To form an understanding of the actual difference in performance between
these three programs, let us compare the estimates, using the QUBE, to see how
they would perform with the current indexes shown in Figure 8.3.
We will make the estimates using the highest filter factors, assuming that
these represent the worst case.
FF (CCTRY = :CCTRY)
max 10%.
FF (IEUR > :IEUR)
max 0.1%
Current Indexes
The current indexes are shown in Figure 8.3. This diagram is shown again for
each of the three programs, together with the appropriate access path involved
and the touches required to each index and table.
ProgramA:OuterTableCUST
Step 1: Access Table CUST via Nonclustering Index CCTRY The DBMS
must find all the customers from the specified country. It can do this either by a
full table scan or by using the index CCTRY (Fig. 8.4). With a high filter factor for
the predicate CCTRY
:CCTRY, a full table scan will be faster; with a very low
filter factor, an index scan will be faster. If the optimizer selects the access path
only once for many executions , it may choose an index scan based on a filter factor
of 1% (one divided by the number of distinct values of the column CCTRY). If
the optimizer selects the access path every time based on actual user input, it will
undoubtedly choose a table scan as long as it is aware of the true filter factor.
=
SQL 8.6
DECLARE CURSORC CURSOR FOR
Program A
SELECT
CNO, CNAME, CTYPE
FROM
CUST
WHERE
CCTRY = :CCTRY
DECLARE CURSORI CURSOR FOR
SELECT
INO, IEUR
FROM
INVOICE
WHERE
IEUR > :IEUR
AND
CNO = :CNO
OPEN CURSORC
FETCH CURSORC
while
CCTRY = :CCTRY
OPEN CURSORI
FETCH CURSORI
while
IEUR > :IEUR
CLOSE CURSORI
CLOSE CURSORC
SORT THE RESULT ROWS BY IEUR DESC
Search WWH ::




Custom Search