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 Selected Output of SET EXPLAIN ALL for Distributed Query, Part 3 for Distributed Query Output Line in Result of EXPLAIN AVOID_EXECUTE directives 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.