Figure 73 shows the chosen query plan for the distributed query.
QUERY:
------
select l.customer_num, l.lname, l.company,
l.phone, r.call_dtime, r.call_descr
from customer l, vjp_stores@gilroy:cust_calls r
where l.customer_num = r.customer_num
Estimated Cost: 9
Estimated # of Rows Returned: 7
1) informix.r: REMOTE PATH
2) informix.l: INDEX PATH
(1) Index Keys: customer_num (Serial, fragments: ALL)
Lower Index Filter: informix.l.customer_num = informix.r.customer_num
NESTED LOOP JOINThe following table shows the main differences between the chosen query plans for the distributed join and the local join.
| Output Line in Figure 73 for
Distributed Query |
Output Line in Figure 64 for
Local-Only Query |
Description of Difference |
|---|---|---|
| vjp_stores@gilroy:
virginia.cust_calls |
informix.cust_calls | The remote table name is prefaced with the database and server names. |
| Estimated Cost: 9 | Estimated Cost: 7 | The optimizer estimates a higher cost for the distributed query. |
| informix.r: REMOTE PATH | informix.r: SEQUENTIAL SCAN | The optimizer chose to keep the outer, remote cust_calls table at the remote site. |
| select x0.call_dtime,x0.call_descr,x0.
customer_num from vjp_stores:"virginia".cust_ calls x0 |
The SQL statement that the local database server sends to the remote site. The remote site reoptimizes this statement to choose the actual plan. |