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

CTYPE IDATE

CUST

MS/HJ

INVOICE

Figure 8.13 Merge scan
join example.

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

The choice of outer and inner table is less important now, although the terms
are still relevant. (See Fig. 8.13.) Each local predicate is used, in turn, to produce
temporary tables that contain only those rows that are required. Using 0.1% filter
factors for both local predicates, the QUBE for these accesses would be:

Step 1: Access Table CUST via Nonclustering Index CTYPE

Index CTYPE TR = 1 TS = 1000

Table CUST TR = 1000

LRT TR = 1000 TS = 1000

1000 × 10 ms 1000 × 0.01 ms
10s+0.01s=10s

A table scan would, at least according to the QUBE, also take 10 s but use more
CPU time.

Step 2: Access Table INVOICE via Nonclustering Index IDATE

Index IDATE TR = 1 TS = 20,000

Table INVOICE TR = 20,000

LRT TR = 20,000 TS = 20,000

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

A table scan would, at least according to the QUBE, also take 200 s but use
much more CPU time.

Step 3: Merge and FETCH the Result Rows As the rows are retrieved,
they will be moved into two temporary tables. The cost of doing so will be
easily absorbed within the cost of accessing the rows in the first place. Neither
temporary table will be in the join column sequence, and so they will have to be
sorted. The cost of the sort will be the usual figure of 0.01 ms per row. The two
tables will finally be merged, the cost of which can again use the same figure of
0.01 ms per row. Thus: