Database Reference
In-Depth Information
MERGE SCAN JOINS AND HASH JOINS
When we analyzed both program A and program B in our case study, we discov-
ered that the benefits to be gained by using ideal indexes were limited because
of the huge number of TRs to the inner table index. One of the main advantages
of a merge scan join or a hash join is that this problem is avoided.
Merge Scan Join
Merge scan joins take place as follows:
ž Table scans or index scans are carried out to find all the rows that satisfy
the local predicates.
ž Possible sorts, if these scans do not provide the results in the required
sequence.
ž A merge of the temporary tables created in the previous two steps.
A merge scan join is often faster than a nested-loop join in the following cases:
1. A join column index is not available. In this case, the inner table might
have to be scanned several times with a nested-loop join. In real life
it is unlikely that a join column index does not exist because most
join predicates are based on primary key
=
foreign key as in our case
INVOICE.CNO.
2. The result table is very large. In this case a nested-loop join might cause
the same page to be accessed over and over again.
3. There are local predicates with low filter factors to more than one table.
As we have seen, nested loop may then result in too many random touches
to the inner table (or index).
Before using the case study to compare a nested-loop join with a merge scan
join, we will first take a simple example to show what steps need to take place,
and how we may calculate the LRT using the QUBE.
study—CUST.CNO
=
Example 8.3: Merge Scan Join
SQL 8.14
DECLARE CURSOR81 CURSOR FOR
SELECT
CNAME, CTYPE, INO, IEUR
FROM
CUST, INVOICE
WHERE
CUST.CTYPE = :CTYPE
AND
IDATE > :IDATE
AND
CUST.CNO = INVOICE.CNO
Search WWH ::




Custom Search