The query plan of a distributed query

You can display the chosen query plan of a distributed query. The information displayed for a distributed join differs from information displayed for a local join.

The following figure shows the chosen query plan for the distributed query.

Figure 1. Selected Output of SET EXPLAIN ALL for Distributed Query, Part 3

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 JOIN

The following table shows the main differences between the chosen query plans for the distributed join and the local join.

Output Line in Figure 1 for Distributed Query	Output Line in Figure 1 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.