Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 157

SQL 8.13

Program C+ First transaction

DECLARE CURSORJ CURSOR FOR
SELECT CNAME, CTYPE, INO, IEUR

FROM CUST, INVOICE

WHERE IEUR > :IEUR

AND
CCTRY = :CCTRY
AND
CUST.CNO = INVOICE.CNO

ORDER BY IEUR DESC, INO

WE WANT 20 ROWS PLEASE

OPEN CURSORJ
FETCH CURSORJ while IEUR > :IEUR and CCTRY =
:CCTRY, max 20

CLOSE CURSORJ

SAVE / INSERT IEUR, INO the values of the last

line displayed to user

potentially huge number of random touches to an inner table (or its index); the
index slices of customers who have at least one large invoice are not adjacent in
the index (CCTRY, CNO, CNAME, CTYPE).
In Chapter 5, we introduced the Basic Question, BQ: Is there an existing or
planned index that contains all the columns referenced by the WHERE clause?
We observed that:
According to our experience, a significant proportion of index problems
encountered after cutover would have been detected early by considering BQ.
Applying BQ to single-table SELECTs is straightforward. A join on the other
hand must be mentally broken down into several single-table cursors before the
BQ can be applied. This is a much more complex process, one that we shall
consider in great detail in Chapter 8.
The reasoning behind BQ, of course, was to ensure, probably using index
screening, that we only access a table when we know that the table rows are
required. We can now extend this argument to include the inner table or its index
in a nested-loop join—that is, we only access an inner table or index when we
know that the table or index rows are required. Our basic join question ,BJQ,
therefore, becomes: Is there an existing or planned index that contains all the local
predicate columns? This includes the local predicates for all the tables involved.
Here, the only way to eliminate the problem of excessive random touches
to the inner table, and thereby its index, is to add redundant table columns in
such a way that one table contains all the local predicates. Here, it suffices to
add column CCTRY to table INVOICE, replacing predicate CCTRY

=

:CCTRY

by INVOICE.CCTRY :CCTRY. Now it is possible to create an index that

contains all the local predicate columns, as shown in Figure 8.11.

=