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

STAR T

P,C

CNO CNO

CUST INVOICE

Figure 8.1 Customer
outer table.

1,000,000 rows 20,000,000 rows

same customer number in the second SELECT statement, 20 rows being fetched
(on average). Likewise the QUBE would be the same, (except for the number of
FETCH calls).

Example 8.2: Invoice Outer Table

SQL 8.2

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

This is another simple example of accessing columns from two different
tables (Fig. 8.2), but this time the invoice date index IDATE will be the starting
point as there is no customer information provided in the query. Assuming only
the first 20 invoices are required, these will be accessed both to pick up the two
columns required and to obtain the customer number of each invoice for the
access to the customer table, now the inner table, via the CNO index. The access
to the customer table will, as before, be via the primary index, one customer row
for each invoice.
It is quite possible that each invoice will be for a different customer and so
there will be quite a large number of random touches. The response time for this