Professor DePratti examined the following query and db catalog information and correctly predicted that the optimizer would use a Merge Scan Join.
Explain the difference between a Merge Scan Join algorithm and a Nested Loop Join algorithm, and why in this case it chose the former.
Select Customer_Id, Order_Id, Order_Date
From CSC342.Customer C, CSC342.Factory_Order FO
Where C.Customer_Id = FO.Customer_Id
and C.Customer_Id between 1345621 and 2112345;
Problem assumptions:
- There is a Customer_Id PK index on CSC342.Customer.
- The CSC342.Customer table is a heap file.
- There is an Order_Id PK index on CSC342.Factory_Order.
- The CSC342.Factory_Order table is a heap file.