The following parameterized query can use this join index because the Optimizer knows that the matching rows are contained in the index because the WHERE clause predicate in the query is a conjunction between the covered term zipcode and the parameterized term :N.
USING (N VARCHAR(32)) SELECT pid, name FROM tp1 WHERE zipcode IN (54455, 53066) AND name = :N;
The explanation for this query looks like the following report:
Explanation
---------------------------------------------------------------------------
1) First, we lock CURT.TP1_JI for read on a
reserved RowHash to prevent global deadlock.
2) Next, we do an all-AMPs RETRIEVE step from CURT.TP1_JI by way of an
all-rows scan with a condition of ("((CURT.TP1_JI.zipcode = 53066 )
OR (CURT.TP1_JI.zipcode = 54455 )) AND (CURT.TP1_JI.name = :N)")
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with no confidence to be 1 row (
64 bytes). The estimated time for this step is 0.03 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.
Only prototyping can determine which is the better design for a given set of tables, applications, and hardware configuration.