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

Example 8.1: Customer Outer Table

SQL 8.1

DECLARE CURSOR81 CURSOR FOR
SELECT CNAME, CTYPE, INO, IEUR
FROM CUST, INVOICE
WHERE CUST.CNO = :CNO
AND
CUST.CNO = INVOICE.CNO
WE WANT 20 ROWS PLEASE

This is a very simple example of accessing columns from two different
tables (Fig. 8.1). The starting point is the primary key via the primary index
CNO, and so only a single row from the customer table will be accessed to
obtain two columns.
The join predicate shows that the customer number is also to be used to
access the Invoice table (the foreign key in this case) using the foreign index
CNO, where two further columns are obtained. Each customer will have on
average 20 invoices (1 million customer rows and 20 million invoice rows), but,
of course, some will have more.
We are assuming the CNO index on the invoice table is not the cluster-

ing index.
The QUBE (based on a 20-row result set) would show a very respectable
response time as follows:

Index CNO TR = 1

Table CUST TR = 1

Index CNO TR = 1 TS = 20

Table INVOICE TR = 20

Fetch 20 × 0.1 ms

LRT TR=23 TS=20

23 × 10 ms 20 × 0.01 ms
230ms+0.2ms+2ms=232ms

There is no question that the outer table of the join (i.e., the starting point)
will be the customer table; the WHERE clause contains the primary key so the
DBMS will know that only a single row will be required, and therefore a single
scan of the inner table. There are no local predicates for the invoice table and so
this table must be the inner table.
This same process would also take place where two separate SELECT state-
ments were used instead of a single join statement. The first would use the
primary key on the customer table; the second would use a cursor to use this