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

ProgramC:OptimizerChoosesOuterTable

SQL 8.5

Program C

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

OPEN CURSORJ
FETCH CURSORJ while IEUR > :IEUR and CCTRY = :CCTRY

CLOSE CURSORJ

If only one of the tables had local predicates, starting with that table would
seem like a good idea from the performance point of view, as we saw in SQL
8.1 and 8.2. As both tables now have local predicates, it is not obvious which
program would be faster—unless we know how the tables are indexed.
For the purpose of this case study let us assume that, apart from the primary
and foreign key indexes, the only additional index is (CCTRY) on table CUST,
as shown in Figure 8.3. CCTRY is the code representing the country. Now most
readers would probably vote for program A; we appear to have an appropriate
index on the customer table but not on the invoice table.

FF(CCTRY = :CCTRY)
= 10%

P,C

CNO CCTRY

P F,C

INO CNO

CUST

1,000,000 rows

INVOICE

20 invoices
per average customer

20,000,000 rows

Figure 8.3 Current indexes for the case study.