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

In future, therefore, it will not be necessary to differentiate between merge
scan joins and hash joins, unless one wishes to use a reduced cost with regard to
the final step—the hash process itself. We will subsequently use the term MS/HJ
to refer to either process.
Let us now return to our case study and reconsider program C.

Program C: MS/HJ Considered by the Optimizer (Current
Indexes)

With our filter factors for the local predicates IEUR > :IEUR and CCTRY =
:CCTRY in CURSORJ, the optimizer may indeed choose the MS/HJ, which as
we saw with the nested-loop join figures, might not be a bad idea with the current
indexes (Fig. 8.14). In order to make comparisons somewhat easier to understand
against the nested-loop joins, we will assume that the optimizer uses the same
filter factors that we have been using.

Step 1: Full Table Scan on CUST

Table CUST TR = 1 TS = 1,000,000

1 × 10 ms 1,000,000 × 0.01 ms

LRT

10ms+10s=10s

SQL 8.15

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

Step 2: Full Table Scan on INVOICE

Table INVOICE TR = 1 TS = 20,000,000

1 × 10 ms 20,000,000 × 0.01 ms
10 ms + 200 s = 200 s

LRT